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

Question

I'm designing a database for an organization that has multiple "types" of users. At first, I created only one user table. However, while all users share some common information (first name, last name, username, password, etc.), each user type requires one or two additional fields that are not applicable to all users. While I can create these additional fields and set them as NULL, I do not wish to do this, as the fields are foreign keys and its been causing problems for me.

How is this situation normally handled?

Thanks!

Answer 1

This is the famous normalization question.

Take a peek at this article or others like it to try to find an answer that fits the business needs.

To normalize or not to normalize

Answer 2

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.