CODEWITHSUNDEEP
sql-server
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34160

Combining several tables of different types with common columns

I'm creating a database that has 6 different user types (web_users, sellers, repair_centers,...) they all have common options (columns) that will be used a lot, but each of the have some unique columns that other user types don't have.

Now I want to know is it wise to create just one user table with common columns and use another table as options table for those unique columns or should I create a separate table for each user type?

EDIT: actually all different types of will have same functionality in website. the differences are like sellers can have a logo that web_users can't. producers have Producer_ID (that is defined by government) that others don't have, and .... and all those unique columns are fixed values that no operations done on them.

Upvotes: 1

Views: 124

Answers (1)

Brian
Brian

Reputation: 3713

It's an interesting question. If it were me, I'd use two tables: one for all the common properties and another for all the outlying properties of the users. Something like this:

  CREATE TABLE _UserProperties(
    ID int ,
    Property varchar(1000) ,
    Value varchar(1000) ,
    UserID int 
  ) ;
  insert into _UserProperties(id, property, value, userid)
  values (1,'employee id', '123556',1),
        (2,'CustomerID', '345677',2),
        (2,'SalesGroup', 'ABFD34',2),
        (2,'SalesToDate', '2344',2),
        (3,'Project', 'cat juggling',3);


  CREATE TABLE _Users(
    ID int ,
    UserName varchar(50) ,
    UserAddress varchar(1000) ,
    UserType varchar(50) 
  ) ;      

  insert into _Users (ID, UserName, UserAddress, UserType)
  values (1,'Fred Jones','123 Mample','Customer'),
        (2,'Sam Smith','234 Oak','Employee'),
        (3,'Sally Black','345 Pine','Consultant');

Then you can combine everything with a simple query like:

 select * from _users a left join _userProperties b
 on a.id = b.userid

or use a pivot table to make the rows in to columns.

Here's a SQL Fiddle View

Upvotes: 1

Related Questions