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.

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

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.

One table for contacts, please.

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.

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

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.

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

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

That is the generally preferred design.