User, customer, admin account in 3 different tables?

Question

In my web application I will have three types of accounts.

• User: for using the web application for free
• Customer: for advertising and getting a Company Logo
• Admin: for editing and deleting stuff

Should all these three be in separate tables or in one with a column named "account_type" where i can mark it as User, Customer or Admin?

What are the pros and cons for both? Is there a best practice for this?

Thanks

Answer 1

In general, a person can be user, customer and admin -- so, I would start with a Person table with columns IsCustomer, IsUser, IsAdmin. Later (for fast search) you may decide to add separate tables Admin, Customers, Users with FK to the Person table.

EDIT:

A typical case may be:

• 5 million users
• 1000 customers
• 10 admins

In general, having separate tables for customers and admins should speed-up any admin/customer related query.

Answer 2

If a user can only be one type, you'd be better off with one table and a bit field for IsAdministrator, etc.

If a user can be of more than one account type, you should then have a different table with a foreign key,

sample structure (data sypes are SQL Server and suggested only)

Users table

• UserID - int
• Username - varchar(25)
• Password - varchar(25)
• Firstname - varchar(50) etc...

Roles table

• RoleId - int
• Role Description - varchar(25)

User_Roles table

• UserId - int (with a foregin key to the Users table)
• RoleId int (foreign key to the Roles table)

Answer 3

Pros and Cons vary based on the size and complexity of your system.

I would break it up into User, Role, UserResources

User (would define basic information)

User Roles

• FK->RoleType

Role_Type (user, admin, customer, possibly permissions or you could break this out further).

UserResources (media)

• FK->User