User, client, administrator account in 3 different tables?

Question

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

• User: To use the web application for free.
• Client: for advertising and obtaining a company logo.
• Admin: to edit and delete files

Should all these three be in separate tables or in one column with the name "account_type", where I can mark it as a User, Client or Administrator?

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

thanks

Answer 1

In general, a person can be a user, client, and administrator, so I would start with the person table with the columns IsCustomer , IsUser , IsAdmin . Later (for a quick search) you can add separate Admin , Customers , Users tables with FK to the person table.

EDIT:

A typical case might be:

• 5 million users
• 1000 customers
• 10 admins

In general, having separate tables for clients and administrators should speed up any query related to the administrator / client.

Answer 2

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

If a user can have more than one type of account, then you must have another table with a foreign key,

Sampling structure (data types are SQL Server and are offered only)

User table

• UserID - int
• Username - varchar (25)
• Password - varchar (25)
• The name is varchar (50), etc.

Role table

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

User_Roles table

• UserId - int (with the foregin key in the Users table)
• RoleId int (foreign key for the role table)

Answer 3

The pros and cons vary depending on the size and complexity of your system.

I would break it into User, Role, UserResources

User (defines basic information)

User roles

• FK-> RoleType

Role_Type (user, administrator, client, perhaps permissions, or you can break it).

UserResources (media)

• FK-> User