0

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,

Customer:

  • id
  • purchases
  • name
  • age

Employee:

  • 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.

2
  • You clearly already have some part of your implementation details done - post the current table structures and some more details around what the system needs to do as a minimum - I'd also post any ideas you've had already. Commented Dec 19, 2018 at 8:24
  • And what you will do when Employee and Customer is the same man? Use sparse table (it is easier for beginner) or Main table + 2 particular tables for each type of a user referenced to Main.
    – Akina
    Commented Dec 19, 2018 at 8:51

2 Answers 2

0

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.

0

I'd recommend 4-table structure.

  1. Person. Stored info about a (wo)man. One body - one record.
  2. User. Stored credentials info (login/password).
  3. Customer. Stored info specific for a customer. References to Person and to User.
  4. Employee. Stored info specific for a employee. References to Person and to User.

This allow:

  • one person may be both customer and employee
  • one person may have more than one customer account (personal and agent, for example)
  • one person may have more than one employee account (permanent and temporarily assigned, for example)

... and some another possibilities.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.