Many To Many relationship question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Many To Many relationship question

Author

Topic

mikener
Starting Member

4 Posts

Posted - 2009-10-23 : 12:41:31

I am not sure of the best way to handle the following scenario.

My system has many customers. Each customer can have several addresses. The addresses can be of several types, billing, shipping...etc. Many of these customers can point to one specific address. When a customer moves from one address to another, other customers that share this address should not have their addresses changed.

Here are my tables:

Customer which has a Primary Key of CustomerID
Addresses Which has a Primary Key of AddressID
AddressType which has a Primary Key of TypeCode

CustomerAddressXref which has the following fields:

CustomerAddressID - Primary Key
CustomerID from the Customer table
TypeCode from the AddressType table
AddressID from the Addresses Table.

In addition to these tables, I have an Orders table. The 3 fields of interest are the CustomerID, ShippingAddressID and the BillingAddressID.

My first thought was that the ShippingAddressID and the BillingAddressID should be dependent on the Addresses table and should therefore contain the AddressID from the Addresses table.

That's when I ran into a problem. If a Customer moves its billing address from 99 Fortress of Steel to 12 Metropolis place I am forced to find every order for this customer and change its billing address.

I thought there must be a better way, so I changed 99 Fortress of Steel to 12 Metropolis place on the Addresses table. That caused a different problem as other customers that pointed to 99 Fortress of Steel didn't want to move.

That left me with my current solution. I changed the Orders tables so that it was dependent on the CustomerAddressXref table instead of the Addresses table. This mean't that I only had to change the AddressID column on the CustomerAddressXref table instead of changing all of the Orders. This seemed like an ideal solution. I was then told that it is a bad idea to be dependent on a cross reference table.

I don't have enough experience to know why creating a dependency on a cross reference table is a bad idea. Can anyone explain why this is a bad idea?

Can anyone tell me how they would handle this?

Thanks so much, I am really stumped.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:38:25

i think you need to link order to only customer via customerid. the address details should be linked to customer as its customer who had sipping address,billing address etc. order just needs to refer to which customer placed it.

mikener
Starting Member

4 Posts

Posted - 2009-10-23 : 14:52:11

First I would like to thank you for getting back to me. I am really under the gun to figure this out.

I had thought of doing as you say (linking the order to customer id only). If I do that I am not sure how I can determine which shipping and billing address to use as the customer can have many shipping and billing addresses.

Any ideas?

Thanks

GrTech2009
Starting Member

6 Posts

Posted - 2009-10-28 : 15:14:12

quote:
Originally posted by mikener

First I would like to thank you for getting back to me. I am really under the gun to figure this out.

I had thought of doing as you say (linking the order to customer id only). If I do that I am not sure how I can determine which shipping and billing address to use as the customer can have many shipping and billing addresses.

Any ideas?

Thanks

Lets look at your senerios here:

One customer can have 1 or more addresses hence (one-many)
One Customer can place 1 or more orders. (one-many)

I would design my address table in the following way
tbladdresses (addressid, customerid,Addresstype) + what every field you need for address such as city state zip etc.

by structuring the address table this way you can have a 1 to many relationship between tblcustomers and tbladdresses linking them by customer id. the address type field will specify what category the address is eg. shipping add., billing address etc.

mikener
Starting Member

4 Posts

Posted - 2009-10-28 : 15:58:16

I am sorry if I am being a little dense here but doesn't the methodology lend itself to duplicate data for the address information (City, state zip...)?

There is also a point here that I unfortunately didn't make clear (sorry). A Customer may have more the one billing or shipping address. So unless I am mistaken, this means that given the customer number from the Order I would not be able to determine the correct billing address when reading tblAddresses if the Customer had more then one Billing Address.

Does this make any sense? From what I can see I am forced add a Billing and Shipping Address column to the orders table. I then have to do one of two things:

1. Make the Order dependent on the Addresses table and then modify each order when the customer moves or

2. Make the Order dependent on the CustomerAddressID from the CustomerAddressXref table. This means that when a Customer moves all I need to change is the Address ID column on the CustomerAddressXref record so that it points to the new address. Although this is easy it doesn't feel right to me.

Thanks

GrTech2009
Starting Member

6 Posts

Posted - 2009-10-28 : 16:43:36

quote:
Originally posted by mikener

I am sorry if I am being a little dense here but doesn't the methodology lend itself to duplicate data for the address information (City, state zip...)?

There is also a point here that I unfortunately didn't make clear (sorry). A Customer may have more the one billing or shipping address. So unless I am mistaken, this means that given the customer number from the Order I would not be able to determine the correct billing address when reading tblAddresses if the Customer had more then one Billing Address.

Does this make any sense? From what I can see I am forced add a Billing and Shipping Address column to the orders table. I then have to do one of two things:

1. Make the Order dependent on the Addresses table and then modify each order when the customer moves or

2. Make the Order dependent on the CustomerAddressID from the CustomerAddressXref table. This means that when a Customer moves all I need to change is the Address ID column on the CustomerAddressXref record so that it points to the new address. Although this is easy it doesn't feel right to me.

Thanks

you need to establish a unique key for all billing addresses and shipping addreses.

You can have the following:

tblcustomers
custID
fname
lname
Gender
Ship_addr_num (unique identifier for each address)
Bill_addr_num (unique identifier for each address)

tblCustomer_address

custID
address_num,
Addrline_1
Addrline_2
city
state
zip

So lets say you want to query Bobs Ship to address it would be something like this

SELECT ca.custID,
ca.addr_num,
ca.Addrline_1,
ca.Addrline_2,
ca.city,
ca.state,
ca.zip
FROM tblcustomer_address ca
JOIN tblcustomer ON ca.address_num = c.ship_address_num
WHERE c.custID LIKE '12345'

Just speculating here but I think this would work.

SELECT

mikener
Starting Member

4 Posts

Posted - 2009-10-29 : 09:05:43

It would help me understand your methodology if you could show me an example with data. For instance in the table tblcustomers, I am not sure how CustID 12345 would have multiple billing and shipping addresses.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources