put login and password in one table or in multiple tables for each type of user ?

Question

I have different 3 types of users and each type of user can have columns and relationships with tables that another type doesn't, but all of them have login(Unique) and password,
how would you do:

• create a table for each type or
• create one table for all of them or
• create a table for all of them only for login and password and separate for all the other things and bind them with a FK
• something else

Answer 1

Number 3 is the best of the options you suggested (updated slightly for clarification):

• create a table for all of them for login and password and anything else that is shared and a separate table for all the other things that are not shared and bind them with a FK

Except don't store the password, store a hashed version of a salted password.

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

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

A unique constraint on the foreign key ensures that a user can only have a role once.

Answer 2

I had this exact question when building a recent system. Here's a thread that was super-helpful for me: http://stackoverflow.com/questions/600684/object-oriented-like-structures-in-relational-databases.

I went with the disjoint subtypes solution, described well here: http://stackoverflow.com/questions/561576/polymorphism-in-sql-database-tables/561960#561960