locked
Person & Organization, combined lookup table design RRS feed

  • Question

  •  

     

    Hello!

    Our application will use the following tables:

     

    1. PERSON - to store the following business objects:

    • Student
    • Employee

    2. ORGANIZATION - to store the following business objects:

    • Vendor
    • Bank
    • School

    Some sub-system/modules of our applications solely use persons and others use organizations only.

    But we have financial sub-systems/modules that uses them both, Person and Organization. For example, our Billing and Collection module looks ups to both person and organization for billing students and schools. Our Accounts Payable and Disbursement module looks to both person and organization to pay students, employees, vendors and banks.

    HOW WILL I PROVIDE A "LINK " FOR THE PERSON AND ORGANIZATION FOR THE FINANCIAL MODULES?

     

    We are looking at the following options:

    1. Include a GUID column for each of the Person and Organization tables. Create a View, to UNION the Person and Organization tables, with the GUID column as the lookup ID column.
    2. Create a base table, say Profile.Common, that will be the Parent Table of both the Person and Organization tables. The PK ID of the Common table will become "source" of Person.ID and Organization.ID. The Common table will serve as the lookup table.

    Which will be a better design? Are there still other options better than what we are considering?

    Please help. Thank you.


    a filipino newbie
    • Edited by superkuton Thursday, November 4, 2010 11:12 AM
    Thursday, November 4, 2010 5:29 AM

Answers

  • You could use a Union All query to combine the two tables, and include
    another column for Type ("Person" or "Organization") to determine
    which is which.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Marked as answer by Tom Li - MSFT Friday, November 12, 2010 11:34 AM
    Thursday, November 4, 2010 6:25 AM

All replies

  • You could use a Union All query to combine the two tables, and include
    another column for Type ("Person" or "Organization") to determine
    which is which.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Marked as answer by Tom Li - MSFT Friday, November 12, 2010 11:34 AM
    Thursday, November 4, 2010 6:25 AM
  • OrganizationID column in Person table will link the person to Organization

    CREATE TABLE Organization(OrganizationID INT NOT NULL PRIMARY KEY, OrgName VARCHAR(50),

    .................

    CREATE TABLE Person (PersonID INT NOT NULL Primary KEY,Name VARCHAR(50) NOT NULL,

                                        PersonAddress VARCHAR(100) NOT NULL, OrganizationID INT

                                          FOREIGN KEY REFERENCES Organization([OrganizationID]) ON DELETE CASCADE ON UPDATE CASCADE ,

    .................(

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, November 4, 2010 6:39 AM
  • Do you need a third person category -- "Organization Contact"?

    Another alternative is to have "Contact" as your top entity.  This entity then has 5 subcategories:

    • Student
    • Employee
    • Vendor
    • Bank
    • School

    "isIndividual" becomes an attribute of the "Contact" entity along with the discriminator -- "Contact_Type".

    Thursday, November 4, 2010 3:13 PM