0

We are running single database for rest app server. We have three types of users

  1. for customer
  2. for administrator, and
  3. for partners

Currently they have different tables and username and password are also in separate respectively Now We need to refactor this schema as user are expanding. So should a single table User with Role table is OK ? (Here Role can be admin, partner or customer, manager).

OR

Should we a keep as it is as We will be having issue if we use User and Role table:

  1. if an admin acquire a username then that username can not be the same again for customer or partner due to unique constraint.
  2. I think user role can not be as "Customer" as customer is not a Role. Role can be admin, manager etc

I think this is not the right way to keep in single table. What are your suggestions?

qwerty_so
  • 35,448
  • 8
  • 62
  • 86
Asad Khan
  • 473
  • 1
  • 8
  • 21

3 Answers3

3

I think you should create three tables for your user management, considering the fact that one user can have several roles (ex:- admin can also be a manager Or Customer can also be a partner). So User table and Role table have a Many-To-Many relationship. In order to create this relationship, you have to create 3rd table having userId and roleId as composite primary key.

additionally, i noticed that you are going to save user's passwords in the database. For the security reasons do not store passwords in plain-text. Instaed store the hash of the password using one way hashing algorithm. You can read more about it from here -> Best way to store password in database

Community
  • 1
  • 1
Nuwan Sudarshana
  • 235
  • 2
  • 8
  • No we are saving password using BCrypt. But problem is making a single table with role as user admin or customer or partner can acquire the same username. We are also doing Social network login therefore they can have same social network id (fb or gmail) against userId. We will be in dilema due to managing them. – Asad Khan Mar 21 '17 at 11:16
  • For any table design, try to use a numeric primary key. In this case, use Auto increment UserID as a primary key for the user table. To identify a user uniquely, gather user email address when registering a user. So you will not need to worry about conflicting with other users when thay use social login. Remember to add user details to the database, when thay use the social login for the first time. – Nuwan Sudarshana Mar 21 '17 at 12:05
  • 1
    You didn't catch the idea behind that answer. You have a table containing all users logins as password hashes (and other details) but without information about their roles (called User). It'll have one entry for each user regardless how many rules a particular user actually has. Then a table listing all possible roles (called Role). You'll have only 3 rows here: one for Customer, one for Administrator and one for Partner. Then you have a third table that informs which user has which role and the same user might have more than one role assigned there. – Ister Mar 22 '17 at 06:48
  • Note also that this is one of possible solutions. It strongly depends on the requirements and assumptions taken. – Ister Mar 22 '17 at 06:50
1

Yes, it would be better to keep separate tables because of the following reasons:
1. As you specified, Customer is not a role.
2. Since administrators would be limited in number, there is no point in fetching the record for authentication/authorisation from a large data set having the customers. It will hinder the performance.

Arjun Duggal
  • 31
  • 2
0

User
id
userId
role (foreign key)
etc..

Role
id
name
etc

the above structure is best practice. if you really need extra fields for admin, partner or customer you can create separate entity for each one and you can refer user as a foreign key like as follows


Customer
id
name
user(foreign key)
etc

Richardson. M
  • 852
  • 2
  • 17
  • 28
  • where i will keep username and password ? only in user table ? – Asad Khan Mar 21 '17 at 11:09
  • @JohnnyMartin, Yes you can keep username & password(encrypted) in user table itself. – Richardson. M Mar 21 '17 at 12:19
  • As per your design user can not have many role but anyways work for me as we have a single role. but instead role i will make it user_type table so anyone can understand that user is of single type. – Asad Khan Mar 22 '17 at 04:48