Need advice with relationships

[nedstar1]

Hi,

I'm in my fifth attempt at designing a database, and I'm really frustrated. My issue is that I can't quite get a solid understanding of how I should set up my table relationships. The overall goal here is to create a form that will allow a user to enter basic quantity information, select a part, and view calculated prfoit margins on the fly. This form will also (I hope) allow additions to the part database. I'm not asking about the queries and the form design here - most of my progress so far is in this area, I just need help with the table relationships.

I have:
TblCustomerData
CompanyName
ContactFirstName
ContactLastName
CompanyorDepartment
BillingAddress
City
StateorProvince
PostalCode
Country/Region
ContactTitle
PhoneNumber
Extension
FaxNumber
EmailAddress
Notes

TblItemMaster
PartID
PartNumber
PartDesc1
PartDesc2
ItemFOBCost
ItemDDPCost
ItemAddlCost

TblVendorData
VendorID
VendorName
VendorAddy
VendorCity
VendorStateorRegion
VendorPostal
VendorPhone
VendorFax
VendorPort1
VendorPort2
VendorPort3

These tables are created, but have no data as yet. The initial field in each table is a primary key/autonumber field. I need to have multiple products associated with each vendor, and multiple products associated with each customer.

Can anyone tell me how to handle the relationships for these? Any suggestions on perhaps splitting out information into other tables? This is by far the hardest part of the process for me - anything anyone could offer is most welcome and appreciated.

Regards,
Ned

 

[BNPMike]

To start with don't do anything with relationships. Just build some tables and join them in the query window to test your model. It looks like you are missing a table that holds customer-parts.

 

[nedstar1]

Hey BNPMike,

Thanks for the quick reply. I have a quick question about the customer-parts data. Should I create a table that specifically is used to show what customers order what? Is that wahat you mean?

Also, sorry to be so dense, but is joining the tables in the query window the same thing as defining relationships? ie, don't I need to have my relationships defined BEFORE I start to work on the query?

TIA, and best regards,
Ned

 

[BNPMike]

You don't need to define relationships. In the query design screen just click on the join field of one table and drag it to the join field of the other table. That creates a join for the query just the same as if you had declared relationships. There are good reasons for relationships but, between you and me, I haven't got them on any of my current databases.

You probably need an order table and then an order-item table.

Access used to come with a sample database called Northwind. If you have that it will be very useful to look at it as it is probably very similar to what you want.

 

[lespaul]

Relationships are defined in order to enforce referential integrity between tables. For example, I have a database with a table tblStatusCodes:

StatusCode StatusDesc
NR No Response
WS Will Serve
PP Postponed

I have a table tblMain with a field StatusCD. For each person's record they are assigned a status code. Referential Integrity ensures that a status code CANNOT be entered in tlbMain that does not already exist in tblStatusCodes.

Even if you add the relationships without enforcing referential integrity, all you are really doing is showing Access how to relate the tables together IN THE QUERIES.

It looks like you haven't identified which vendors supply which parts. You'll need a table that has both vendorID and PartID as a composite PK and you'll probably want to put the cost of the item in THIS table instead of the Item table that way you have each vendor's price.

You may also want to break out the CONTACT information from the COMPANY table, do you have any companies where there is more than one contact? If so, then you should do this.

If you are going to have multiple part descriptions, again you should have a table that has DESCID, PARTID, DESCRIPTION with DESCID and PARTID making up a composite PK.

The same with the VENDOR PORTS.

Have you read "Fundamentals of Relational Database Design"? It's available on JeremyNYC's website in the Developer section.

http://www.alphabetcitydataworks.com

HTH

Leslie

 

[BNPMike]

You're right Leslie but unfortunately Microsoft don't call it the Constraints or Database Integrity screen. They call it relationships so people think you connect things here and that's done. So they create a form, mention fields from two tables and can't understand why Access acts as if it knows nothing about the relationships you've lovingly declared. One bloke was convinced it created pointers to connect the tables. He knew the number of bytes per pointer, but went quiet when I asked him why the mdb didn't grow by the suggested amount.

 

[nedstar1]

Thanks for the replies. Leslie, your answer is greek to me at present. I'm going off to read the suggested article to see if the waters are clearer. BNPMike, thanks for the explanation - setting the association in the query - is this something that must be done for each query? Do I need to worry about primary keys if I'm not using relationships?

TIA

Regards,
Ned

 

[nedstar1]

I've read over a lot of the suggested article, and I'm starting to understand. I need some clarification on composite keys, however.

A composite key uses two fields to establish a unique identity, right? So an item could be referenced using a partnumber and a vendorid, right? That would mean seelcting a partnum, a value that can come from many vendors, and also validating it by specifying a vendor - correct? How is this set up via the relationships window?

Also, any harm in using a single contact table to hold info from both vendors and customers?

I'm beginning to see that the goal is really to be as granular as possible with the info - break it down as much as possible - is this a correct assumption?

This thread is really smashing my glass ceiling - I am grateful to everyone for their patience/input.

Regards,
Ned Hodgson

 

[BNPMike]

You must establish the joins yourself for every query. If you declare relationships, Access will generally put the right one in for you. It can't always be right because you can have more than one relationship between two tables and only you can decide which you want in those situations.

Primary keys are like relationships. You don't absolutely need them but contrary to my other advice I suggest you always declare them. It is a fundamental concept and if you are not totally clear about your keys you will struggle to move forwards with your data model.

 

[lespaul]

Ned,

Glad you are finding the article helpful!

A composite key uses two fields to establish a unique identity, right? So an item could be referenced using a partnumber and a vendorid, right? That would mean seelcting a partnum, a value that can come from many vendors, and also validating it by specifying a vendor - correct?

That's exactly right!!

Also, any harm in using a single contact table to hold info from both vendors and customers?

No harm at all! But I would put an indicator field (C or V) to distinguish between the two.

At this point I wouldn't worry too much about the relationships. If you set your tables up correctly, the rest is cake!

Leslie

 

[Olaf4]

This question is for BNPMike. Could you please give me a very good reason or reasons to not to use o worry about tables relationship?
Thanks

 

[BNPMike]

Olaf

Declaring relationships is I suppose basically an all-good thing to do. My concern is, for people who haven't read manuals on Access (which is a lot on this channel), it will confuse them into thinking that they are connecting tables. Worse however than this is they might start declaring referential integrity before they've worked through their data model properly.

Either way they find things happenning that they didn't expect or intend. I try and point out you don't need relationships or referential integrity.

First build and explore your model/application and when you are comfortable with that, and with Access, by all means go back and declare relationships and integrity constraints.

If you know Access then by all means use relationships freely.

 

[Olaf4]

Thanks BnPMike. I thought I was missing something new. It's OK your point.
But if people don't understand relationships, they cannot build a very good application and can lose data in orphan records.
Orphan records can lead to inaccurate results.
Thanks again. If in the future you know something better, please tell us.

Now, to nedstar1. I think you have to create two more tables. One between CustomerData and ItemMaster and the other one between VendorData and ItemMaster.
Think like this: one customer can order many Items and one Item can be ordered by many customers. Then, you need that table. The same for Vendors.