0

I'm building a website for a small store and I was told that its better not to keep the login and the rest of the users information in the same table.

Now im wondering, what is the best way to implement this website's database?

Some of the database tables are:

  • Customers
  • Providers
  • Products
  • Etc..

I need to have different users, after someone is logged in the site, depending on their rights they can do a series of things:

  • If they are a customer they can order stuff
  • If they are employees they can order stuff, manage stock, add customers, etc
  • If they are the owner or administrator they can add employees and everything else.

Should I keep the login and the rest of the information of the user in the same table?

Improve this question
10
  • Yes, you should keep the login details in one table and permissions on another table for the user login id's. Commented Apr 25, 2013 at 5:23
  • Okay, i will consider it, thank you very much for your answer. Commented Apr 25, 2013 at 5:32
  • 1
    Although I'm not very knowledgeable on the subject, I believe it is good practice to separate concerns. If I were to implement this application, I'd have a, say, a logins table which holds data that's just enough to log in a user securely and map him to a user profile. The user profile might have consisted of permissions and user_attributes tables which I may use to decide what actions a particular user take and store some necessary metadata about him (billing address, phone number etc.). I'd suggest designing your application by visualizing first too. Commented Apr 25, 2013 at 8:49
  • @dan1111 If your question is about the example, I don't claim it is a good idea. But I think that separation of concerns is a good idea and I just wanted to illustrate it with an example. Commented Apr 25, 2013 at 9:02
  • 1
    In the future, instead of reposting the question, please flag it for migration. This way there is a single question on the sites with all of the answers to it rather than two questions, each with their own answers. Commented Apr 25, 2013 at 14:04

6 Answers 6

Reset to default
6

There is no security reason to separate the login information from the rest of the user information. It doesn't make your site safer in any meaningful way, so you should just organize the tables by what is most logical.

The real security issue in this domain is that you should not be storing plain text passwords. Only store a salted hash of the password.

That being said, it probably makes logical sense to separate the user information into a few tables, because you have different users with different roles. Have a user table with all the common information, then customer, employee, etc.

Improve this answer
3
  • 10
    @GustavoRodríguezSuarez, it is a good idea to care about security even in that case. After all, what are you practicing for (and what do you want to show employers)? Commented Apr 25, 2013 at 8:39
  • 1
    Fully agree with @dan1111 on the security aspects. Especially if it is a sort of "hobby" project intended to show prospective employers what you can do. You have basically all the time in the world - use it to design the system at least as well as you would if you were under serious time and requirement constraints. Take care to store information properly, avoid SQL injection attacks, etc etc. Commented Apr 25, 2013 at 8:44
  • 3
    @GustavoRodríguezSuarez, not to belabor the point, but if I were a potential employer looking at your work and saw that you used a secure hash for the password, I would think "This guy really knows what he's doing". If I saw a plain text password, I would think you were an amateur. So, it might be worth investing the time in this particular feature. I wish you the best of luck on your job search. Commented Apr 25, 2013 at 9:14
3

I think below table design would suit your requirements.

**User** ----------- UserId UserRoleId UserFname UserLName . . . **UserLogin** ------------ UserId LoginName Password **UserRoles** --------------- RoleId Type

Now based on the UserRole you can decide which all features need to be shown on website. Here UserRole could be Customer,Provider or Admin

Improve this answer
2
  • why is User and UserLogin seperate? I agree roles and permissions should be seperate Commented Apr 25, 2013 at 17:13
  • Its just OP wanted it in seperate table.But there is no specific reason.. Commented Apr 26, 2013 at 5:51
2

It may or may not make sense to have the login and customer information in the same table. As always it just depends on the needs of the business/application. If employees and administrators are "customers" (and more), then you could get away with putting the logins in with the customer information.

However, I tend toward keeping the logins in their own table both for clarity and for the possibility that a future role may not have customer functions. It provides more flexibility and a better separation of concerns.

Improve this answer
3
  • This is a very simple and robust website, i do not need maximum functionality, its just an exercise and a project to show my future employers, so maybe i should just keep the login in the same table? Also employees and administrators are customers and more. Commented Apr 25, 2013 at 5:33
  • I'd say it sounds reasonable to keep them in the same table, then. Commented Apr 25, 2013 at 5:41
  • Thanks for your answer. I might keep it to the same table, its probably going to be a lot easier to implement the 'Register' page. Commented Apr 25, 2013 at 6:12
0

I think you should use the ASP.NET membership functionality. You can create a separate database structure with that and many of the security level hard working is done for you by the ASP.NET.

Improve this answer
1
0

Since the design is for a small website, I think you can have a normalize database up to 2Nf.

By keeping the user login credentials in one table and their information's in another table. As you mentioned this is an exercise project try to make a good impression demonstrating professionalism.

Have separate role and permission tables. Depending upon the role identify, the permission.

Improve this answer
0
0

You could download src code of some good framework for this (drupal for example cant use the code but the db is good). What I have always seen is to have separate roles and/or permissions tables. So 1 table has site wide roles, another has permissions (like a admin user who can see reports but not edit users); and the users table has the actual logins.

Another table has the link -> user ids to permissions and another has user ids to roles

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.