Database Schema Question

Question

I'm creating an application in which there's two different types of users. Should I put all the users data in one table with a 'role' field added to identify user types or should I create a separate table for each user type? Each user type will need a few fields that they do not share in common.

Answer 1

The fact that each user type will have fields that it does not share with other types makes, in the absence of any other information, the separate table approach the simpler one. If you use one table, then you would either have to add columns for all user properties and fill the table with quite a few NULLs, or collect all properties into some kind of encoded clob or blob, which is very un-relational, but in Big Data and NoSQL enviornments, feels fun to work with.

If you had hundreds of user types instead of two, or you have very specific reasons for a kind of denormalized, complex, or encoded property information, two tables should be fine. Queries over "all users" would be messy, though, so go this route if your user roles are distinct.

If users can have multiple roles, one table with the common data and multiple tables for role-specific data works well, as other answers have stated.

Answer 2

How many columns are distinct for each user type??

If you have 50 columns in common, and each type has just two more that are distinct - then I'd probably put those two types of users into the same table and just make those extra columns nullable.

However, if those types of users only share two, three common columns (ID and Name) and each has 25-50 separate columns - some of which you might want to make NOT NULL for that type of users - then I'd use a base table (with just the common attributes) and two separate "derivative" tables for each user type, referencing that base table.

Answer 3

Create a single table for users, with different roles, as you said - having only the common properties for all the users in it. Create other 2 tables in relation of 1-1 with the users table, and in those tables keep the properties for each user type

Answer 4

Generally speaking, the best design is to have a table with rarely-changing details, and another table with a foreign key constraint from the primary key of your first table holding information that does change frequently.

Personally, I'd have a single 'users' table with a field type of what kind of user they are, and the secondary table with other data identifying more specifics that you'd need.

Then you can do a simple join between the tables whenever you need data, but if you just search your main users table, a quick scan will be much faster.

Answer 5

The true answer to this is going to really depend on your downstream implementation of this. In all reality I would most likely think that you might do something with a Role to identify the user and use that role to control validation on additional required fields. But that might not suit the needs of your situation.

The key will be determining is this a one and only type situation, or do you anticipate additional roles/usertypes in the future that will require this solution. Also, is there other functionality that is different to where it would dictate a need for duplication of core user functionality such as validation etc.

Splitting to two user tables could make authentication harder for example if both user types login via the same page.

Answer 6

If the difference is one column you can use a Role column for this and place them into one table. If you see that one role requires more info than another you can create a base table for all of the users and then store additional info for the Role in question into another table and use an id as a foreign key back to the table with the information.