I apologize if this is not the correct forum to ask this question. I can't seem to get a straight answer to this question.

Given the following tables:

Customer
Primary Key CustomerID

Addresses
Primary Key AddressID

CustomerAddressesXref
Primary Key ID
Unique Key CustomerID + AddressID

There is a many to many relationship between Customers and Addresses, so I created the CustomerAddressesXref table.

Orders
Primary Key OrderID

The orders table contains a field called BillingAddressID.

Here is the question. Should the BillingAddressID on the orders table be constrained against the AddressID field on the Addresses table or the ID field in the CustomerAddressesXref table?

Can you explain the reasons behind the decision?

Thanks so much.

How would I do this:
First I need to check, you say many to many because:
> a customer can have multiple addreses?
> and at any given address there can be multiple customers?
If the answer to both questions above is yes then I would call this a many to many relationship. if one of the answers is no then you dont have a many to many relationship situation.
NOTE - PK = Primary Key and FK = Foriegn Key (All my PK's are calle ID there is really no need to have such long field names.)
Customer tble
ID (PK)
Addresses tble
ID (PK)
customerAddresses tble
ID (PK)
customerID (FK related to customer.ID)
AddressID (FK related to addresses.ID)
Orders tble
ID (PK)
billingAddress (FK related to customerAddresse.AddressID)
Why - Becasue you need to know what address to send it to. When you think about it all the address ID's are linked...

__________________
Wind is your friend
Matt

http://www.elitemarquees.com.au

Thanks for responding. I am however confused about one thing. Based on the statement "Why - Becasue you need to know what address to send it to. " I would have thought that you would have said the billingAddress on the Orders table would relate to the Addresses table, not the CustomerAddresses table. Why is it better to relate to the link table?

Thanks

ps. Yes it is a Many to Many relationship - Each customer can have many addresses and each address can point to many customers.

I probably didnt explain myself all that well. Yes you will get the physical address from the addresses table. Of course this is the only table where the text string of the address sits. The customerAddresses table only stores the ID. As I said all three table are related and have relationships. When customer places an order you will I assume need to ask them which one of thier multiple addresses they wish to be billed? therefore get the address from the address table. I would link orders to both tables and I would get the address like so:

SELECT addresses.physicalAddress FROM (((addresses
LEFT OUTER JOIN customerAddresses ON addresses.ID = customerAddresses.addressID)
LEFT OUTER JOIN orders ON customerAddresses.addressID = orders.billingAddress)
WHERE Orders.ID = " & ThecustomerAddressesID & ";"

The ThecustomerAddressesID variable would be identified by the customerID and the billingAddress they instructed the bill to be sent to

__________________
Wind is your friend
Matt

http://www.elitemarquees.com.au

If I understand you correctly you are saying that the BillingAddressID field on the orders table should store the ID of the CustomerAddressesXref table (which points to the Addresses table) instead of storing the AddressID on the addresses table.

Is this the standard approach DBA's use when setting up constraints against a many to many relationship?

Thanks so much for the help.

No but I can see why you thought that, my mistake Above I said:

billingAddress (FK related to customerAddresse.AddressID)

it should have said:

billingAddress (FK related to Addresses.ID)

NOTE - You have figured out my 'CustomerAddresses' table is what you call 'CustomerAddressesXref' sorry I should have kept with your table name. I tend to name my tables simply which relect the core data. After all the table holds customer addresses....

I would relate orders.billingAddress to addresses.ID which means it stores the ID fro the addresses table

__________________
Wind is your friend
Matt

http://www.elitemarquees.com.au

No need to apologize. I am just glad that we cleared that up. I asked the original question because my boss believes that the dependency should be against the cross reference table (CustomerAddressesXref). In fact, he believes that whenever there is a many to many relationship, tables like orders (or whatever) should always be constrained against a cross reference table. I don't have enough database design experience to argue with him. To make matters worse, he has less experience then I do.

Thanks