Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2010
    Posts
    7

    Question Structure; Company, Client, Contact, Individual

    First, let me say, I'm very green at this... My database experience has been limited to mere flatfiles with MS Works (Laugh if you wish, I understand.)

    I'm at the point now, where I am about to embark on a redesign of this flatfile (migrating to a relational structure)... I've got a long road ahead, to be sure. But I think I've got all the pieces in place, the concepts, and plenty of resources to rely on to get the job done.

    I'm in the planning phase and the first table(s) I go to make brings up a situation I'm not sure how to proceed. (My first step and I trip already.)

    Here's the situation. The db ultimately is going to be an order-entry system; customers, jobs, etc.

    Some customers are Companies... some customers are individuals working for companies (more than one individual at company, different teams, etc.)... and some customers are just individuals.

    So the question is how do I build the tables?
    ID, CompanyName, Address, phone, etc.
    ID, IndividualName, CompanyID, Ext, whatever?
    ID, IndividualName, Address, phone, etc.

    The solution is right on the periphery of my thought process... I can almost see it.

    Any suggestions?

    TIA,
    ~Woody

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The proper way to model this, though it can be a bit difficult to get your head around, is to represent both companies and individuals in a single table which I typically call "Entity". You can relate entities to each other as employer/employee relationships, company/division relationships, company/employee relationships, and owner/company relationships. Some comples constraints need to be created to ensure referential integrity, but I have found this to be the most robust schema for representing this logical data model.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2010
    Posts
    7

    Talking

    Quote Originally Posted by blindman View Post
    You can relate entities to each other as employer/employee relationships, company/division relationships, company/employee relationships, and owner/company relationships. Some comples constraints need to be created to ensure referential integrity, but I have found this to be the most robust schema for representing this logical data model.
    Yeah... this is kind of where I'm heading to... I think I may have it (or at least the basic of it).

    1 Client (ClientID, ClientName, AddressID)

    2 Individual (IndividualID, ClientID, LastName, FirstName, CompanyID)

    3 Company (CompanyID, ClientID, CompanyName)

    4 Address (AddressID, Address, Address2, City, State, etc.)

    Where each of the first items are the primary key... ClientName is the Individual's Name(s) unless they are part of a company, then it's the CompanyName with the individual as a contact.

    I think that's right. (Swear to goodness I don't know why they call it normalizing... it ain't normal.)

    ~W

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Here's a good template you should take a look at:
    A Universal Person and Organization Data Model

  5. #5
    Join Date
    Jan 2010
    Posts
    7
    Thanks dportas,

    I started to read it and then realized I was running down the clock at work. I'll try to finish reading it tonight... (no O/T)

    But what I gleaned so far is showing me I might be on the right track.

    Thanks again.

    ~W

  6. #6
    Join Date
    Jan 2010
    Posts
    7
    I haven't decided yet, whether "I got it" or that I'm on the verge of epiphany.

    I've got three tables planned to create a "customer" entity ("customer" is what will be shown on the top of the form... basic information only, I'll worry about the other stuff (phones etc) later)

    So I have my first table:

    customer (customerID, customername)

    Seeing how I have basically two types of customers; companies and people that suggests two more tables, as well as adding an additional boolean field to my first (this will help direct things as to which table to enter the data to.)

    customer (customerID, customername, boolean(company y/n?)
    company (companyname)
    person (last,first,mi)

    later on down the line, when I get to the point where the customer specifies a contact (either a company has a contact name, or a person names another as secondary contact) I can create an association table that links the two.

    associate (customerID, person)

    That sound about right?

    ~W

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by tildewoody View Post
    Seeing how I have basically two types of customers; companies and people that suggests two more tables,
    You are missing the point. What I termed "Entity" is what the article refers to as "Party".
    Your logical data model will separate businesses and individuals, but your physical data model will combine them into a single table.
    I avoid 1-1 subtype tables whenever possible, in favor of more generic fields and nullable columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2010
    Posts
    7
    You are missing the point.
    Hmmm... I thought I got it. I guess I'm mistaken. Back to the white board.

    I avoid 1-1 subtype tables whenever possible, in favor of more generic fields and nullable columns.
    I'm not quite sure I follow. Are you referring to my "associates" table? Or just the simplicity of the tables? ex. Company(CompanyID, CompanyName)

  9. #9
    Join Date
    May 2008
    Posts
    277
    What you're attempting to model here is something known as a supertype/subtype relationship. Here's an article about it you might find useful:
    Implementing Table Inheritance in SQL Server - SQLTeam.com

    So you'd end up with tables like this:
    party(party_id*, party_type)
    person(party_id*, party_type, first_name, last_name)
    company(party_id*, party_type, company_name)

    What blindman is talking about is collapsing this into one table:
    party(party_id*, party_type, first_name, last_name, company_name)

    As I see it, there are benefits and drawbacks either way, so it's up to you which method to use.

  10. #10
    Join Date
    Jan 2010
    Posts
    7
    Thanks Futurity and Blindman... Looking at my notes and whiteboard... I may be off the path... but only by a step or two...

    I'm going to keep plodding along and play around with this...

    Expect me back...

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by futurity View Post
    What you're attempting to model here is something known as a supertype/subtype relationship.
    ...which is the type of 1-1 relationship I specifically try to avoid.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2010
    Posts
    7
    Quote Originally Posted by futurity View Post
    What you're attempting to model here is something known as a supertype/subtype relationship.
    Quote Originally Posted by blindman View Post
    ...which is the type of 1-1 relationship I specifically try to avoid.
    Well, I can see I'm a ways off, and that there are different schools of thought. I'm chuckling to myself how diametrically opposed those two statements are if read on their own. (ie. If I'm attempting to drive a car to the store, I wouldn't avoid actually driving the car.)

    Off to study...

  13. #13
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by blindman View Post
    ...which is the type of 1-1 relationship I specifically try to avoid.
    Conceptually the concept is the same, regardless of how one physically implements it in the database, no?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by tildewoody View Post
    Well, I can see I'm a ways off, and that there are different schools of thought. I'm chuckling to myself how diametrically opposed those two statements are if read on their own. (ie. If I'm attempting to drive a car to the store, I wouldn't avoid actually driving the car.)

    Off to study...
    That depends. Is your objective to drive the car, or is your objective to get to the store?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •