If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Normalization Help
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. |
#2
|
|||
|
|||
On Fri, 7 Jan 2005 13:45:02 -0800, r. howell
wrote: 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] |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. "Graeme Richardson" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Duplicate data | Rob Green | Database Design | 3 | November 7th, 2004 03:08 AM |
Access & OleDb - generating schema changes, problem with identity/counter fields. | Thomas Tomiczek [MVP] | Database Design | 9 | November 5th, 2004 10:32 AM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |