Laravel - Split Users DB Table: Why It's (Not) Bad?
HTML-код
- Опубликовано: 5 июл 2024
- I often hear an argument that there can be too many fields in the "users" DB table, so it's worth splitting it into two tables with the relationship. Let's take a look at one example, and I will share my thoughts.
Source of Laerx: github.com/codenteq/laerx
My course "How to structure DB in Laravel": laraveldaily.teachable.com/p/...
- - - - -
Support the channel by checking out my products:
- My Laravel courses membership: laraveldaily.teachable.com/p/...
- Laravel QuickAdminPanel: bit.ly/quickadminpanel
- Livewire Kit Components: livewirekit.com
- - - - -
Other places to follow:
- My weekly Laravel newsletter: bit.ly/laravel-newsletter
- My personal Twitter: / povilaskorop 
Хобби
Very genius overview. 👏
Personally I'm fan of database structure tutorials.
From a database point of view, that's fine. Maybe exactly as it should be. The problem here is eloquent. In traditional approach you can write one sql query that covers lots of table and result that into one object. The lack of this approach should be criticized, not the design of db tables.
the design of the tables is criticized because it's using laravel and he is reviewing a Laravel project with that database design. if he was criticizing only the database design this would be a fair comment.
@@sergiocasao I agree with you. The reason why I considered it important to write a comment is that there are many people who do not have the necessary knowledge and learn it as a rule.
It's not even a problem in the first place since Eloquent has the necessary tools to properly execute the performant queries. In this particular case, you have options to use eager loading or use subqueries with addSelect(), or just go with joins. Eloquent handles all that, too. The problem here is the app design, not he tool.
The db structure was perfect. Its the query that was wrong. It's become fashionable to not use joins in laravel. I don't know why. There seems to be a preference for multiple small and simple queries over one large and slow one. Which is fine. But most large and slow queries can be optimised to be just as fast or faster than multiple small queries. It just takes good db engineering knowledge. Which most laravel devs seem to lack.
Just join the info table and enjoy querying the fields with the same ease and speed as if it were on the user table. One to one joins are the easiest. No reason to avoid them.
Very useful information 👌 keep it 💯
The way I suggest to do this is keep using the user information on the normal `user` table. So you wont duplicate the query when you type auth()->user() . For user authentication (user id, email, and password) you can use a separate table.
Nice topic, 👍
thanks mate for the tip
Love database structure tutorials using Laravel! I wonder if you have a video explaining the best way to structure a database where users can have different roles (i.e. buyers or sellers) and you need to store different data for each.
Not specifically, can you give a specific example of the list what those fields would be? Could be a good topic for a future video.
@@LaravelDaily for example i would like to create an uber like app where i have drivers and users, users can take a drive and rate the driver also drivers can have their bio, license number, years driving, rating and just them can have relations like to the car the are driving and so on... both drivers and users should be able to login so i'm not sure if i should store drivers and users in the same users table or create a drivers table associated with the users. If I go with the first option how can I make sure the driver of a ride is effectively a driver and not a simple user?
I would do this with optional data, and then I would probably use a key/value store for each optional field. Most efficient!
i feel this too, some field needed to place inside users table instead of user_etc for ease of query. But this method not good for upgrade laravel or reusability, BUT upgrade is once upon of time, i pick normalize user table.
Just add a $with in the User model, and you'll get the ->info loaded every time in one query only, since it's used almost 100% of the time.
While this project doesn't look very serious and there's a ton of improvement that can be made, splitting the user table in this case doesn't seem very concerning.
Well, it's not used 100% of the time, only in a few places, so $with would load too much data for all the other cases. Agree that this project is not a good example, but that's exactly why I took it for review.
I think you don't need $with here. Yes, you will probably need current user' info on pretty much every page, but there could be places, where more than one user is queried, and not for all of them there really a need of loading info (and where it's needed - you just explicitly add ->with('info').
As for the current user - i think it's ok to have one extra query on all the pages.
I like to have all required fields in the users table (name, password, photo, username, etc) and all optional fields in a profiles table (bio, company_name, etc.)
Database structure tips🎉🧨
is there a way to load both at the same time in the User model constructor for example ?
$with = ['info']; but you need to be careful to always load too much info
We could just cache the user object and purge the cache on every update event for that particular user. So if caching is used, it really doesn't matter if there are multiple tables or a single table. Right?
In theory, yes, but you have to be really careful with cache and its refreshing. And I don't think storing all data for all users in the cache is a good idea.
Won't auth()->user()->info-> result in a DB lookup the first time, but then any subsequent uses will use cached data, so no more DB queries?
Also, what's the drawback of having a table with lots of fields?
Table with lots of fields will be slower to INSERT data, with also risk that developers would do "Select * from table" and blindly download too much data.
With cached data, it depends on how you use that auth()->user()->info in your code, the example I've shown is just one of the examples.
when you write user->info-> just the first time eloquent query for info table. also if you dont want this approach and want query each time you can write user->info()->
What about using only a json column ? I usually use this way in all my projects because my clients always ask me for different fields and dynamic or extra fields for their users and instead of creating fields in the database I only use a json type field, in this way I can add different fields without doing a migration for each one. What do you think ? it worth?
If in your case it makes sense, and you don't have problems querying the data from there, then it works for you :)
I think json columns work fine most of the times. What I don't like it is that your lose the ability to use fk in cases where you have relationships inside the json
Sounds hacky and like a hassle to maintain
Seems like you need document database lol
What about a morph relation on the User model that points to additional fields from another model, Is it a viable option? MobileUser, StoreUser..etc
Sounds like unnecessary over-complication, but is it viable or not would need to actually try it and measure the results.
I use json field for dynamic data. Is it good to use json field for dynamic data. Imagine you are building google form builder or survey builder, how would you save all the data, what will be your table structure?
I guess this is worth another video :)
good morning... I have an application with a database that will have about 100 tables... is that too much or should I split it into two related databases? Thanks
Do you have performance problems? If not, it's fine :)
There's no "too much", there are specific problems that you need to solve and then optimize specific functionality related to it.
@@LaravelDaily for now no working problems...thanks
But what if this structure is what they want? I mean, this can be also improved without changing the DB structure, while still having this maintainable, clean IMO structure. Personally I like it, why not?
For example in your first example in the Controller, they could just use $user->info once, on a variable like $userInfo = $user->info and just use this variable below. Problem solved for this one, now it will run just one query to get the user info.
About taking the photo on the layout, what would you suggest? Well, caching some specific user->info fields or the whole info modelis they only way I think.
And about the middleware, the same, this should be cached.
And "forget" from cache each time a user information gets updated. (using Observers)
My whole point is, what would you suggest if they really want this structure, for whatever reason. I would really like to hear your opinion on this, but with the current structure.
So you're saying I should make a suggestion on how to find complex workarounds for the structure which they chose to be more complex themselves? Sorry that's not my way of thinking :)
How would you face this problem if the DB had already one million users, as a random example of course. You would change the structure and somehow backfill the new fields?
Yes, because I would care about the future of this project, and not a temporary workaround fix.
But, most likely, I would refuse to work on such project :)
@@LaravelDaily haha makes sense. thank you man. Continue the excellent work. Sorry for too many questions, but you know I try to keep this interesting, for both of us. Just saying "excellent nice" and things like that doesn't drive anywhere :)
Performance or Aesthetic Functionality ? We must have both
So, dont separate fields that often used for queries?
It seems to me that there is no problem with database (with approach to separate "service data" and "profile data"), there is a problem with the code itself and one big flat table not solve the problem but creates new
what about a json field in same table?
Also possible, but I'm not a big fan of json fields. They kinda ruin all the purpose of using DB and its capabilities. But they may work, in some cases.
integer is super fast and imagine u wanna convert (status_id, month_id, company_id,....) to json and put these data in a single column in database. I think its against the rule of database engine and in big data for CRUD operations the program will die.
If you're not going to query based on a json object, it's a pretty good option. As a rule I don't put in stuff that is better suited for a relational DB, usually references/keys.
@@arashpirhadi1326 understand, You are definitely right
I'm not sure, but the way Laravel's bindings works, when you call auth()->user(), you get the same model instance every time, so when you call auth()->user()->info multiple times, the info relation already eager-loaded, so there are no extra queries to the database. At least, this is how facades and singleton bindings works, but I'm not sure how it's inside Auth facade, need to inspect more detailed
i noticed you wrote "companyId" and not "company_id" isn't it good practice to use the second approach, use underscores ?
it's better to use underscores
No, good practice is company_id. I didn't write that code, it's not mine, I took it from one open-source project, I mentioned it in this video.
@@LaravelDaily my bad I missed that part, thank you 🙏🏽
Oké so why not create a method in the user model that eager loads the info with the user. Then you only have to call that method on the places needed, you’d also could give it a param to only() get the necessary fields?
return $this->settings()->with('user');
why not load a singleton in a ServiceProvider with all the info? This can save performances, i suppose. Of course, your solution is more "standard"
This project is not a very good example to follow 😖
But nice topic, thanks Sir 🙏
I often take projects for review not for people to follow those projects, but specifically for that reason - for me to work on some improvements.
That routes file is a mess
Asın bayrakları
Maybe this could be a nice feature for the next Laravel release: map the user Eloquent model (or any model) to get data from two tables instead of just only one.
You can suggest that as a pull request, it's an open-source framework :)
Splitting a table because it's aesthetically more pleasing to the eye does not make it a valid argument. Why burden the db with an extra query every time a user's info needs to be fetched? Totally against it since it doesn't produce any tangible benefit.
I agree, joins are expensive and should be avoided when possible
Ты же русский? Акцент похож очень
И на угрюмую рожу - тоже русский. Тут нас ни с кем не спутаешь. :) Но ему сейчас лучше не акцентировать на этом внимание.
@@CJIu3eHb и правда😂