User hierarchy database structure

[lemmin]

I am working on setting up a user hierarchy system for a website and I am considering two different ways of doing it.

 

The first way is to have one users table and separate tables for each different user type. To change a user's access rights, his/her user id is put into one of those separate tables. The second way is to only have the one users table and have a user type field where an integer id represents the access rights that the user has.

 

The benefit of using the first way is that it would be possible to store extra data that relates to that user type in the separate tables. This could get rid of some null fields in the user table. The benefit of using the second way is that the query could simply check if the user's type is greater than (or less than) a certain value and include multiple user types for access rights.

 

Does anyone else see any other benefits or downfalls of these two structures? Or, does anyone know of a better way other than these two ways to create a user hierarchy? Which way would you do it?

 

Thanks for any input.

[mapleleaf]

I would have it all in one table. You can always join the other table that has info for the level. It really depends on how many levels and how much other info.

[lemmin]

Any particular reason for that choice? There will probably be about five or six different levels and they will probably all have unique information separate from the default information in the users table.

 

Thanks for the input.

[fenway]

In general, more tables are free, at least until traffic becomes a serious issue.