Table Normalization Help

[Guest]

I am getting dizzy figuring this out.

I have a table with basic project information that is unique to each project.

Each project has many contracts.

Each contract has many alternate details (a different number and different
details for every contract, and for every project).

Each contract has many bids (each of which will contain bids for each
alternate detail).

If I have a contracts table, does it include all the contracts for each
project?

If I have a bid detail table, does it include all projects and all contracts?

Or am I doing something wrong because there is all this repeated information
(project number, contract number, etc on each table to allow it to hook up in
one to many fashion).

Thanks.

 

[John Vinson]

I am getting dizzy figuring this out.

I have a table with basic project information that is unique to each project.

Each project has many contracts.

Each contract has many alternate details (a different number and different
details for every contract, and for every project).

Each contract has many bids (each of which will contain bids for each
alternate detail).
Ok.

If I have a contracts table, does it include all the contracts for each
project?

It contains all the contracts FOR EVERY PROJECT in the database.
There's just one Contracts table.

If I have a bid detail table, does it include all projects and all contracts?

Yes. It sounds like you should have a Details table, and that the Bids
table should relate to it rather than to the Projects table; it sounds
like the Bids table should be related one-to-many to Contracts and
also one-to-many to Bids.

Or am I doing something wrong because there is all this repeated information
(project number, contract number, etc on each table to allow it to hook up in
one to many fashion).

If the only information you're including in the "child" tables is the
one or two fields needed to link to the "parent" - e.g. a
ProjectNumber in the Contracts table, a ContractNumber in the Bids
table - you're doing it exactly correctly. What you *don't* want to
inlclude is any fields from the parent table *other* than the linking
field.

John W. Vinson[MVP]

 

[Graeme Richardson]

Hi, start with the tables

Project, Contract, AlternateContractDetails, and BidDetails

read "fk" as foreign key to

Contract has fkProject
AlternateContractDetails has fkContract
BidDetails has fkContract

Now
A bid contains sub bids so we need a SubBidDetails table that contains
fkAlternateContractDetails and fkBidDetails

HTH, Graeme

 

[Guest]

Thanks to both of you. This confirms what I thought. Talking to folks who
do not understand databases was making it hard to think by the end of the day.