locked
One Table for many Entities RRS feed

  • Question

  • Hi, I use VS2010, SQL Server 2008 Express, EF4.1.

    Q1/ What if I set one table to hold Company, Customer, Vendor and Employee data, should I set corresponding discriminator columns ( let's say : IsCompany, IsCustomer, IsVendor, IsEmployee )? Please note that one customer could also be a vendor.

    Thanks.

    Tuesday, May 1, 2012 4:44 PM

Answers

  • Why do you need to care for that? You hold Customers in Customers  table and Vendors in Vendor.

    CREATE TABLE Customers (CustID INT, CustName VARCHAR(20).....)

    INSERT INTO Customers  VALUES (1,'Peter'),(2,'Alex')

    CREATE TABLE Vendors (VendID INT, VendName VARCHAR(20).....)

    INSERT INTO Customers  VALUES (100,'Peter'),(200,'John')

    Do you need a report to identify who is Customer as well as Vendor? Then  you can have IsVendor column in Customer table only..


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 5:17 AM
  • >>>what if I want to get the list of vendors?

    Just SELECT * FROM Vendors, no need UNION ALL....

    >>>An Employee can also acts as a customer, should I add IsEmployee column to the Customer table?

    Yes...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 6:42 AM
  • You need to follow general database design table normalization.... It is hard to suggest something useful especially for design question as only you know better all business requirements....

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 9:22 AM

All replies

  • Is there a requirement for only only table? In general this is very poor design pattern for a relational database design.

    I would start with one table for each entity and go from there. If there are common attributes like address, demographics etc, you may want these to have their own tables, this way one person could be both a customer and vendor.

    Bill


    William F. Kinsley

    Tuesday, May 1, 2012 5:09 PM
  • Hi,

    Thank you WFKinsley for your answer, if customer and vendor are maintained in separate tables, how come a customer be also a vendor?

    Tuesday, May 1, 2012 5:24 PM
  • Hi,

    Let's call that table <Partner> with <PartnerId> as the primary key.

    1/ That <PartnerId> can go into different tables ( ItemDocument, Transactions...), and it's easy to lookup for the corresponding <Partner>.

    2/ If the user wants to write check, it's easy to get all <Partner>s in a lookup combo box.

    3/ If a customer acts also as a vendor, it's easy to show the balance ( Sales Balance - Purchases Balance )

    4/ If the user wants to view all transaction of a given <Partner> in one list ( or filter by transaction type ), this could be achieved so easy even if the customer is also a vendor ( Sales and Purchases are shown on the same list )

    --- At least I want the customer and the vendor to be on the same table

    Tuesday, May 1, 2012 5:52 PM
  • A good example is in the AdventureWorks database ( you can download the ERD from here).  The person table is used as a direct join for the Employee and Customer, in indirectly via a cross reference table for BusinessEnity / Vendor.

    Bill


    William F. Kinsley

    Tuesday, May 1, 2012 6:05 PM
  • Hi,

    The person in AdventureWorks database hold just contact data ( and that's fine ), though Customer, Vendor and Employee live in separate tables, without having any relationship between these tables.

    Wednesday, May 2, 2012 4:41 AM
  • Why do you need to care for that? You hold Customers in Customers  table and Vendors in Vendor.

    CREATE TABLE Customers (CustID INT, CustName VARCHAR(20).....)

    INSERT INTO Customers  VALUES (1,'Peter'),(2,'Alex')

    CREATE TABLE Vendors (VendID INT, VendName VARCHAR(20).....)

    INSERT INTO Customers  VALUES (100,'Peter'),(200,'John')

    Do you need a report to identify who is Customer as well as Vendor? Then  you can have IsVendor column in Customer table only..


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 5:17 AM
  • Hi,

    Thanks  Uri Dimant for your suggestion, what if I want to get the list of vendors? I query against the Vendor table and union the customers that have IsVendor = 1.

    An Employee can also acts as a customer, should I add IsEmployee column to the Customer table?

    Wednesday, May 2, 2012 6:38 AM
  • >>>what if I want to get the list of vendors?

    Just SELECT * FROM Vendors, no need UNION ALL....

    >>>An Employee can also acts as a customer, should I add IsEmployee column to the Customer table?

    Yes...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 6:42 AM
  • Hi 

    I will Suggest you to keep different tables for each entity. for Customer and Vendor you can keep ref. of Customer in Vendor Table or Vice-Versa. Something like this

    Create Table Vendor(VendorId int Primary Key,VendorName Varchar(40))
    Create Table Customer(CustomerId int,CustomerName Varchar(30),VenderId int Null references Vendor(VendorId))

    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com

    Wednesday, May 2, 2012 6:46 AM
  • Hi,

    I think a better solution could be such :

    master table: <Partner> ==> PartnerId as PK ( <Partner> table should contain all common properties )

    customer table <Customer> ==> PartnerId as FK,PK ( primary and foreign key which references <Partner> table )
    vendor table <Vendor> ==> PartnerId as FK,PK ( primary and foreign key which references <Partner> table )
    ...

    If I insert a detail row such as a customer or vendor row, I should first insert a row in the master table <Partner> to get its PK.

    All transactions that refer to customers, vendors, employees, Company should refer only to <Partner> table.

    Edit:

    Oops! same problem, how to know if a vendor or employee is also a customer?


    • Edited by YussafFree Wednesday, May 2, 2012 6:53 AM
    Wednesday, May 2, 2012 6:48 AM
  • Better? Then  you need an extra INSERT to maintain Partner Table...

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 6:51 AM
  • >>>what if I want to get the list of vendors?

    Just SELECT * FROM Vendors, no need UNION ALL....

    /uri_dimant/

    I see, you inserted the customer data ( which is also a vendor ) twice, once in the Customer table and then in the Vendor table.

    The problem is as such :

    1/ If I should update the data I'll do it twice.

    2/ If I have to pay the vendor, I can't tell what I've sold him.

    Wednesday, May 2, 2012 7:13 AM
  • For me they are two different person... I just case of Vendors and Customers .... But If I want to know who are both I just query Customers table WHERE IsVendor=1 (same name same info..)



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Wednesday, May 2, 2012 7:21 AM
  • Better? Then  you need an extra INSERT to maintain Partner Table...
    /uri_dimant/

    The insert curve is in decrease, the first two months, there will be a lot of insert, then the curve stays constant at a rate of three inserts a day.

    In Contrast the lookup is heavier, beginning from the third month and up going.

    Wednesday, May 2, 2012 7:31 AM
  • Ok, I am sure that you know business requirements better than me :-)))) go for it

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 8:08 AM
  • Hi,

    To get all customers : SELECT PartnerId FROM Partner WHERE IsCustomer = 1

    To get all vendors : SELECT PartnerId FROM Partner WHERE IsVendor = 1

    ....

    Should I go this way?

    Wednesday, May 2, 2012 8:24 AM
  • I have already suggested my way, but you can go for this implementation as well

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 8:26 AM
  • Hi,

    What about performance? Is it better to keep Purchase Documents in one table and Sales Documents in another table? Though they have the same structure.

    Wednesday, May 2, 2012 8:35 AM
  • By documents, do you mean files or just text?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 8:49 AM
  • By documents, do you mean files or just text?

    /uri_dimant/

    I mean Estimate, SalesOrder, ItemReceipt...

    Thanks.

    Wednesday, May 2, 2012 9:00 AM
  • I do not understand you what are  Estimate, SalesOrder, ItemReceipt..?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 9:03 AM
  • Hi,

    These are tables to record Purchase and Sales documents.

    Wednesday, May 2, 2012 9:13 AM
  • You need to follow general database design table normalization.... It is hard to suggest something useful especially for design question as only you know better all business requirements....

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 2, 2012 9:22 AM
  • The Customer, Vendor and Employee link via FK to the BusinessEnity table. (See the table/column descriptions in the DB). While the  AdventureWorks db has some good example of a pure database design, it is unfortunately there does not appear to be any good documentation on the AdventureWorks design.

    Creating tables is simple, creating a good database design is more difficult. 

    William F. Kinsley

    Wednesday, May 2, 2012 5:52 PM