Geek Answers Handbook

Enter login and password in one table or in several tables for each type of user?

I have 3 different types of users, and each type of user can have columns and relationships with tables that the other type does not have, but they all have a login (unique) and password,
how would you do:

  • create a table for each type or
  • create one table for all or
  • create a table for all of them just for login and password and separately for all other things and associate them with FK
  • something else
+7
sql database database-design
source share
2 answers

I had this exact question when creating a new system. Here's a thread that has been very useful to me: Object-oriented structures in relational databases .

I went with the disjoint subtype solution well described here: Polymorphism in SQL database tables?

+2
source share

Number 3 is the best option you have proposed (slightly updated to clarify):

  • create a table for all of them for login and password and everything else that is common, and a separate table for all other things that are not common, and associate them with FK

Except for saving the password, save the hashed version of the salty password.

An alternative would be to assign groups and / or roles to your users. This can be more flexible than a fixed table structure that allows you to dynamically add new roles. But it depends on your needs, whether it is useful for you or not.

As Aaronout noted, in the main table, you need an AccountType so that the user can have only one of the roles. You must remember to check the value of this column when joining tables to ensure that the user has only one role active.

The unique foreign key constraint ensures that the user can have only one role.

+11
source share

More articles:


All Articles