User Roles: Many Fields in Users, or Separate Tables?

Today I'm answering one question from the comments, with a demo example. In my experience, it's no big deal to have 10-20 extra fields in the Users table. What is your experience?
Repository: github.com/LaravelDaily/Larav...
Eloquent Local Scopes: laravel.com/docs/8.x/eloquent...
- - - - -
Support the channel by checking out our products:
- Enroll in my Laravel courses: laraveldaily.teachable.com
- Try our Laravel QuickAdminPanel: bit.ly/quickadminpanel
- Purchase my Livewire Kit: livewirekit.com
- View Laravel Code Examples: laravelexamples.com
- Subscribe to my weekly newsletter: bit.ly/laravel-newsletter

[@_paradiddley]

I’m surprised spatie/laravel-permission wasn’t mentioned. I agree that your example is perfectly fine for prototyping and small projects. But for medium/large projects with unknown scope, it’s worth doing the extra work separating the data out to separate tables and using the package I mentioned. Scales much better in my opinion. But of course you know that Povilas.

[@justtestingmycurrentskills747]

I kinda prefer separate tables. I think it's more scalable and looks nice, but of course there are some downsides like querying relationships.

[@Ahmed_Elmahdy]

Then you will have to create user pages like login,forget password, etc. for each one

[@ShamilHPC]

@@Ahmed_Elmahdy no you'd have the same login/register flows. Because both doctors and patients are users. You store the common info in the users table. You can even have the role_id on the users table itself. The doctors table and patients table then are related to each user, and contain specific information. They'd have a user_id field as a foreign key.

[@wahid-shorts]

@JustTestingMyCurrentSkills, hay can you share me the link how to Login from multiple table. Like in this video doctor and patient. How to do it?

[@justtestingmycurrentskills747]

@@Ahmed_Elmahdy That depends on your clients' requirements e.g two separate apps for patients and doctors with different logic.

[@justtestingmycurrentskills747]

@@wahid-shorts I can't think of one notable. Sorry.

[@NotBeHaris]

Thank you sir very much. For picking up my comment and explain this in detail. Thanks again.

[@Samuel.Mwangi]

I will say like majority of the commentors here, it's heavily depend on the project at hand. This 100% personal preference. If its a small project where I don't see the project scope clanging much then I would consider this approach. Every other time I opt for parental by Caleb which for example allows both Models to share a table but can have distinct relationships. I contend that this method may come with a slight performance hit in the form of table joins but helps keep the data and code consistent . Also avoids null columns that are not shared

[@ashay191]

This may result in many fields remaining null for a patient or vice versa and does not account normalisation. But again the question boils down to trade off of complex query/code vs simple code to maintain normalisation.. it would be great if you can make a video about this very tradeoff ..great work keep it up

[@user-si3nz1dd9w]

One way to deal with this issue is to move the non-essential data (i.e. the columns that are not used in DB queries or authorization logic) to a JSON column. With Laravel, the laravel-schemaless-attributes package by Spatie makes it especially handy. Then, to keep things consistent, you would declare the fields list of this column for each role in separate classes. With this approach, you get the no-sql kind of flexibility when working with the non-essential details while all the benefits of the relational database are still there at your disposal. I find this approach very practical and I use it all the time when dealing with the data (not just user-related) I don't need to query directly by column name.

[@huynguye]

@@user-si3nz1dd9w we have tried that. Eventually, it became a debate every time a new field is needed for the role, as to whether it should be added to json field or a separate column. If added to json field then someone decides they need the field for querying later, we have to set up a column extraction and after a few times doing this, we started to realize that scalibility is greatly affected especially after db records grew in size.

[@user-si3nz1dd9w]

​@@huynguye It might be an issue, but this is more of a management / style-guide issue than an architecture-related one. For safety, it's better to start moving absolutely secondary details and attributes/parameters to the JSON columns. If there's something that "might be queriable" in the future, if it even remotely makes sense, then it's better not use the JSON columns and stick with normalization techniques for the common attributes and parameters. It always depends on the case and what makes sense in the particular context. Always. No single "right" solution.

[@salmamehanny4030]

i prefer the separate tables i think it is useful and very clear to me

[@GergelyCsermely]

Thank You. Very Interesting solution with scope for select fields.

[@enrica6616]

Your method is ok if you never will have one user in both roles. I did such a table for credit covers (like real estate or a bank guarantee). But natural persons could have more than one role. Eg. if a doctor himself is patient at another doctor what then? You have to enter same name, address etc twice. I am sure that an address change will be done once only. In such a case I prefer a proper normalized database. Another example is a companie could be customer or supplier.

[@wahyu_pratamabwi]

I think for database normalization, its better to separate it to other table. (In my preference, i'd like a table with less columns, so the readability is better)

[@chameeradinesh1221]

Users table with role-id is better than separate tables ❤

[@Niboros]

Thanks for the video once again! If you are going to work with Single Table Inheritance (STU) as it is called and as is shown in the video, this is the way to go. This very maintainable use of scopes can save your day. It really depends on the situation tho what implementation is the better solution for your current problem. I would still love to see your take on the up and down sides of both implementations. Maybe with a nice overview sheet or something as well! Main question to tackle: What are the most critical signs to look for to know if you need STU or Multiple Table Inheritance?

[@coding_david]

I have things like that in my product and I divided them into separate tables (e.g. User and Person data like address information etc) Wouldn‘t the combination of these columns into one table somehow violate the single task principle that I‘d use for other components like controllers and others?

[@gtsmeg3474]

I actually had to manage a db with different jewelry products (ring, necklaces, ......), so at first place, each jewel has it own table (each jewel is characterized by special attributes like ring have size, necklace have chain type, etc) The big issue with this approach is when filtering the products on certain criteria, it became impossible to retrieve the right products from the database, so at last I had to regroup all products in one products table so I can apply filtering and pagination and order with ease.

[@photofrode]

Faceted filtering? Sounds like a good job for ElasticSearch

[@iamriwash7943]

It is possible to check relation title if null or not . For ex if their is role I use if condition if $user->role->I'd null or not

[@kaimunlee7494]

I do add more columns to the users table when required rather than creating another table. I feel that it is fine on performance and readability.

[@KatieGeorgieva]

The separate tables approach will work faster, for the database programs generaly prefer harmonized databases to tables with many fields. If you manage to transfer th work to the database, it will be even faster.

[@abdulj3927]

Hello sir, I have questions about setAppends I've case 1 main table "A" 2. HasOne table "B" 3. HasMany table “C" And they have an add attributes B: function getAttrSomethingBAAttributes() { return query } C: function getAttrSomethingCAAttributes() { return query } Then I have eloquent like: ModelA::with(["b","c"])->get(); How to call the attributes b and c ? I've try something like ModelA::with(["b","c"])->get() ->setAppends(['b.attr_something_b_a','c.attr_something_c_a']); and I got error setAppends function not found or doesn't exist. I don't want use the append on model cs not every single page need that attributes. If I add append on model that will work but always show that append. The question is, how to use the child attributes on main eloquent? Thanks, I'm sorry if my English is bad ✌

[@LaravelDaily]

I wouldn't use attributes like this, joining other tables, attributes are mostly used within one table

[@abdulj3927]

Ah I see thank for the answers sir.

[@ruslanvoroshchukowlookitlt245]

Hi, Povilas, thank you for the new bunch of practical tips! Could you please make a video about how to deal with concurrent eloquent requests using optimistic and pessimistic locking or other solutions? Thanks.

[@LaravelDaily]

It's a question that is actually relevant and needed for a very small amount of developers, so for now not in plans.

[@ruslanvoroshchukowlookitlt245]

@@LaravelDaily got it, thanks

[@JamieHollyHIT]

This is fine if you are only going to have a couple thousand users max, but once you get into the tens of thousands, you will hit performance issues, especially if you have to start querying on different fields. Personally I like to keep the user table lean, then use additional tables for things like roles and profile information. The only thing I would keep in the user table outside of the default is things that would be commonly accessed, like a display name and avatar. In this scenario I would go with a separate table for roles (though I don't really like that name in this scenario), since a doctor could also be a patient. I would then create a common "profile" table, then if that's needed on a users listing page, load it via a join (1-1 on PK is lightning fast) setup through a scope. One other thing to consider is that all the data from your users table is also going to load through auth for the current user. Again, not that bad in your example, but if the data keeps growing, well you could end up with a lot of needless data being sent back to the app just to load the authenticated user.

[@wawanneutron]

what is the name of the extension in controller like pointer guide , example: view, column, var_name. please let me know :D

[@LaravelDaily]

It's phpstorm by default

[@fathra6102]

inline parameters for vs code, if you use vs code

[@ChangeYourLifeForever]

awsome !!

[@afuwapesunday5423]

For me this what i normally do in the users table i will have a user type column which can be doctor or patient Then i will have two table for the patient and doctor and this is why the Doctor will have fields that are related to it likewise the patient table that will help in readability And if i need to query for the doctors alone the query will be constrained with a where condition that select only the user_type of doctors and this is useful if the roles are static like just doctors and patients alone

[@Extjac]

it depends on the project. I personally use both approaches; for me, it also depends if customers/patients will have a front-end admins/Drs will have a backend.

[@rachelkaranja8201]

Hello guys, I have a question, what if some users in your table have relationships with each other or other entities in the application? Is it better to use separate tables or put everything in the users table? Your help will be greatly appreciated

[@LaravelDaily]

There's no one correct way. You need to experiment with both ways and try it yourself, and then choose

[@Voltra_]

My rule of thumb is: do the Entity Association modelling. Most of the time, in my experience, these tables with lots of field are this way for "convenience" and because it wasn't modeled at all. Following the modelling principles and applying the conversion to schema algorithm you always end up with a database in at least 3NFBC

[@nisargsachinsaple]

Sir can you make a video regarding passing foreign keys and indexing in database ?

[@LaravelDaily]

What exactly is unclear about passing foreign keys? And each foreign key is automatically an index in the database, from what I remember. I don't really see the question/problem to shoot a full video about.

[@nisargsachinsaple]

@@LaravelDaily In my company developer never passes foreign keys to database still they make relationship in Models of laravel Is it proper way ? and I am asking what is meant by indexing in database ?

[@ShailendraSharmaDark]

@@nisargsachinsaple Indexing makes search faster, by default primary key is indexed so any query on primary key field will return results faster but if there are other fields on which you perform search queries then you can add index on that particular field and you can add index on any existing table through migration

[@LaravelDaily]

I have two videos you can show to that developer: kzbin.info/www/bejne/Y2iafaF4iLFlkM0 kzbin.info/www/bejne/p3izqJiwndB1qdk

[@nisargsachinsaple]

@@LaravelDaily Thank You sir

[@pouriya2376]

What is the name of debugging tool you're using bro? I really need something like that

[@LaravelDaily]

Laravel Debugbar

[@pouriya2376]

@@LaravelDaily Thank you very much, that was sort of life saving in my new project 😅❤️

[@SinghatehAlagie]

Well all the approach’s are good but normal I prefer using separate tables when dealing with different roles rather than one table, the downside of it you need to ready to select the fields that you might need to reduce the query.

[@OlegHolovko]

I have a different solution, maybe it's not in Laravel style. I had created related tables for different user types. But in the main table, like the "users" table in this case, I had created a field with type of "json". This field must be updated when data in the related table has been updated and it must have all needed fields from a related table but in JSON representation. In this case, we don't need to use join with select-type queries. Also, we may use JSON subfields in "where" statements with select-type queries. This possibility is presented in both Postgres and Mysql. It made the database structure more scalable, and we don't need to use joins in requests.

[@Kewledgykid]

1:59 what is User:ROLE_DOCTOR called?

[@genechristiansomoza4931]

This is probably how I do it. Doctor has user. Patient has user. User belongs to a doctor. User belongs to a patient.

[@nadjinmalade8738]

For small projects I this way is the best way I think. It's readable, and you avoid using relationship. My method to make it shorter for example when it comme to permisson is to add only extra field(Json). I store [key:value], and on the frontend I just Iterate on Key. But when It come to bigger project, I thik this way may not be good.

[@ahsanullah6326]

separate tables for different authenticable is a pain in the ass i regret not doing it in one table RN in my project

[@gebruikersnaam999999]

Why would you do that? You only need authentication for the users table. And then you have two separate tables for patients and doctors

[@1hotday1]

In a perfect world, two tables. Codd-Boyce would be proud. However, if it's the difference between making money and simplicity in query, one table it is. I lost projects trying to do things the "right" way. Live and learn.

[@muhammedbaldeh559]

I use one JSON column to store user permission roles. Easier and more flexible.

[@MustafaBirsoz]

nice

[@sean_reyes]

why not store it in json? json columns can be queried..

[@rafaelrgl]

I understood your point of not having separate table, but lets extend the problem a bit further so I can have more insights from your prospective. Lets say now instead of doctor/pacient, we have hospital/patients. i'm saying this because hospitals will manage patients on the admin panel. up to here all is fine, but what happens if i need to signup same patient in 2 hospitals with same email and on hosptial one they have a frontend for patients and they need to login that patient to show stuff. Help us with that problem pls

[@sahin8649]

I am not a professional but I'd still go with separate tables. I feel comfortable using separate functions in the user model to ask for information of users with different roles.

[@o_lobato]

Using one table all fields must be null, so you loose database level required data. That might be a problem for some cases.

[@mmramadan496]

Awsome, butI think that its not good way to do like that because we must save the consistency of the database its not good way to have a million records and the half of them is null, i think that the best solution is to do a relationship between the user and the doctor or add doctor's attribute in the users table as a json

[@maflones]

If I were to work with a data structure like that I would define views for separation in MySql with joins and sorts and limits and everything prepared. CREATE VIEW 'vRecentDoctorsData' AS SELECT (a.field_list, b.field_list) FROM Users AS a LEFT JOIN Other AS b ON a.id = b.doctors_id WHERE b.relevant_date_stamp > DATE_SUB(now(),INTERVAL 1 MONTH) And then use that as a base for a lookup inluding what we in laravel would ->with(). Speedy.

[@haroldpepete]

for sake of normalization the best way is separate the data, you still avoid repeat information, what happen if doctors have the same pacient and vice versa

[@Stoney_Eagle]

This is insanity! Don't store both in the same table.... A doctor has many patients and a patient has many doctors. But you can't query the patients that a doctor has or vice versa. What if you want to add a department or salary scale? Don't store different types of "people" in one table 🤦🏼‍♂️

[@aryaaditiyaa]

so create seperate table for doctors and patients is the solution?

[@ilhamfajriumar9085]

wdym, you can create many to many table and do normal join with it

[@mikegodz]

@Stoney_Eagle In general, I agree with you: different entities should go on different tables, and I would have all in one only if the specific fields are very few. In this case in particular, with so many independent fields (yes, to me 10 are "many") I would have chosen to split the tables, especially if you plan to scale things. That said, you can query the doctors for a patient and the patients of a doctor, it all depends on how you set up your relations: even on a Many-To-Many relation you have just a pivot table that pairs users with other users. It requires a bit more checks just to avoid inconsistencies, but it's doable and not so dramatic.

[@Stoney_Eagle]

Why go against the Laravel way of doing this and create chaos when it's totally unnecessary. Yes you can query relations like this but you have to set it all up and if someone else joins your code they have to look trough it all to figure out how it all works instead of knowing it because you followed the best practices of Laravel. Doctors::where("id", 4)->with("patients")->first(); Do you expect 2 tables or 1?

[@LaravelDaily]

What do you mean "can't query", everything is query-able, it's just a join to users table with alias doctors. You're suggesting to have separate table doctors, and then join that table doctors, just without alias. I don't see any fundamental difference, to be honest.

[@SussanRai]

Sometimes i feel, no sql is better for this kind of data

[@gnguyo]

Yes, This is where the MySQL JSON table column come in handy.

[@mikegodz]

Not an expert in NoSQL, but isn't its main goal to be used with "unstructured" data? In this case datas are quite well defined and structured...

[@jachas7566]

@mikegodz Its trully not good to use the mysql and keep that empty fields, cause its take the memory for each record even if its null. In nosql you could store the object data that you need to keep so at least you wouldn't use space that you dont have to use, thats why i guess nosql is better practice for this example :P

[@orkhanfarmanli]

This is not a good approach imo. First, it’s always a bad design if you have 20 nullable fields in a table. Second, things can and will get messy if you are going to add extra features down the road. Best possible way would be a users table for the admins of the CRM, then a separate doctors and a separate patients table that are authenticable just like users. This will result in a better normalized database and application architecture. Imagine the logic User model would carry if all these tables were together.