Relational Theory Question

[Bill George]

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate normalization
principles to define a table to represent a teaming relationship between a
Prime and various Subs like this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the contractor
table?

TIA.

[Salad]

Bill George wrote:

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate normalization
principles to define a table to represent a teaming relationship between a
Prime and various Subs like this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the contractor
table?

TIA.

It does seem odd. Will Primes always be GC's and subs always subs? I would
think the definition of primary/sub contractors would be defined at the job.

I would think you'd want an additional table with an autonumber field, a prime
(gc) id and a subid field. Then store the primary in it (blank sub means its
the GC) and any sub records would have their id in the sub field and their GC
in the prime field....if you aren't doing this from the job.

GCs may work with some subs multiple times but I doubt the'd do so for every
job.

[Larry Linson]

Aside from the business rules questions raised by Salad, the kind of table
you describe is normal for a many to many relationship... but it does not,
of course, define the job for which that relationship applies.

Yes, a many-to-many can join records in the same table.

You'll probably want to include some validation that you don't have the same
contractor ID in both fields (unless you have a business rule that says a
contractor can subcontract to themselves). And, if it is correct that a
prime is always and only a prime, and a sub is always and only a sub, then
you'd want validation of that against the type, as well.

In programming/development contract work, the roles do often switch. In
construction, there is a more defined line between a "general" contractor
and a subcontractor... sometimes limited by the license that each has.

Larry Linson
Microsoft Access MVP

"Bill George" <Wo*******@frontiernet.net> wrote in message
news:BC3814A2.787B%Wo*******@frontiernet.net...

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate normalization
principles to define a table to represent a teaming relationship between a
Prime and various Subs like this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the contractor
table?

TIA.

[Nigel tombs]

I thought "Type" was a reserved word and may cause complications during the
build
"Bill George" <Wo*******@frontiernet.net> wrote in message
news:BC3814A2.787B%Wo*******@frontiernet.net...

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate normalization
principles to define a table to represent a teaming relationship between a
Prime and various Subs like this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the contractor
table?

TIA.

[Bob Quintal]

Bill George <Wo*******@frontiernet.net> wrote in
news:BC3814A2.787B%Wo*******@frontiernet.net:

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate
normalization principles to define a table to represent a
teaming relationship between a Prime and various Subs like
this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the
contractor table?

Not at all. This type of relationship is the basis for a pedigree
tree, or a Bill of Material tree.

One word of Caution: Your type field does not belong in the
contractors table. I would see definition as a field call
Prime_contractor in the contracts table. You may, and in my
business (Aerospace) see one contract where company A is the
prime and B, C and D are sub-contractors to A.Onanother contract
B is the prime and C,D and A are subs.

Even more confusing can be a major job, like the International
Space Station which has many layers of subcontracts, and some
even involve the prime contractor receiving a small sub contract
from a sub-sub contractor.

Good luck.

Bob Q

[Bill George]

Many thanks.

in article BC*********************@frontiernet.net, Bill George at
Wo*******@frontiernet.net wrote on 1/24/04 12:25 PM:

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate normalization
principles to define a table to represent a teaming relationship between a
Prime and various Subs like this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the contractor
table?

TIA.

[Pieter Linden]

In addition to all the advice you've gotten so far, read these two articles:

Bill of Materials:
http://www.mvps.org/access/modules/mdl0027.htm

Sub-classing Entities:
http://www.mvps.org/access/tables/tbl0013.htm

They may apply to your problem.

HTH,
Pieter

[Mike Sherrill]

On Sat, 24 Jan 2004 17:25:11 GMT, Bill George
<Wo*******@frontiernet.net> wrote:

If I have a table of contractors with this layout:

ID
Name
Address
Type

And type can be Prime or Subcontractor, does it violate normalization
principles to define a table to represent a teaming relationship between a
Prime and various Subs like this:

Prime-ID
Sub-ID

Where Prime-ID is a foreign key to the primary key, ID of the contractor
table?

No, that just shows there's some kind of relationship between the two
contractors. But . . .

In the general case, the problem is that a given company can be a
prime contractor for some jobs, and a subcontractor for others. That
is, the terms /prime/ and /sub/ don't always identify different types
of contractors, but they do always describe a relationship between
contractors.

--
Mike Sherrill
Information Management Systems