database design with many type of users

Question

I am pretty new to database design. I am building a small asp.net mvc web application in which there are are various type of users who can login to the website.

So the table structure is,

Users table userid(PK), username , password , email , role, etc

Employees table: Eid(PK), userid(FK), firstname, joining date, etc

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

There is one to one relation between the users - employees table and users - clients table.

The general users , only have the details present in Users table.

Is this a right design?

Answer 1

If users can be only one type...

Users -> UserTypes

Users table would have a type_id, which would be the PK of a field in the UserTypes table.

Users

• userid 1
• name Jonathan Sampson
• email [email protected]
• 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, [email protected], 1, Unregistered

If Users can be multiple types...

Users -> UsersToTypes -> UserTypes

If a user needs to have the capacity to be more than one type, you'll introduce a third type:

UsersToTypes

• userid
• typeid

So if a user is both type1 (Customer), and type2 (President) you would have two records in UsersToTypes:

• userid (1 id for Jonathan Sampson)
• typeid (2 id for President)
• userid (1)
• typeid (1 id for Customer)

Extra Comments...

I wouldn't place the company-name in the users/clients table. You'll end up with that name existing numerous times if you have many of their reps in your database. Instead, create a Companies table that stores the following:

• CompanyID
• CompanyName
• ContactNumber
• StreetAddress
• City
• State
• Zipcode

This way, if a company ever goes through changes, you don't need to edit the clients table to update its name. You update its details in the proper table, and they are then globally updated.

Of course if you have multiple reps for each company, you'll need to create a RepsToCompanies table, similar to our UsersToTypes.