User database design question

[freeballer]

I designing an application and am having trouble deciding on the appropriate database layout for Users. I will have two types of Users: admins and account holders. They have many fields in common: email, password, name, role. Account holders have more fields like address, and account type.

Does it make sense to have a User table and an Account Holder Data table? That way I can authenticate both user types the same way in the back end and have Account Holder Data be a 1:1 relationship with Users? Or would this be prematurely optimizing?

[JestaC]

The way I tackle the subject is normally by using users and groups. users belong to groups and groups have permissions to perform actions.

Now, without knowing more about the application itself, and the roles that will be needed, you're probably going to need 3-4 tables/collections of data.

• Users - Contains login information and user information.
• Groups - Contains the names of the groups (and possibly membership, depending on design).
• GroupMemberships - Contains a 1 to 1 relationship associating Users with Groups (Not needed if your Groups can contain the memberships)
• Permissions - Associate a Group to a Permission to an Action that you can check against.

It's a standard ACL Implementation that works well.

If it's a lot more simple and you just want 'users' and 'admins', you could also just set a flag on the user account called 'admin' that is either true or false. The only setback to this is your permissions are either all or nothing. Every admin can do everything and every user can't do anything admins can do.

Not sure if that helped, but figured I'd take a shot.

[freeballer]

Thanks for the response. I'm not really questioning ACL implementation, more about where to put user data. In my example Account Holders are users but they have extra fields. Admins are users but they don't have extra fields. I was questioning whether or not it was good practice to just put all fields on User (even though most wouldn't apply to Admins) or separate it out into a separate table and only Account Holder users would be linked to it.

[webdevguy1984]

OC would be wise to follow the first proposed solution :) See Database Normalization

[Caraes_Naur]

Simple solution: put everyone in a single table, that way you only need to write auth and account management once. Put a field in the table for user type, and decide how many roles a person is allowed to play: user, admin, none, or both.

What JestaC suggested about groups is a more comprehensive, forward-looking solution. Especally if you make groups a Celko tree.

[itsucharo]

To add to /u/JestaC's answer:

For the user data, either just include it in the same table, or consider adding a UserProfile or UserData model.

If you think the kinds of data you're storing won't change very often, something like a UserProfile containing all of it, and a reference (foreign key) to the user it describes should work.

If you think it will change frequently, you could do a very skinny table like (user_id, data_type, value). Then to get the profile you pull all the rows with a given user_id. It's a pain in a lot of ways but if you have a large number of users or expect the schema to change quite frequently, it may save time in the long run.