Relationships for Customers, Addresses, Orders

[Scoop]

I am working on an Order Processing and am having trouble trying to
figure out some of the relationships for Customers, Addresses, and
Orders. Here are my requirements:

I want to support multiple addresses for customers...specifically
multiple shipping addresses. Supporting multiple billing addresses is
fine but not a pressing need.

I don't need to support multiple customers at an address so I don't
think a many-to-many relationship there is necessary. As far as I can
tell a one-to-many for Customers to Addresses is sufficient. I
understand that it's theoretically possible to end up with redundant
information if a customer moves and the new person at that address is
also customer but it's unlikely enough that I'm not worried a/b it.

What I'm having a difficult time with is the relationship between
Addresses and Orders. If I have an Order that is billed to one Address
and shipped to another and two fields in the "Orders" table (e.g.
BillToID, ShipToID), what is the relationship between Addresses and
Orders. Additionally, how do you maintain referential integrity?

 

[Albert D.Kallal]

You have a customer, and that customer can have several (many) address.

So, why not just have a drop down box for mail to, and bill to address that
selects from the above list of possible address(S)
in a order?

If the ship to, and bill to combo box is based on the above list of "many"
address, then you kill two birds with one stone (ie: you *will* have the
ability to have multiple billing address if you need to...).

So, you have a customers table, and you relate a address table to the
customers table......

What I'm having a difficult time with is the relationship between
Addresses and Orders. If I have an Order that is billed to one Address
and shipped to another and two fields in the "Orders" table (e.g.
BillToID, ShipToID), what is the relationship between Addresses and
Orders.

You simply relate the orders table to the customers table. The two comb
boxes for billto, and ship do are simply the list of address for this
customer.

You don't really have a relationship from order to address in a sense (at
least no cascade deletes, or updates -- I mean, if you delete a order that
you made by mistake...nothing EVER happens to the address table anyway. ).

So, just build a orders form/table, and when you launch this orders form,
have the shipto, and billto combo boxes be loaded up with the address
list...you are done....really, not a difficult relationship at all. In fact,
you don't even need to setup the relationship in the designer to do this.
Sure, drawing the lines in the relationship builder from the order table to
the address table for both shipto, and billto might help..but, really, you
gain little, if anything by doing this. However, for documentation issues,
you might want to draw the lines. You should try and distinguish the
difference between a enforced relationship with cascade deletes, and that of
a lookup. In your case, the Shipto/billto is more of a look up value to the
address you want. Hence, our orders table will have a ShipToAddresID, and
BillToAddressID, and they get their values from the address table. I suppose
you could enforce the relationship here..but since the user never types in
the address id themselves, then how will they mess this up?

So, sthipto, and billto will simply be combo boxes that get their values
from the address table...

 

[Scoop]

Thanks Albert! That helps a lot...just a couple of questions?

If an address is removed at some point (with or without a customer),
doesn't it present a problem for orders that have the ID number in
either the ShipToID or BillToID field?

Also, I didn't think you could actually have two relationships between
the same two tables. I tried it in the designer and it created the
relationship but added a table in the designer view (i.e.
tbl_Addresses_1). Is this normal?

 

[Joseph Meehan]

Thanks Albert! That helps a lot...just a couple of questions?

If an address is removed at some point (with or without a customer),
doesn't it present a problem for orders that have the ID number in
either the ShipToID or BillToID field?

You should not remove the obsolete addresses, rather you want to add a
field to indicate them as inactive. A binary yes-no field works well.

 

[Albert D.Kallal]

If an address is removed at some point (with or without a customer),

With or without customer is confusing here???

You will HAVE to have a customer table entry BEFORE a address entry can be
made into the address table.

If you delete a customer, then all orders and address will cascade delete

So, yes, you can certainly remove a address entry anytime you wish.

doesn't it present a problem for orders that have the ID number in
either the ShipToID or BillToID field?

No, not a all. It is just two plan Jane long number fields, and you store a
address ID number in that field. Nothing special, or out of the
ordinary. here. I would consider having the default for these two fields
null...and not zero...

Also, I didn't think you could actually have two relationships between
the same two tables. I tried it in the designer and it created the
relationship but added a table in the designer view (i.e.
tbl_Addresses_1). Is this normal?

Yes, the above is normal. As I mentioned, drawing the lines is really
optional in this case. What benefit do you gain by drawing the lines?

Actually, there is a benefit if you do enforce RI. If you try and delete a
address that is STILL used by a order, you will NOT be able to do this until
you delete the order first, or change the address to something else....