Two type users configuration


I am developing an app that has two types of user: Customers and employees.

I have planned to make the supertype as user that contains only:

  • id
  • username
  • password

While there would be two subtypes (customer and employee). Each of them has different specific data, namely,


  • id
  • purchases
  • name
  • age


  • id
  • badge
  • name
  • age.

Could someone help me by suggesting a structure with what foreign key to put and implement?

I'm clearly new to making databases.

Best Answer

Make Customer_Id a foreign key referencing User_Id. Employee_Id will also be a foreign key referencing User_Id. As a new user is INSERTed and the User_Id generated use that same value in Customer_Id or Employee_Id (or both) as appropriate.

If name and age are common to both employees and customers, hold them in the super-type (user). Never ever hold age. It changes all the time. Hold Date_of_birth, which never changes, and calculate age when it is required. Even if you need the user's age when the row was written you would be better off with Date_of_birth and Row_created_date.

A customer should be allowed to have many purchases. Storing them all in a single column breaks first normal form. If your implementation is in a document DBMS then that's OK, but should be acknowledged. If you plan to use a relational database you really should pull it into its own entity type early on, during design.

Don't store the password in plain text. It should only ever be the salted, encrypted version that is persisted. There are plenty of articles on the web that explain why and how to implement this.