Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Unanswered: DB Design Question

    I have to build a DB using SQL Server 2000 that lets me have many-to-many relationships between Company, Contact, and Address tables. That is, each 'entity' (Company, Contact or Address) can have many of the other two entities related to it.

    I've never built anything like this before. I've come up with two possible scenarios to do this. Can someone give me some input on the best way to achieve this. Here are the two scenarios I'm looking at:

    Scenario 1: Use one Junction table to create the many-to-many relationship.

    Company
    -------
    CompanyId(PK)
    CompanyName

    Contact
    -------
    ContactId(PK)
    ContactName

    Address
    -------
    AddressId(PK)
    Address

    JunctionTable (PK made up of all 3 FKs)
    -------------
    CompanyId(FK)
    ContactId(FK)
    AddressId(FK)

    Or
    Scenario 2: Use different junction tables to create M:M relationships between each pair of tables.


    Company
    -------
    CompanyId(PK)
    CompanyName

    Contact
    -------
    ContactId(FK)
    ContactName

    Address
    -------
    AddressId(PK)
    Address

    JuncCompCont
    ------------
    CompanyId(FK)
    ContactId(FK)

    JuncContAddr
    ------------
    ContactId(FK)
    AddressId(FK)

    JuncCompAddr
    ------------
    CompanyId(FK)
    AddressId(FK)


    Since I've never built this, I can't say for sure both will work the same way. The DB has to be normalized too. I also have to consider how difficult it will be to create different views as well as the forms used to enter the data.

    Any input would be greatly appreciated.


    Thanks.
    Anthony

  2. #2
    Join Date
    Nov 2002
    Posts
    23
    My vote's for #1.
    Reason: That's how I've always done it. The other way seems too redundant for me.

    The only times I've come across dbs like the 2nd would be in places where there is HUGE amounts of data (3, 4 million records of companies, 10-12 mill contacts, etc). Typically, even though I didn't like it, it was told to me that this way (#2) has a much faster response. That could be the only benefit I know of. But as you can see by my posts, I'm not the most knowledgable on this subject. :-)

    I'd love to hear the other response from people more qualified.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have to ask yourself if it's always a three-way relationship, or whether two-way relationships are possible

    in other words, if mary is a contact for Company A, does she have to have an address? if mary has addresses, does each address have to be associated with a company? can a company have an address without a contact?

    with all the possible variations, you can see pretty quickly that you will have to allow for nulls in all of the FKs in this table --

    JunctionTable (PK made up of all 3 FKs)
    -------------
    CompanyId(FK)
    ContactId(FK)
    AddressId(FK)

    therefore, you cannot use the 3-column composite as a PK because it'll have nulls, and therefore, you have to use a surrogate key, and then in addition, declare separate unique indexes for all combinations of two keys (you wouldn't want the same contact at the same company more than once, for example)

    with all of the above, does #1 still sound attractive?

    didn't think so


    rudy

  4. #4
    Join Date
    Nov 2002
    Posts
    23
    Completely agreed.
    Sorry, I was assuming you wouldn't have a contact without a company or address.

  5. #5
    Join Date
    Oct 2002
    Posts
    9

    Re: DB Design Question

    I have to disagree with Criki on this one. I would definitely go with #2. Yes, it saves some space but I would guess that it would be slower. You should make combo Primary Keys on the junction tables that use all of the IDs present. So for one of your Junction tables in #2 the script would be:
    CREATE TABLE JuncCompAddr (
    CompanyId int NOT NULL,
    AddressId int NOT NULL,
    PRIMARY KEY(CompanyId, AddressId)
    )
    GO
    One of the two column junction tables in #2 will have much less data that the massive three column junction table in #1. This means your select statements will have less real estate to cover to return your records. Also, the primary key index on the two column tables will hold more per page since each row will only be 8 bytes instead of 12 (4 bytes for an integer). The less pages it has to go through the faster the response time will be.

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Post Re: DB Design Question

    RE: I've never built anything like this before. I've come up with two possible scenarios to do this.

    Q1 Can someone give me some input on the best way to achieve this. A1 Yes. Concisely, the 'best way' is to:
    i Find out and understand what the business rules are before you start creating databases and tables. (When the BRs are ambigous or inconsistent, you need to bring that to the attention of the business users so they may resolve any issues so you may create correct logical designs).
    ii Create a logical design that reflects what the (logically consitent) business rules are.
    iii Review the implications with the users before populationg your designs. Test and validate with the business users (correct / redesign as necessary) prior to putting anything into production.

    Why is this the best way? Some form of this approach is the only professional way to go about things.
    ----------Example--------------
    Consider (the logically VERY DIFFERENT design implications of) various responses to the following example busines rule question:

    Business Rule Example Question 1:
    Mr. / Ms. Business owner / user what are the Company, Contact and Address relationships? Do you need to allow for having contact information without company information?, etc.? Please comment and elaborate so I may understand the relationship correctly and completly.

    Business Rule Example Response 1:
    No, unidentified contacts never happen in this industry! If we call a contact and do not know the Company info we will insult the contact and LOOSE that account immediatly; this is an important cultural consideration in this industry. Also, by law, we are only allowed to conduct business with one contact for any company in country ABC; if we break this law we will be fined for unfair business practices per law XYZ and also banned from conducting business there for one year.
    -----Vs.------
    Business Rule Example Response 2
    Yes, we frequently need to track contacts (free agents) who are between companies. Also, it is important to note that one contact sometimes represents many companies, and has different contact addresses that we must correctly use for each different company the contact represents.
    ----------Example--------------
    The example is contrived, but do you want to be responsible for potentially creating a database that does not meet business needs, or worse causes business problems?

  7. #7
    Join Date
    Oct 2002
    Posts
    6
    Here are some business rules...

    a company can have 0 or more addresses
    a company can have 0 or more contacts
    a company can have neither a contact or an address (thoeretically)

    a contact can have 0 or more addresses
    a contact can correspond to 0 or more companies
    a contact can have neither a company or an address

    an address can correspond to 0 or more companies
    an address can correspond to 0 or more contacts


    this means...

    two companies can share the same address
    a company can share an address with any number of contacts

    two contacts can work for the same company and each have home addresses. the home addresses would not correspond to the company.



    Based on this, will scenario 2 work? Is there a simpler way altogether that has not been mentioned?

  8. #8
    Join Date
    Oct 2002
    Posts
    369

    Arrow

    RE: Here are some business rules...
    a company can have 0 or more addresses
    a company can have 0 or more contacts
    a company can have neither a contact or an address (thoeretically)

    a contact can have 0 or more addresses
    a contact can correspond to 0 or more companies
    a contact can have neither a company or an address

    an address can correspond to 0 or more companies
    an address can correspond to 0 or more contacts
    *[Can an address have neither a company or a contact, or must it have at least one (a contact or company) to be inserted?]

    this means...
    two companies can share the same address
    a company can share an address with any number of contacts
    two contacts can work for the same company and each have home addresses. the home addresses would not correspond to the company.


    Q1 Based on this, will scenario 2 work?
    A1 Based on this, (the logical design) scenario 1 is not appropriate.
    The logical design of scenario 2 may be appropriate; however, the design may well require additional constratint(s). (For example: In the * case above for example, if an address must have at least one related contact, or one related company in order to be inserted; the logical design of scenario 2 would call for additonal constraint(s) to address that aspect of the BRs.

    Q2 Is there a simpler way altogether that has not been mentioned?
    A2 Well maybe, but 'simpler' in what way, and for whom exactly? Clearly, any 'simpler way' that ignores or lacks a logical design that accurately reflects what the (logically consitent) business rules are, will have the potential to create problems of one sort or another (either in terms of usability for the business, or in terms of incorrect and / or inconsistent or corrupt data within the database).

  9. #9
    Join Date
    Oct 2002
    Posts
    6
    I suppose an address would have to belong to either a company or contact (or what's the point of having it?) I've altered scenario 2. I think this is it.


    Company
    -----------
    CompanyId (PK)
    CompanyName

    Contact
    ---------
    ContactId (PK)
    ContactName

    JuncCompCont (used to link Companies to Contacts)
    ------------------
    CompanyId (PK)1 (excuse the incorrect way to write this)
    ContactId (PK)2


    Address
    ---------
    AddressId (PK)
    Address


    Junction (used to link addresses to either companies or contacts)
    ---------
    AddressId (PK)
    CompanyId (PK)1
    ContactId (PK)2


    This way, JuncCompCont creats a M:M relationship between companies and contacts. Junction is used to link each row(s) to the correct address(es).


    How's this?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how's this? it's getting worse :-)

    can you have a contact that is not associated with a company?

    sounds reasonable to me

    but if so, then with your latest design, the contact cannot have an address

    <sigh />

    the best solution still looks like three different association tables

    note to DBA: if you're going to quote extensively, please use the appropriate Quote tags (under the COLOR dropdown list), because it's very hard to see where your actual comments begin; besides, previous posts in the thread are always visible, so extensive quoting should not be necessary

    rudy

  11. #11
    Join Date
    Oct 2002
    Posts
    369

    Post

    Originally posted by LaStralla
    I suppose an address would have to belong to either a company or contact (or what's the point of having it?) I've altered scenario 2. I think this is it. ... How's this?
    Q1 How's this?

    A1 Actually, it appears to be appropriate for a rather different set of BRs (than what I've seen here so far).

    The logic for an appropriate Scenario2 Address constraint specified by the BR: (An address must have at least one related contact, or one related company in order to be inserted) is more like, for example:

    An additional constraint on the addresses table:
    -------
    Create an Address table After Trigger. The pseudo-code to run after inserting data into the Address Table being something like:
    IF (
    There Exists a CompanyAddress record Where CompanyAddress.AddressID = Address.AddressID
    Or
    There Exists a ContactAddress record Where ContactAddress.AddressID = Address.AddressID
    )
    Then -- {in this case, allow the insertion to take place with no rollback}
    Else
    Rollback -- in this case there is neither a related Company or Contact therefore remove and disallow entry of the new address data into the address table!
    -------

    One way this sort of situation is handled is to wrap (an address insertion) into an app_AddNewAddressAndPopulateAppropriateOwningEntit yIDs stored procedure that takes either a corresponding CompanyID and/or a corresponding ContactID (that the new address record is related to), as parameters.

    The app_AddNewAddressAndPopulateAppropriateOwningEntit yIDs stored procedure first inserts a new address row, and then the appropriate row(s) into either the CompanyAddress and / or the ContactAddress tables (as appropriate) that correspond to the new Address data. Note: often it is more efficient to check to make sure the CompanyID and / or ContactID parameters are valid and exist, within the stored procedure itself, rather than to rely on the Address constraint e.g.(the after trigger) on the Address table, to rollback any errors.

    Schema Reference:

    Address
    AddressID
    Address

    CompanyAddress
    CompanyID
    AddressID

    ContactAddress
    ContactID
    AddressID

  12. #12
    Join Date
    Oct 2002
    Posts
    6
    I just came to the same conclusion!!! I built a test model in Access and sure enough the contacts can't have an address unless they have a company. Unfortunately I need the reverse to be true also. So back to the drawing board.

    I'm going to try building scenario 2 as I originally thought it up and see if I can get that working.

  13. #13
    Join Date
    Oct 2002
    Posts
    369

    Question

    RE: note to DBA: if you're going to quote extensively, please use the appropriate Quote tags (under the COLOR dropdown list), because it's very hard to see where your actual comments begin; besides, previous posts in the thread are always visible, so extensive quoting should not be necessary.

    Q1 [Please use Quote tags; it's very hard to see where your actual comments begin.]
    A1 Sorry; when responding I often manually have to copy and paste, (and put in tags), and do not have a COLOR dropdown list available.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you hit the Reply button, you should see the following, starting from the top of the form --

    Logged in user: DBA [logout]
    Post subject: (Optional)
    Message Icon:
    No icon
    vB Code [help]
    Normal Mode
    Enhanced Mode SIZEsmall large huge FONTArial Times Courier Century COLOR sky blue royal blue blue dark-blue orange orange-red crimson red firebrick dark red green limegreen sea-green deeppink tomato coral purple indigo burlywood sandy brown sienna chocolate teal silver


    the vB Code line is followed by Your Reply: with the textarea you type into

    do you really not have a COLOR dropdown?

  15. #15
    Join Date
    Oct 2002
    Posts
    369

    Question

    RE: do you really not have a COLOR dropdown?

    Q1 do you really not have a COLOR dropdown?
    A1 If I do, I certainly don't see it.

    S1 Here is what I DO have: (when I hit the Reply button, starting from the top of the form)


    Logged in user: DBA [logout]
    Post subject: [ ] (Optional)
    Message Icon: [ icons with radio buttons ]
    * No icon

    Your Reply: [ ]
    See Forum Rules (below) for
    more information about what
    codes you are allowed to use
    in your posts.

    Options:
    [x] Automatically parse URLs: automatically adds and around internet addresses.
    [x] Email Notification: emails sent to you whenever someone replies. Only registered users are eligible.
    [_] Disable Smilies in This Post
    [_] Show Signature: include your profile signature. Only registered users may have signatures.
    Attach file: [ ]
    Maximum size: 10485760 bytes
    Valid file extensions: gif jpg png txt zip bmp jpeg gz tgz rar par pdf doc sql pc c pm pls pl rar rex rexx

    [Submit Reply] [Preview Reply]

    Forum Rules:
    You may post new threads
    You may post replies
    You may post attachments
    You may edit your posts HTML code is OFF
    vB code is ON
    Smilies are ON
    [IMG] code is OFF


Posting Permissions

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