How best do I structure my User database?

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

Comments

[Aaron]

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 and meta_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 the usermeta 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 a one-to-many relationship with a property. I would wager you could have multiple tenants per property so that would also be a one-to-many relationship. Caretakers I'm not sure but that could be a one-to-one or one-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!

[Derek Hopper]

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.

[Oracle Sean ♠️]

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.

  create table users (
         user_id number not null
...
,        role_id number not null);

  create table roles (
         role_id number not null
... );

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 the user_id and role_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 users
roles defines roles
user_roles says what users have what roles

Consider 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:

  create table user_roles (
         user
,        role
,        property);

insert into user_roles ('Mary', 'Tenant', 'BuildingA');
insert into user_roles ('Mary', 'Caretaker', 'BuildingB');

(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.

[Lorraine Lee]

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.

[dreplica]

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.

[fedeagripa]

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 :)

[medboo]

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)