How hard can it be maintaining only single table for users of different roles

If you get to design a Database table for users, will stuffing all users with different role be best option in long term maintainability, database programming, web development. Take this situation as a example , I am asked to design a Database for Forum & manager wants only one table for all the users be it Admin, Moderator,Registered,Guest roles. I think it would be not such a good idea (and I am unable to find reasons, just inner feeling). How can I persuade manager that having many tables like for admin & another for all is a good idea. You can also apply similar开发者_如何转开发 thing to basecamp site where here is the hierarchy:

Company
- Admin
- Employees
- Clients
- Managers

Even though they only differ by roles, having different tables is good right?

---

A single table for all users will definitely be the easiest to manage and code against. Just have a column defining the user type, or a permissions table that keeps track of which permissions the given user has. Having separate tables will be a major coding hassle, and conceptually, isn't most of them exactly the same? E.g. admin is just a moderator with more permissions, same goes for user vs moderator, etc.

---

It all depends, you need to go away and find out. Its all about the attributes of the entities.

You probably want one table for all users where you will store all the attributes that are common to every user. In this User table you could also store a category attribute that will relate Users to other tables where category specific information is stored. These other 'Category' tables would obviously have a foriegn key to the User table.

I can't tell you the right answer without doing the appropriate requirements gathering and analysis necessary to develop good software and databases.

For more information on why you structure the database this way research Third Normal Form and the other levels of data normalisation.

---

Your boss is probably right - the best design for a structure like this would be to have a single 'User' table which has a column called 'Role' (assuming each employee can only have one role).

Your design means that if a new employee type was added, a whole new table would have to be added to the database, whereas in the example I've given above, you'd only need to add a row of data into your 'Role' table.