Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Im building this website for a small store and I was told that its better not to keep the login and the rest of the users information on 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?

share|improve this question
    
Yes, you should keep the login details in one table and permissions on another table for the user login id's. –  Sivakumar Apr 25 '13 at 5:23
    
Okay, i will consider it, thank you very much for your answer. –  Dolan Apr 25 '13 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. –  user67429 Apr 25 '13 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. –  user67429 Apr 25 '13 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. –  MichaelT Apr 25 '13 at 14:04

6 Answers 6

up vote 5 down vote accepted

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.

share|improve this answer
    
Hello dan, i dont really care about security this time, its just a simple project im working on to show my future employees and practice. Other than that i just need to find a way to make it simple and useful. –  Dolan Apr 25 '13 at 8:32
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)? –  dan1111 Apr 25 '13 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. –  Michael Kjörling Apr 25 '13 at 8:44
    
I dont have that much time actually, if i dont land a job soon im going to be on big trouble. Thanks for your answers, i will proceed as you suggested. –  Dolan Apr 25 '13 at 8:52
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. –  dan1111 Apr 25 '13 at 9:14

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

share|improve this answer
    
Thanks, i will consider it! –  Dolan Apr 25 '13 at 8:05
    
why is User and UserLogin seperate? I agree roles and permissions should be seperate –  tgkprog Apr 25 '13 at 17:13
    
Its just OP wanted it in seperate table.But there is no specific reason.. –  AnandPhadke Apr 26 '13 at 5:51

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.

share|improve this answer
    
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. –  Dolan Apr 25 '13 at 5:33
    
I'd say it sounds reasonable to keep them in the same table, then. –  Facio Ratio Apr 25 '13 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. –  Dolan Apr 25 '13 at 6:12

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.

share|improve this answer
    
You can read more here: msdn.microsoft.com/en-IN/library/yh26yfzy(v=vs.100).aspx –  DeveloperArnab Apr 25 '13 at 13:45

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.

share|improve this answer
    
Thank you for your answer, i will consider it. –  Dolan Apr 25 '13 at 6:09

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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