I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited information about each user. I then have 3 tables for each user type: admin, reader, approver.
Now, the design I have gone with thus far has been something like this:
General Tables (don’t pertain to users)
Tables That Pertain to all Users
Tables That Pertain to User Type
Now, based on this information, I am wondering a few things:
Global tables do not get a prefix. Is it considered bad practice to prefix the tables that pertain to a subset with the subset name (e.g., admin_)? I figured it makes it easy to look at the DB design and see exactly what subtype each table pertains to, but the next question is what made me ask this question at all.
The table person_contacts_categories_map is a junction table between the many categories a person can have, and the many contacts in those categories. OK, typically I name my junction tables something like contact_category. Unfortunately, there are more things that get categories in this app than just contacts, thus I am saying this table relates to all persons (e.g., person_) and relates to contacts (contact_). I was thinking then that the junction table should be named person_contact_person_contact_category, but that is just horrible.
So, given what I’ve laid out, does anyone know a better approach to this problem, or is what I am doing fine?
Instead of changing the names of individual tables, I would recommend using schema names to denote the different types of users.
Using Schemas in this way allows a more flexible method for controlling access since you can assign rights by schema instead of at the individual table level.
I would reverse the naming convention for dealing with subset types. So a table of users, then another table of users_admin, users_reader, users_approver.
I’ve used this in the past for parent-child tables. It makes them easy to locate and determine who is the parent and who is the child when looking at a list of table names.