Hi everyone,
I need help designing my user related database structure. I am building an app with Rails to allow house owners manage their various properties and I want to have different categories of users. There are the admins: they get the dashboard and do analytics etc. Then the main app users are owners themselves. I want the owner to be able to add a tenant to a flat and also add a caretaker to manage a building or more. Do you think I should use the same user table for these 3 with roles?
Any advice you give will be very helpful.
Thank you
Top comments (7)
OK I will admit I am not the greatest at database design, but I think a reasonable approach could be something like this. I think keeping all of your users in a single table makes the most sense, but with an additional table that stores certain meta data about that user.
The table would have just a few columns, mainly the
meta_key
andmeta_value
columns. This allows you to keep your users table as clean as possible, but give you the freedom to add and remove key/values without having to modify the columns.In this way you would store your user roles as an
int
inside theusermeta
table. Additionally I would think about using ints like 1 - admin, 3 - manager, 5 - caretaker, etc. That way if you wind up adding more roles you have room to expand.Then in your code you would check for a users role and allow or disallow functionality based on that. You would then need a table for your properties -
property
. Since a user could own more than one property, a user would therefore have aone-to-many
relationship with a property. I would wager you could have multiple tenants per property so that would also be aone-to-many
relationship. Caretakers I'm not sure but that could be aone-to-one
orone-to-many
depending on your requirements.I've made a rough diagram of what this looks like, though again I'm not the greatest at this but thought I'd share just in case it gives you ideas or you can iterate off of it.
dbdiagram.io/d/5f3efc2fcf48a141ff5...
I do hope this all made sense!
As an aside, you can also take a look at the rolify gem to see how they do things (github.com/RolifyCommunity/rolify). I'll often take a look at how popular libraries solve for similar problems. Even if I don't end up using it, it's a way I learn. Sharing this as a reply to your comment Aaron because your design is similar.
Users are users. They have one or more roles. In a simple design you would have a user table with a column for the user's role. This gives you the flexibility to add roles and move users between roles without changing the user.
Your application queries the user table to get the role, then the role table to get the permissions associated with it (or better still a view to get it in a single query during initial authentication).
To more properly follow good database design convention you would move the role out of the
users
table entirely and have a table,user_roles
, to marry users to multiple roles. It would contain theuser_id
androle_id
and allow one-to-many relationships between a user and their roles and optionally define a Property (to clarify, this is a captial P Property for the user/role record here, not a flat or apartment) for the relationship.users
defines usersroles
defines rolesuser_roles
says what users have what rolesConsider two buildings in the application. Mary lives in Building A and manages Building B. Her role under Building A is tenant; for Building B she's a caretaker. Assigning roles in the
user_roles
table gives the ability to refine her access based on a higher key in the hierarchy—property_id
. Now she can query her own flat in Building A but not see others, while still managing Building B through rules like:(Ideally you're using appropriate natural or surrogate keys for these values and not text!)
This may seem like an overly complicated way of doing it but it's exceptionally scalable, flexible, and fast. Abstracting things in the database avoids having to redesign the database later, or compromise performance and flexibility by adding complexity to work around all of the things that were "hard coded" into the initial database design.
I like the (admittedly ancient) VMS model of user privileges where it's assumed that the relationship between users and privileges is many-to-many. A particular user has a particular privilege, or does not. Unfortunately, Rails seems to eschew many-to-many relationships.
Have a table for all users, create a relationship for each roles, owners might have more than one caretakers and caretakers might have more than one owners. Its more like building an interactive app with friends.
Just curious when you talk about admins, are they AdminUsers like ActiveAdmin ones? if so you have a first cristal clear separation there (but ActiveAdmin solves it for you, so no worries). If not..
You can take 2 vias:
Create 3 different tables
You will probably repeat a lot of code and add devise to all of them, so it does not look like a good idea (besides only thinking in the database seems so a good call)
STI solution
This is basically adding a type field to your user, and I think it would be the best solution for you. You can then add (Pundit)[github.com/varvet/pundit] for authorization to handle your specific user roles permissions (access dashboard, analytics, manage building, etc).
So conclusion:
I would recommend going for an STI and add Pudit to handle authorization (you can also use rolify or cancancan if you want, as they were commented in the thread).
Hope this helps you but feel free to dm me if you want any help or maybe a brief pair session :)
If you have few roles and you want to use the same "users" table to have a column "roles" (and also hopefully save some extra DB join requests) then check my answer here stackoverflow.com/a/66399033/2392106 (hope this help you)