General discussion

Locked

Access 2000 Key fields

By debbie ·
In my database I used a field called Job_number as the key field in multiple tables. Table Job_Name connects to Job_Spec..Job_Spec connects to Job_Contract... Job_Contract connects to Job_Phases. Is it ok that I use the same key field in all? Even though I set up the relationships this way is it then ok to have Job_Spec connect directly to Job_Phases, etc. What is the best way to not confuse user friendly Access?
Thanks
Debbie

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000 Key fields

by Shanghai Sam In reply to Access 2000 Key fields

Debbie

I am making some assumpt based on your tbl names. Ans to "Is it ok that I use the same key field in all?" is yes, but.

Usually the prim key to any tbl are those values that will make each rec unique. In cases this is a single field, in others it may take 2,3,+ to achieve.

It appears that the DB is for construction, consulting, etc. Given that a mini-design in such a situation one might have, Customers, Contracts, payment, billing, etc. A customer might have multiple contracts, the contract may have sub-contracts (phases?). A database usually mirrors (with benefits & penalties) a paper system. For above:
Customer: CustID, Name, Address, Phone, Contact, CR_Limit, etc. This tbl would have data that is of a single occurrence and specific to the customer.

Contract: ContId, Amount, Beg_Date, End_Date, Name, etc. This tbl would have data that ... the contract.

In above CustID and ContID are primary keys, assinged, sequential, soundex, etc.

For a given cust,specific cont, the contract requires 3 phases, each with multipe specs, etc.

A ContPhase table would have PhaseId (same/similir to *ID above), ContId, CustId, PhaseName, Phase_BegDate, etc.
The primary key could be PhaseID (unique number within database, or PhaseId + ContID+CustId. There are valid design reasons for doing one vs the other. In this ContId and CustId (primary keys in other tables) are foreign keys in the ContractPhase table. They allow you to make querises like ContractPhase ... where ContId = <value> and hase_BegDate > <date> ...

The PhaseSpec is similar, another tbl, specific values with foreign keys to allow location of an upper or related record (this is where Relational in Relational DB applies).

If you canmail me you db tables/fields and a short explanation of what you are trying to setup (Building Contractor, Software Contractor, etc.) I could get a bit more specific. lo

Collapse -

Access 2000 Key fields

by debbie In reply to Access 2000 Key fields

Poster rated this answer

Collapse -

Access 2000 Key fields

by Lo In reply to Access 2000 Key fields

Debbie

I am making some assumpt based on your tbl names. Ans to "Is it ok that I use the same key field in all?" is yes, but.

Usually the prim key to any tbl are those values that will make each rec unique. In cases this is a single field, in others it may take 2,3,+ to achieve.

It appears that the DB is for construction, consulting, etc. Given that a mini-design in such a situation one might have, Customers, Contracts, payment, billing, etc. A customer might have multiple contracts, the contract may have sub-contracts (phases?). A database usually mirrors (with benefits & penalties) a paper system. For above:
Customer: CustID, Name, Address, Phone, Contact, CR_Limit, etc. This tbl would have data that is of a single occurrence and specific to the customer.

Contract: ContId, Amount, Beg_Date, End_Date, Name, etc. This tbl would have data that ... the contract.

In above CustID and ContID are primary keys, assinged, sequential, soundex, etc.

For a given cust,specific cont, the contract requires 3 phases, each with multipe specs, etc.

A ContPhase table would have PhaseId (same/similir to *ID above), ContId, CustId, PhaseName, Phase_BegDate, etc.
The primary key could be PhaseID (unique number within database, or PhaseId + ContID+CustId. There are valid design reasons for doing one vs the other. In this ContId and CustId (primary keys in other tables) are foreign keys in the ContractPhase table. They allow you to make querises like ContractPhase ... where ContId = <value> and hase_BegDate > <date> ...

The PhaseSpec is similar, another tbl, specific values with foreign keys to allow location of an upper or related record (this is where Relational in Relational DB applies).

If you canmail me you db tables/fields and a short explanation of what you are trying to setup (Building Contractor, Software Contractor, etc.) I could get a bit more specific. lo

Collapse -

Access 2000 Key fields

by debbie In reply to Access 2000 Key fields

Poster rated this answer

Collapse -

Access 2000 Key fields

by debbie In reply to Access 2000 Key fields

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums