1

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

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
Héctor
  • 509
  • 2
  • 7

2 Answers2

2

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.

tempusfugit
  • 437
  • 1
  • 3
  • 14
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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thanks for your help mates, i will think about it, at this moment i think that the second option is the best one. Maybe it works faster than the others and it's not too complicated to use. Thanks @san1646 too – Héctor Oct 21 '14 at 21:19