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.
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.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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_keyandmeta_valuecolumns. 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
intinside theusermetatable. 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-manyrelationship with a property. I would wager you could have multiple tenants per property so that would also be aone-to-manyrelationship. Caretakers I'm not sure but that could be aone-to-oneorone-to-manydepending 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.