Relationship Theory

[Homegrownandy]

Who would have thought I come to the access forums for relationship advice?



I'm just after some opinions more than anything.

I have a table of contacts. This table lists every person within the business and related to the business (external). Each contact has an associated company so that could be our own company or another.

This is all fine. But when I come to defining relationships to the order table I'm wondering what is best practice.

The orders table should usually have two contacts in each record. We have the person within our company responsible for the order and then we have the person in the external company who placed the order. The user can only select the relevant people based on the company ID so again. No issue there.

I'm wondering what is best so far as linking the tables in the relationship manager. Do I even need to?

[ranman256]

You should for Parent -Child tables.
like a web shopping cart: tOrder to tOrderDetails tables.
Or
tClient to tClientPayments.

but not for lookup tables, like the Country or State field.
The State field is filled by looking up the code in tState table and filling the field in the tOrder table.
It is not a child of tOrder, just a lookup, so I don't make a relationship.

[Homegrownandy]

Since I've been doing this for two years now, when I started I knew almost nothing. Now that I do know something, I look back and to be honest although it works its pretty messy. So I'm redesigning it and moving the data over to a new database with the bare minimum.

So little things like this where I'm not sure, its great you can answer them questions for me. Its surprising how much I know now compared to before.

[pbaldy]

You would the contacts table twice. Access will alias the second copy. You relate each of the two fields in the orders table to a different instance of the contacts table.

[Homegrownandy]

That's the way I have done it previously but with no relationship at all it still works as intended. It just looked messy with multiple tables with the same data.

[pbaldy]

Whether or not you create the relationship, you'd have to do that in queries to get names associated with contact ID (which I assume is what you're storing). With the relationship established you can enforce referential integrity, but of course that's up to you.

[Homegrownandy]

Okay, thanks for the input. Was just making sure no one on here would shout at me for not defining the relationship before the query haha.