Geek Answers Handbook

Database design with many types of users

I am new to database design. I am creating a small asp.net mvc web application that has various types of users who can access the site.

So the table structure,

User table userid (PK), username, password, email address, role, etc.

Employees table: Eid (PK), userid (FK), name, join date, etc.

Clients table: Cid (PK), userid (FK), name, company name, etc.

There is one relationship between the user and employee table. and the users table is clients.

General users only have the data presented in the "Users" table.

Is this the right design?

+6
database-design
source share
1 answer

If users can only be one type ...

Users → UserTypes

The user table will have id_type, which will be the PK field in the UserTypes table.

Users

  • userid 1
  • name Jonathan Sampson
  • email foo@bar.com
  • typeid 1

UserTypes

  • id 1

  • Type Unregistered

    SELECT users.name, users.email, users.typeid, usertypes.type FROM users LEFT JOIN usertypes ON (usertypes.id = users.typeid) WHERE (users.id = 1)

Returns: Jonathan Sampson, foo@bar.com , 1, Unregistered

If users can be several types ...

Users -> UsersToTypes -> UserTypes

If the user must have a capacity of more than one type, you enter the third type:

UsersToTypes

  • user ID
  • Typeid

So, if the user is both type1 (Customer) and type2 (President), you will have two entries in UserToTypes:

  • userid (1 identifier for Jonathan Sampson)
  • typeid (2 id for president)
  • userid (1)
  • typeid (1 identifier for the client)

Additional comments ...

I would not put the company name in the users / clients table. You will have such a name that exists many times if you have many of your representatives in your database. Instead, create a company table that stores the following data:

  • Companyid
  • The name of the company
  • ContactNumber
  • Streetaddress
  • Town
  • State
  • Zipcode

Thus, if a company ever makes changes, you do not need to edit the customer table to update its name. You update your data in the corresponding table, and then globally update.

Of course, if you have several repetitions for each company, you need to create a RepsToCompanies table similar to our UsersToTypes.

+7
source share

More articles:


All Articles