Three Way (four?) Association Table

[AccessJunkie33]

Is the three way association table necessary?

Hi guys.

Attached is the basic database for reference.

Here is an interesting problem that I've run into at my internship.

Currently, I'm working on a database for a compliance audit on a bunch of construction companies. Since they're going to do city work, they must contract out 30% of construction revenue to sub-contractors, creating a relationship between the prime and subs.

Here are the tables:

Packages (lists of contracts given to 'Prime' contractors)
Contractors
Prime
Sub
Package-Contractor Association Table

Everything would've been fine and dandy if a Prime could only be a Prime and a Sub only a Sub, but that's not how it goes. : |

If a business is considered a small business, they can be a Prime or Sub-contractor at the same time. This is what's kicking my ass.

My question is, since the prime and sub ids won't make sense as a foreign key in the contractor table due to a duplication of data, how should I link them through the association table so that the database is normalized, without violating REA?

Thank you for your time.

 

[AccessJunkie33]

Linking through a Duality

This is just a rehash of the "Three-Way Association Table" in a clearer way but I'm curious about why linking through a duality is so abhorred?

My professor claims weird stuff can happen and that IT'S STINKING EVIL, but nonetheless, he recommended it to me for my particular compliance audit database. : |

Is there a duplication of data? Does the loop in the three-way duality screw up queries? Is it not normalized?

 

[gemma-the-husky]

what is the significance of the prime and sub tables?

just use this table

Package-Contractor Association Table

and add another (y/n) field for "prime"

then you can easily find for each subcontractor the count of contracts on which they are defined as prine, and on which they are subbies.

 

[AccessJunkie33]

The significance of the prime and the sub table is so that the prime and sub id appear as a foreign key in the contractor table. From what you've just said, it now seems unnecessary.

What do you mean by (y/n) field? Will I have to just manually assign y/n to each field or is there also a drop-down box for y/n? Another issue is that I don't want to assign hundreds of contractors a y/n in the association table one by one. Is it possible to query it out and place it in the package-contractor table?

I'm also still curious about what is so bad about linking through a duality?

My idea of why it's bad:

Duplication of data due to loops
Weird things can happen?
Not normalized?

Thanks for replying!

 

[Lightwave]

If I'm reading you correctly sub table is probably an inappropriate name for it as it is really a joint table that is being used simply to associate records in the same companies table.

As such the Joint association table simply has

PKID
ContractName
PrimeContractor (FKID)
SubContractor (FKID)

The two fields PrimeContractor and SubContractor are referenced to the same field in the Contractor name table such that in one contract Company A will be the prime contractor to company B while in another contract it may reverse. This can be continued for as many contracts that exist. You are able to have infinite contracts and the ways in which contractors are related can vary as much as you want althouther there is only ever a finite number of ways a number of companies can relate although those relationships can duplicate infiinitly.

Junction tables are very standard in database design.

A simple query on the junction table will give you how many times a company is prime or sub and to whom and how these relationships exist.

The yes no option by Dave is an alternative way of doing same thing rather than having a field with prime and a field with sub you have a tick for which one is prime / sub. Would allow the ability to swap prime and sub very easily although is extra fields compared to my set up.

 

[AccessJunkie33]

Thank you for replying! It's 7:18 AM where I am. Nothing like database design and breakfast at the same time. That said, I shall grace you with a thorough reply after I get done with my midterm today.

 

[The_Doc_Man]

Make one table for all possible contractors. Where a contractor COULD go both ways... (hmmmm, kinky!), have a flag or two that say "Can be Prime" and "Can be Sub."

Make one table of two columns ([PrimeCon] and [SubCon] plus relevant baggage, like package number) that is one-to-many where both the left and right columns of the refer to the contractor table. Each entry in this self-junction table represents a relationship based on the prime always being the same and the subs differing.

You constrain it so that (1) the prime and sub FKs cannot be the same (2) the contractor to be stored in the prime FK must be flagged as "can be prime" (3) the contractor for the sub FK is flagged as "can be sub."

There is no contractor duality. Stated another way, the duality is a role of the circumstances of the package award, not always an attribute of the contractor.

 

[gemma-the-husky]

Make one table for all possible contractors. Where a contractor COULD go both ways... (hmmmm, kinky!), have a flag or two that say "Can be Prime" and "Can be Sub."

Make one table of two columns ([PrimeCon] and [SubCon] plus relevant baggage, like package number) that is one-to-many where both the left and right columns of the refer to the contractor table. Each entry in this self-junction table represents a relationship based on the prime always being the same and the subs differing.

You constrain it so that (1) the prime and sub FKs cannot be the same (2) the contractor to be stored in the prime FK must be flagged as "can be prime" (3) the contractor for the sub FK is flagged as "can be sub."

There is no contractor duality. Stated another way, the duality is a role of the circumstances of the package award, not always an attribute of the contractor.

The last statement is the key to this. It's not the contractor itself that is defined as the main contractor. It's the way the job was allocated.

So eg, Costain can be the main contractor on their own jobs, but a sub-contractor on a DOE job. The prime and main are attributes of the job table, or assocaiation table. As you such, you just dont need the tables for Sub and Prime you initially used.