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
|
|||
|
|||
Using Primary Keys
Currently I'm working on a data base that tracks inventory for clients.
I am using 4 tables - Client Info (primary key Client Name and Client ID) - Inventory (primary Key Crate number) - History (Primary Key HID auto number) - Crate Contents (Primary Key CID auto number) Each client has several crates and they can't be duplicated, but crate numbers can be duplicated if used by a different clients. Is there a way to do this? -- Thanks, Jodie |
#2
|
|||
|
|||
Using Primary Keys
Certainly, this is a classic Many-to-Many relationship:
Each Client can use one or more Crates Each Create can be used by one or more Clients. To model this, you must create a Linking table composed of the primary keys of each of the other tables. It would be better and easier if you had a single primary key for the ClientInfo table that was an autonumber field. Given that, your table would look like this: ClientCrate ======== ClientID (fk)(cpk) CrateNumber (fk)(cpk) Notice that you will make a compound primary key (cpk) of BOTH fields in your linking table. You would then create a relationship between ClientInfo and ClientCrate on ClientID and another between Crate and ClientCrate on CrateNumber. If you MUST have a compound PK in ClientInfo (this would take a lot of convincing on my part), you would need to add ClientName to ClientCrate and add that field to the relationship as well. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Jodie" wrote in message ... Currently I'm working on a data base that tracks inventory for clients. I am using 4 tables - Client Info (primary key Client Name and Client ID) - Inventory (primary Key Crate number) - History (Primary Key HID auto number) - Crate Contents (Primary Key CID auto number) Each client has several crates and they can't be duplicated, but crate numbers can be duplicated if used by a different clients. Is there a way to do this? -- Thanks, Jodie |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple Primary Keys | Mike | Database Design | 7 | July 19th, 2004 05:53 PM |
Primary Key not sorted | Mike | Database Design | 6 | June 16th, 2004 11:11 PM |
Primary Keys | js | New Users | 5 | June 8th, 2004 11:36 PM |
Choosing Primary and Foreign Keys | A.V.H | New Users | 8 | May 23rd, 2004 09:12 PM |
Primary Keys and Relationships | TR | Database Design | 2 | May 23rd, 2004 03:19 AM |