views:

199

answers:

1

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?

+1  A: 

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

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.

Jonathan Sampson
What if a user becomes an employeee or a client, or both? With the original design, this is not a problem, but with an explicit user_type_id, this causes havoc.
Steven Sudit
Actually the role column can distinguish between different types of users.
San
Employees/Customers should be one table. I've updated my answer.
Jonathan Sampson
But the problem is Employees and Customers have different details to be stored, eg. our customers are actually people from different companies , so I need to store the "CompanyName" as one of the column in the clients table.
San
Not really. Add a company_id field and add a companies table. That way if you ever want a list of companies you do business with, you don't query your users table.
Jonathan Sampson
Plus if a company name ever changes, you don't want to update 300 records in your clients table. You want to update 1 in your companies table.
Jonathan Sampson