MySQL Question - How to Handle Multiple Types of Users - One Table or Multiple

MySQL Question - How to handle multiple types of users - one table or multiple?

Your instincts to not create a big table with lots of NULLS is right on. That's a bad idea, from a storage/retrival/maintenance point of view, as well as a data validation point of view (more on that later).

The two most common approcaches:

1) Have a user table with all the common fields in it, including a "userType" field. Then have a separate table for each user type containing the extra fields. All users have a row in the users table and one or more of the specific user type tables. This is the most normalized and the most efficient for storage and quick logins. This also lets you use contraints and foreign keys to assure that all required information for each user type is available.

2) Have a user table with all the common fields in it. Have another table called something like UserAttributes that has fields for userid, key, and value. Any extra metadata for a particular user can be stored in here. This has the advantage of not requiring any database administration to add new user types or metadata to be stored for each user type. However, it doesn't let you do any data validation at the DB level.

How do I handle multiple types of users?

It would simplify your code if you had a role column that had either teen, individual, or member. You'd probably normalize these roles to their own table, and use a foreign key:

Roles
1      Teen
2      Individual
3      Member of an Organization

Database for multiple types of users

There are three main ways of implementing inheritance on database models. Please check the links below, and study which is the best one to solve your problem. Nothing better to start analyzing this types of situations to become a good architect.

Single Table Inheritance

Class Table Inheritance

Concrete Table Inheritance

Each of the different approaches have their pros and cons so choose wisely.

One Or Multiple Tables For Multiple Users

The way I see it, you need 5 tables, one each for category, post and user, and 2 tables to store possible (many-to-many) relationship between user-category and post-category.

category - category_id, category_name, description
user - user_id, user_name, user_details
post - post_id, user_id, text
user_category_mapping - user_id, category_id
post_category_mapping - post_id, category_id

---

EDIT

If you want to have only one category per post, following four tables should suffice

category - category_id, category_name, description
user - user_id, user_name, user_details
post - post_id, user_id, text, category_id
user_category_mapping - user_id, category_id

Similarly, if you want to implement a subcategory functionality, you can alter the category table as below

category - category_id, category_name, description, parent_category_id

How to handle the primary key of different types of user tables?

I looked online and found that its common to make 2 separate tables and just have the differences in these tables.

I'd be surprised. That's not a normalized schema.

I think you should just have a single table with nullable columns, or else as per this answer here, or else (madly) go the whole hog: normalize fully and have three tables: a UserID table, and a phone-no+address table and an email table, both with UserID foreign keys, which is clearly major overkill. If you use secondary tables you just do joins on UserID from the UserID table.

You should certainly not have two user tables of equal standing where a new UserID can be inserted into either. That's not normalized.

Relational database design multiple user types

Your case looks like an instance of class/subclass.

There are two classic ways to design SQL tables to deal with subclasses. Each has advantages and disadvantages.

One way is called "Single Table Inheritance". In this design there is just one table for all types of users. If a given column doesn't pertain to a given row, the intersection is left NULL. A column can be added to indicate the user type.

Another way is called "Class Table Inheritance". This is much like the answer Nanego gave, with a few minor changes. There is one table for users, with all the common data, and a id field. There is one table for each subclass, with data that pertains to that subclass. The id field is often set up as a copy of the id field in the matching row back in the users table. This way the subclass key can do double duty, acting as both a primary key and as a foreign key referencing the user table. This last technique is called "Shared Primary Key". It requires a little programming at insert time, but it's well worth it. It enforces the one-to one nature of the relationship, and it speeds up the necessary joins.

You can look up all three of these designs as tags in SO or as articles out on the web.

single-table-inheritance
class-table-inheritance
shared-primary-key

Database design: User Types: Many fields in users table or separate tables?

You are tying two different things together under the term user: user as in “someone who registered in my application” and user as in “someone who’s using my application to get rides”. Both drivers and non-drivers are users in the first definition, but not in the second.

What's confusing is that the Driver entity is just a User entity with more fields, so it's possible to not represent the entity at all, just add more columns to the User entity, and, responding to your first question, add a is_driver column to tell which entity is which.

By doing this, you are crippling your database capabilities to guarantee your data is valid. You now can have a Driver row without a driver_license_number, because your database doesn't know what a Driver is, oops.

There's a lot of benefits by being explicit in your database schema. Part of the database work is to guarantee data consistency, help your database help you.

My suggestion is to go a step further. Credentials are one thing, they get their table. Users are another, they get their table (in your example, users seems to have no data at all, but they will probably have more things than just their name). Drivers are yet another, they get their table too.

---