Need help with establishing relationships

[nedstar1]

Hi friends,

I've been dipping my toes deeper and deeper into Access, and it's finally time to get wet.

I've built my tables as follows (there are quite a few):
BrandContacts
Brands
ManufacturerContacts
Manufacturers
PartNumbers
JobMaster
JobDetail
JobItemDetail
JobPackDetail
JobProductionDetail
JobRenderSample

I'm now working on creating the relationships and revising the fields as required - I've just read a post on normalization, and I'm going to go back and add a tbl prefix as soon as I post this.

I have a primary key in each table, designated by the suffix ID.

Here's my logic - and this is what I need help with, so anyone who cares to, please check my reasoning - The purpose of the database is to track an order from inception to payment post-delivery. Rather than address the broad topic, I'll focus on an example.

I understand the idea of the one to many relationships, but I'm a bit stumped as to how to tie all of this info together in the broad sense.

The PartNumbers are relatively static; this table contains these fields:
PartNumberID
GCItemNum - our part number
ManItemNum - manufacturer's catalog part number
Manufacturer - manufacturer's name

In the Manufacturers table, I have:
ManID
ManName
ManContact

In the ManContact table, I have:
ManContactID
various contact info fields

Let me use these as an example of my reasoning - since there will be many contacts per manufacturer, this should be a one (manu) to many (cont) relationship. But this relationship - should it exist between the ManID and ManContactID primary keys, or should I be inserting a distinct field specifically to link to the primary key of another table? Would one method be better than the other?

---------

Every order we get is essentially a custom job - for this reason, I have created the JobItemDetail table and a lot of subtables - I hope to use these to tie specific information about an item back to a part number, without having to create an intricate, bloated part number table with every conceivable option for every conceivable part number. My thinking is that I can store the specific info for an item on a job order here, and tie it both to the part number and to the job order number. That would be a one to many relationship between the PartNumberID and the JobItemDetailID fields, correct? (One P/N, many records listing requested options)

If someone can kibbitz me at this point, I'd feel much more confident about moving forward.

TIA
Regards,
Nedstar1

 

[willir]

Nedstar1

Simple example...
[tt]
JobMaster JobDetail
MasterID JobDetailID
MasterDesc MasterID
[/tt]

MasterID is primary key on the JobMaster table. Each job has it's own unique ID.

MasterID is a foreign key on the JobDetail table.
All detail records with the matching MasterID belong to the one Job Master record.

Now lets look at the example you presented on contacts for manufacturers.

In the Manufacturers table, I have:
ManID
ManName
ManContact

since there will be many contacts per manufacturer, this should be a one (manu) to many (cont) relationship

So you have a company called Best Widgets, and they have two great contacts - Terry Brooks and Ursula LaGuin. Which of these contacts are you going to store in ManContact field on the Manufacturing table?

Your design should actually be...

In the Manufacturers table,
Table Name: ManuTbl or tblManufacture
ManID - primary key
ManName

Contact table
Table Name: ContactTbl or tblContact
ContactID - primary key
ContactLN - last name
ContactFN - first name
ManID - foreign key to the manufacturing table.

Primary key on the "ONE" side is used as the Foreign key on the "MANY" side.

This design will allow a manufacturer to have many contacts.

...Moving on
The next step after creating your tables is to create the relationships. Access has a great tool for this - from the menu select "Tools" -> "Relationships". Add the tables to the "page", arrange the tables in a logical order. THEN
click on the primary key in one table and drag it to the appropriate foreign key. When "Edit Relationship" popup window opens, select "Enforce Referential Integrity".

The next hurdle will occur when you create forms and subforms. If you created your relationships correctly, when you drag your subform onto your main form, Access will automatically link the two forms using the Master linking field and the Child linking field - sounds confusing I am sure, but Access will basically use the relationships you created to automatically link the child records to the master record.

Richard