Two type users configuration

database-design

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.

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.

Related Solutions

Separate or Combine 2 Tables with Similar Attributes

I'd say "Customer" and "Employee" are two roles a person can play in your system. Thus, I'd add concept of "Role" to your design. Then you can store customer related attributes in one table, and employee related attributes in another. You will have something like

User (user_id, name, ...)
Role (role_id, description)
UserRole (user_role_id, user_id,role_id, date_from, date_thru) 
-- note, dates are quite important(at least date_from should be not null)
CustomerRoleAttribute(user_role_id, NewsUpdateSubscription)
EmployeeRoleAttribute(user_role_id, salary)

Also, to be absolutely correct, Customer and Employee roles require second part - for instance, I'm a customer of AAA company, and an employee of BBB, so my role is defined by relationship of certain type with other user (which is organization in case of "Employment" relationship).

You can find out more about this approach in "The Data Model Resource Book" by Len Silverston

Mysql – How to store a 64-digit key of alpha numerical values into MySQL Database

Storing: Use a field with datatype BINARY(64) or CHAR(64) CHARSET ascii COLLATE ascii_bin.

Generating:

This is not an SQL question because it should not be done in the database. It should be done in the UI layer that receives and checks passwords, etc.

So, given that you should write this in Perl/PHP/VB/Java, you have much stronger tools with which to generate the corporate password.

Of what use is a password that everyone knows? As soon as a disgruntled employee gets fired, the password may become known to the world.

I'm serious -- some things do not belong in SQL; instead they should be in the code that access the database.

But if you must do it (for the class) in SQL, consider creating a table of the possible characters, one per row, plus another table of (at least) 64 distinct integers. Then use a JOIN, GROUP_CONCAT, ORDER BY RAND(), LIMIT 64, and probably a subquery to get the INTs.

Best Answer

Related Solutions

Separate or Combine 2 Tables with Similar Attributes

Mysql – How to store a 64-digit key of alpha numerical values into MySQL Database

Related Question