CODEWITHSUNDEEP
mysqldatabasedatabase-designsingle-table-inheritanceclass-table-inheritance
Héctor
Héctor

Reputation: 509

Database model with 3 kind of users

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

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

Upvotes: 1

Views: 175

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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.

Upvotes: 1

tempusfugit
tempusfugit

Reputation: 437

Yours is a case of Specialization/Generalization. Since you said User (1, 2, 3) share common properties, it is best to have a General entity - User with all the common attributes.

Then you can Specialize to 3 different types and have the specific attributes to each type.

So, option B is what I think is best solution for your problem.

Option A will cause too many null values and option C is overly complicated.

Upvotes: 2

Related Questions