Mysql – Design database –

database-designMySQL

I want to create database. I have two kind of clients which admins and employee should communicate with them ; Customers and Manager.

I'm not sure about which place employee's place on database. is it necessary to use store them on one of users child or insert them on different table is better idea?

Here is my general schema:

Users:

 | id | username | password  |
------------------------------
| 1  | user1    | *******   |
| 2  | user2    | *******   |
| 3  | user3    | *******   |

Manager:

| id | manager | user_id
-------------------------
| 1  | manager1    | 13
| 2  | manager2    | 31
| 3  | manager3    | 12

Customer:

| id | customer | user_id
-------------------------
| 1  | customer1    | 3
| 2  | customer2    | 91
| 3  | customer3    | 162

method 1:

employee table inheritance from users.
Employee:

| id | employee | user_id
-------------------------
| 1  | employee1    | 33
| 2  | employee2    | 61
| 3  | employee3    | 10

method 2 :
emoloyee as an another independent table:
employee:

| id | employee | role_id
-------------------------
| 1  | employee1    | 1
| 2  | employee2    | 2
| 3  | employee3    | 3

employee-role:

| id | role | role_id
-------------------------
| 1  | admin   | 1
| 2  | supervisor    | 2
| 3  | normal    | 3

Which of above method is correct?

Best Answer

The correctness of a database design is specific to the requirements that you gather prior to building it. In simple terms, one size does not fit all and in many situations you will find more than one solution to your problem. The skill is in determining which solution best fits the requirements that you have. There will be occasions when the solution you choose is wrong and you must go back and fix it, it happens to everyone, don't get disheartened about it.

Regarding your specific case, I wouldn't say that there is anything that is bad with what you have chosen, instead I would give you some guidance.

You have a "base" table called users from which customers, managers and employees extend. This is fine, just ensure that any columns that are common between all derived types are in the users table. Only data specific to the derived types should be in those tables.

My personal preference for role management is to have a roles table and then have a user_roles table which implements a many-to-many relationship as it may be possible for a user to be in more than one role. However, if it is not possible for a user to be in more than one role then you could add an additional key to the users table which references the role that they are in.

I still prefer the additional table design myself because its easier to scale. So for instance if you decided that initially a user can only belong to one role, then you would just place a unique constraint over the role and user id columns. In the future, if it becomes possible or required that a user can be in multiple roles it is far easier to drop that constraint than it is to redesign your database.

Keep it simple, and try not to repeat yourself. Remember that database design is not just about the now, it is about attempting to plan for the future as well - you don't want to have to keep redesigning it every week (not to say that doesn't happen sometimes).