Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    53

    Question Unanswered: supertype/subtype advice

    Hello,

    I am seeking some advice on a model I am working on concerning supertypes/subtypes.

    Normally I think this would be pretty straight forward but this one is kind of giving me fits.

    To elaborate... Currently I have three tables; COMPANY, DISTRIBUTOR, MANUFACTURER.

    COMPANY { COMPANY_ID, COMPANY_TYPE_ID, etc... }
    DISTRIBUTOR { COMPANY_ID, DISTRIBUTOR_CODE, etc... }
    MANUFACTURER { COMPANY_ID, MANUFACTURER_CODE, etc... }

    Each distributor and manufacturer may/may not have multiple locations, so a code was implemented in the subtype tables to represent which location it would be. This and the company_id make up the primary key of the subtype tables, as it guarantees the uniqueness. Now the part that is tripping me up a little is that each distributor location could distribute their parts to any of the Manufacturers' locations.

    Example - Distributor ACME has 3 locations and Manufacturer CBA has 5 locations. ACME's 1st location ships their parts to all 5 CBA locations, ACME's 2nd location ships their parts to only 4 of CBA's locations and ACME's 3rd location ships their parts to only 1 of CBA's locations.

    SHIPPING_LOCATION { DISTRIBUTOR_CODE, MANUFACTURER_CODE }

    To represent this I thought of just making an Association table, as posted above.

    Next, each company will have a headquarters address and each location could have multiple addresses, their permanent address (where the plant is actually located, then a billing address, and possibly a managing company address (welcome to the wonderful world of mergers and buyouts and obscure scenarios)) therefore, I created 2 more association tables which looks like the following.

    ADDRESS { ADDRESS_ID, ADDRESS_TYPE_ID, etc... }
    DISTRUBTOR_LOCATIONS { COMPANY_ID, DISTRIBUTOR_CODE, ADDRESS_ID }
    MANUFACTURER_LOCATIONS { COMPANY_ID, MANUFACTURER_CODE, ADDRESS_ID }

    Finally, this database is going to manage all the contacts that a Distributor and a Manufacturer will need for each location; however, in some cases you will have a person who oversees many facilities, such as a President, VP, or Regional Manager, so in this case I chose this model.

    PERSON { PERSON_ID, etc... }
    DISTRIBUTOR_CONTACT { PERSON_ID, COMPANY_ID, DISTRIBUTOR_CODE }
    MANUFACTURER_CONTACT { PERSON_ID, COMPANY_ID, MANUFACTURER_CODE }

    I would appreciate any feedback that could be offered, pros or cons, or a better alternative. Of course this is only a small piece to this model but it is one that I have mulled over for quite some time. Mostly, I think a fresh pair of eyes could offer some valuable insight to something I might not have considered.

    Many thanks for all your assistance.
    KDK

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've had the best success in the past using a more free-form, object oriented model.

    Instead of having separate tables for Companies, Distributors, and Manufacturers, use a single table that represents Businesses. Then create one subtable to establish many-to-many joins between businesses that are companies and businesses that are distributors. Create another table for many-to-many manufacturer relationships.

    Each "Business" record actually represents a business division, and a self join with a ParentID column establishes the business hierarchy for each enterprise.

    Add one more table for BusinessContacts, and you have a flexible schema with just five tables.

    The disadvantage of a schema like this is that it can be confusing to keep track of in your head. Many people recoil from recursive relationships and many-to-many self joins with an attitude similiar to math anxiety. If you write a few stored procedures and user functions for returning recordsets like "BusinessSubdivisions" and "AssociatedManufacturers", it becomes much more manageable.

    The advantage of this type of schema is that it is very flexible and easily deals with situational exceptions like "well, this distributor is also a manufacturer, and this company subcontracts as a distributor for this other company", etc. The resulting code is usually shorter and more concise as well.

    blindman

  3. #3
    Join Date
    Sep 2002
    Posts
    53
    I understand what you mean about the parent/child, however, you kind of lost me in the subtable example. Would you mind showing how the table would link, please. It would definitely help me clarify what you are referring too. Many thanks for your assistance on this blindman.



    Originally posted by blindman
    I've had the best success in the past using a more free-form, object oriented model.

    Instead of having separate tables for Companies, Distributors, and Manufacturers, use a single table that represents Businesses. Then create one subtable to establish many-to-many joins between businesses that are companies and businesses that are distributors. Create another table for many-to-many manufacturer relationships.

    Each "Business" record actually represents a business division, and a self join with a ParentID column establishes the business hierarchy for each enterprise.

    Add one more table for BusinessContacts, and you have a flexible schema with just five tables.

    The disadvantage of a schema like this is that it can be confusing to keep track of in your head. Many people recoil from recursive relationships and many-to-many self joins with an attitude similiar to math anxiety. If you write a few stored procedures and user functions for returning recordsets like "BusinessSubdivisions" and "AssociatedManufacturers", it becomes much more manageable.

    The advantage of this type of schema is that it is very flexible and easily deals with situational exceptions like "well, this distributor is also a manufacturer, and this company subcontracts as a distributor for this other company", etc. The resulting code is usually shorter and more concise as well.

    blindman
    KDK

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The subtables for distributor relationships and manufacturer relationships each have two fields, both of which are linked back to the Businesses table.

    For example:

    Table Manufacturers
    (BusinessID, ManufacturerID)

    Both of these fields have foreign key relationships to the ID field of the Businesses table.

    blindman

  5. #5
    Join Date
    Sep 2002
    Posts
    53
    I mocked up a very simplified version in Access, and I am still a little confused, so once again I will ask if you will elaborate. When making a model as you described below, by linking a table in a many-to-many relationship such as this, it allows me to link the distributor directly to the manufacturer. Therefore, I am not sure why I would need both a Manufacturer and a Distributor table. Also, I am once again confused as to the dual foreign key in a table like the manufacturer table. In the business table the hierarchy is set by referencing its parent, thus showing that the company is managed/owned/operated by its parent. Obviously I am missing a piece of the puzzle, but what part is it that I am not getting? Thanks once again for your assistance!


    Originally posted by blindman
    The subtables for distributor relationships and manufacturer relationships each have two fields, both of which are linked back to the Businesses table.

    For example:

    Table Manufacturers
    (BusinessID, ManufacturerID)

    Both of these fields have foreign key relationships to the ID field of the Businesses table.

    blindman
    KDK

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do manufacturers and distributors have inverse relationships to eachother? IE., if company A is a manufacturer for company B, does that mean that company B is a distributor for company A? If so, then you only need one subtable for the many-to-many relationships.

    Let's call that table DISTRIBUTORS. You could then set up two tables like this:

    COMPANIES
    (CompanyID,
    CompanyName,
    ParentCompanyID)

    DISTRIBUTORS
    (ManufacturerCompanyID,
    DistributorCompanyID)

    DISTRIBUTORS establishes many-to-many relationships between companies that are manufacturers and companies that are distributors. In DISTRIBUTORS, the ManufacturerCompanyID/DistributorCompanyID represent a unique key, and both fields have foreign keys to the COMPANIES table.

    The many-to-many relationship table is pretty common, though it is more frequently used to establish relationships between two different tables. In this case, since companies that are distributors will share many fields in common with companies that are manufacturers, (address, contact person, phone number...) it makes sense to keep them both in the same table. You can, if you want, add a field to the COMPANIES table that indicates whether the company is a distributor or a manufacturer.

    This schema allows a manufacturer to have multiple distributors, and a distributor to have multiple manufacturers. If, in your business model, distributors are dedicated to single manufacturers, then the many-to-many relationship is not necessary.

    I'll say again that this schema, while not uncommon, can be difficult for new DBAs to visualize. You can go with a more simplistic design using multiple tables for different types of companies, and even multiple tables for different business levels, but ultimately it will not be as robust and will require greater administration. You have to decide which is right for your situation.

    blindman

  7. #7
    Join Date
    Sep 2002
    Posts
    53
    Thank you very much for your explanation. I was with you all the way until I got to the 2 tables DISTRIBUTORS and MANUFACTURERS. Due to the many-to-many that they both had I was unable to determine how I would of used it. With that being said, the one table, I already had which linked Distributors with Manufacturers.

    I will be clarifying this with the business-users but in essence they needed to track distributors and who they were shipping too. Which your model below completes the circular reference.

    Once again I appreciate your assistance!

    Brad

    Originally posted by blindman
    Do manufacturers and distributors have inverse relationships to eachother? IE., if company A is a manufacturer for company B, does that mean that company B is a distributor for company A? If so, then you only need one subtable for the many-to-many relationships.

    Let's call that table DISTRIBUTORS. You could then set up two tables like this:

    COMPANIES
    (CompanyID,
    CompanyName,
    ParentCompanyID)

    DISTRIBUTORS
    (ManufacturerCompanyID,
    DistributorCompanyID)

    DISTRIBUTORS establishes many-to-many relationships between companies that are manufacturers and companies that are distributors. In DISTRIBUTORS, the ManufacturerCompanyID/DistributorCompanyID represent a unique key, and both fields have foreign keys to the COMPANIES table.

    The many-to-many relationship table is pretty common, though it is more frequently used to establish relationships between two different tables. In this case, since companies that are distributors will share many fields in common with companies that are manufacturers, (address, contact person, phone number...) it makes sense to keep them both in the same table. You can, if you want, add a field to the COMPANIES table that indicates whether the company is a distributor or a manufacturer.

    This schema allows a manufacturer to have multiple distributors, and a distributor to have multiple manufacturers. If, in your business model, distributors are dedicated to single manufacturers, then the many-to-many relationship is not necessary.

    I'll say again that this schema, while not uncommon, can be difficult for new DBAs to visualize. You can go with a more simplistic design using multiple tables for different types of companies, and even multiple tables for different business levels, but ultimately it will not be as robust and will require greater administration. You have to decide which is right for your situation.

    blindman
    KDK

Posting Permissions

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