Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2008
    Posts
    58

    Normalization Quandary

    Hello,

    I'm designing a new Access database from scratch and am currently in the field/table/relationship design phase of that project. Ideally, I'd like to keep my structure as normalized as possible since the front end for this application will involve some serious coding, calculations and queries. However, I ultimately have to do what I have to do for my application to work properly, regardless of what the normalization rules say.

    Our current system contains information on three primary levels. Each customer is called an Owner. Each Owner has one or more facilities (Sites) at which my company performs Projects. The model essentially is:

    Owners (PK: OwnerID)
    Sites (PK: OwnerID + SiteID)
    Projects (PK: OwnerID + SiteID + ProjectID)

    We also have 3 types of contacts for which to store information. These are customer contacts, third party sales reps and third party contractors. Different contacts are required for Owners, Sites and Projects as follows:

    Owners: corporate customer contact, corporate account rep (sometimes)
    Sites: customer contact, account rep, 3P contractor
    Projects: customer contact

    What makes this especially difficult is that account reps are often assigned to sites without regard for the owner, i.e. it is not a top-down approach. Now, normalization says that all contacts should be in one table, perhaps with an additional field dedicated to "Contact Type". What I can't figure out is how to relate that contacts table to the three separate Owners, Sites and Projects tables. Do I put multiple fields in each that link to the Contacts table, do I pull multiple fields from these tables into the Contacts table,...? It seems like a faux many-to-many relationship using a union table would be the solution here, but since I can't seem to pin it down, I'll submit it here for the experts to see.

    Thank you in advance for your input.
    Last edited by JManSF; 09-08-08 at 13:56.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dreaded? whoa!!

    i would have one table only for the contacts

    then each of the other entities, in order to link to one or more contacts, has one or more dreaded foreign keys

    so in the Owners table, there would be two foreign keys, one called corporate_customer_contact, and the other called corporate_account_rep
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    A separate table for each type of contact would have some advantages. You could create foreign keys to ensure only the right type of contact was used for Owners, Sites and Projects.

    A Primary Key is certainly not an index. Most DBMSs will create an index to support a PRIMARY KEY constraint but a key and an index are quite different things which you should not confuse.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    One table for contacts, please.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by r937
    dreaded? whoa!!

    i would have one table only for the contacts

    then each of the other entities, in order to link to one or more contacts, has one or more dreaded foreign keys

    so in the Owners table, there would be two foreign keys, one called corporate_customer_contact, and the other called corporate_account_rep
    So if I created an arbitrary unique identifier for the Contacts table and called it ContactID, the Owners table (for example) would have two or three separate fields that all reference a ContactID? That was my first idea, but isn't it in violation of 1NF? I suppose that wouldn't be the end of the world, but it seems like there should be a better way.

    What's adding confusion here is that different types of contacts will relate to the Owners/Sites/Projects tables in different ways. For example, customer contacts will be one-to-one with the Owners table, but quite possibly one-to-many with tblSites and tblProjects. Account Reps and Contractors will (almost) always be one-to-many with all three tables.
    Last edited by JManSF; 09-08-08 at 13:55.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    For 1-1 relationships you will have the ContactID as a foreign key in your table.
    For 1-N relationships you will establish tables for mana-to-many joins, containing the foreign keys of both the Contacts table and the related table.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by blindman
    For 1-1 relationships you will have the ContactID as a foreign key in your table.
    For 1-N relationships you will establish tables for mana-to-many joins, containing the foreign keys of both the Contacts table and the related table.
    This is an "Aha!" moment. Many thanks, blindman. So I'll have a union table with:

    OwnerID
    SiteID
    ProjectID
    ContactTypeID
    ContactID

    Where ContactTypeID indicates if the contact is an account rep, corporate customer contact, contractor, etc. Excellent!

    J

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Not quite excellent yet.

    Your use of cascading IDs for your primary keys is really annoying and may cause problems later.

    Is SiteID a surrogate key? Can two sites ever have the same SiteID? If not, then SiteID alone should be the primary key. You don't need to include the OwnerID as part of the key.

    Is ProjectID a surrogate key? Can two projects ever have the same ProjectID? If not, then ProjectID alone should be the primary key. You don't need to include the SiteID as part of the key, and you don't need to include OwnerID at all if SiteID is unique within the Site table.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by blindman
    Not quite excellent yet.

    Your use of cascading IDs for your primary keys is really annoying and may cause problems later.

    Is SiteID a surrogate key? Can two sites ever have the same SiteID? If not, then SiteID alone should be the primary key. You don't need to include the OwnerID as part of the key.

    Is ProjectID a surrogate key? Can two projects ever have the same ProjectID? If not, then ProjectID alone should be the primary key. You don't need to include the SiteID as part of the key, and you don't need to include OwnerID at all if SiteID is unique within the Site table.
    It is annoying. Unfortunately, I have to incorporate our naming convention into this DB design. The naming convention is a concatenation of [OwnerID]-[SiteID][ProjID]. SiteID and ProjID are not unique values. Each Owner has site 1, site 2, site 3, etc. Each Site has project A, project B, project C, etc. For example, Project A at Site 1 for Owner 1001 is "1001-01A". That's why I have the primary keys set up as compositions in this manner. Since concatenation is a form of calculation, I figured it would be best to keep the elements as separate fields and bring them together as needed.

    Edit: I could, if needed, just create an autonumber for Sites and Projects to give them unique IDs, while keeping the naming convention info in the tables for display purposes. That just seems like a waste, though, if I can make it work with composite keys.
    Last edited by JManSF; 09-08-08 at 15:48.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by JManSF
    That just seems like a waste, though, if I can make it work with composite keys.
    R937 would agree with you. I would disagree. Cascading composite keys leads to a big mess in any moderately complex database design.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by blindman
    R937 would agree with you. I would disagree. Cascading composite keys leads to a big mess in any moderately complex database design.
    It's the great surrogate key v. natural key debate. I'm going to keep my distance from that one .

    I'll try this method for now. If it becomes unworkable, I can always go in and add surrogates later. Thanks for your input, blindman.

    J

  12. #12
    Join Date
    Aug 2008
    Posts
    58
    Hi again, just wanted to run my solution by everybody here. I decided to keep all Contacts in one table, since they are one entity. However, instead of one table that relates Contacts to Projects AND Sites AND Owners, I've created three separate tables. Since I'm using composite (and as blindman pointed out, cascading) primary keys, I can't have just one table that relates Contacts to all 3 entities (Projects, Sites, Owners). Here's my logic:

    A single union table would have to contain the foreign keys for all three object tables, as well as a foreign key for the contacts table. Consider the foreign key for Sites, which is ([OwnerID],[SiteNumber]). A Site-related contact is functionally dependent on the Site, and therefore on both fields in the foreign key. An owner-related contact, however, is only functionally dependent on a subset of that key (OwnerID). Not only that, but an owner-related contact would have a Null in the SiteNumber field. If the field can be Null, it can't be a candidate key in the table. So instead I'll have three tables--tblOwnerContacts, tblSiteContacts and tblProjectContacts--where each table only contains data that is functionally dependent on the entire key and not on any subset of it.

    OwnerContacts
    ContactID
    OwnerID

    SiteContacts
    ContactID
    OwnerID
    SiteNumber

    ProjectContacts
    ContactID
    OwnerID
    SiteNumber
    Project

    Does this make sense? Are there any potential pitfalls with this strategy? Thanks,

    J

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by JManSF
    I decided to keep all Contacts in one table, since they are one entity. However, instead of one table that relates Contacts to Projects AND Sites AND Owners, I've created three separate tables.
    That is the generally preferred design.
    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
  •