Database model with 3 kind of users

Question

Im thinking about what is the better way to design a database with the following statements:

• There are 3 kinds of users (3 differents roles).
• They have some common fields, however they have differents fields too.

Solution A: All user in the same table (some users will have empty fieds depending of their role).

Solution B: A main table where i will add users and their role. In addition i will create 3 extra tables where i will record with extra fields (depending of the table) and each record will have an id related with the user in the main table.

Solution C: A main table with basic user info, and a second table with the metadata user info where each record means a field of a user. That it's similar than WordPress

Answer 1

That depends :-)

Will you ever treat the users the same? E.g. at login time: Will someone just login with a username and this can be any of the three user types? Then you need a user table with a username and a unique index on it. This is solution A or B. I'd prefer B over A, so you can decide which of the fields are nullable for the specific role.

Or will you never deal with a user with an unknown role (such as: a person logs in with a role plus a username, so it suffices to have three different tables each with their own usernames)? This would be three special user tables and no common user table.

Option C would be something easy to implement in order to give users additional attributes, but can become a hassle. Especially when there are obligatory fields and fields that link to other tables (such as a job number that is meant to be the key in the jobs table, but you cannot use a foreign key then). I usually don't take this route, if it is avoidable.