1

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?

  • 1
    Its better to save them in a single table and differentiate them separately using a binary field.. So that the data will be easy to collect.. Its my personal opinion.. – Adarsh Mohan Feb 4 '16 at 6:12
1

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

  • Thanks for your comprehensive answer. So, with user_role table, I'll have 5 tables about users? user , user_role , role , manager , customer , employee.. Will manager and customer different from current table? – Farzan Najipour Feb 4 '16 at 7:21
  • That's up to you, if you don't have any additional information to add other than they are a manager or customer you could just add a column to your user table indicating the category instead. Then you would just have user, role and user_role – Mr.Brownstone Feb 4 '16 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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