Please offer your thoughts on the best way to set this up . . .

[standridgekelly]

I am doing a major overhaul to a database I created *gasp* 12 years
ago! It was my first, in Access 2.0 and very crude. It is a simple
order-processing database. Most of our customers have one address for
shipping & billing, some have separate shipping & billing addresses
(of course, we need to keep both addresses on file), then we have drop-
ships, where a customer sends us an order to be shipped directly to
THEIR customer. Most likely, this address will never be reused. So,
I'm debating the following scenarios:

1. Use the billing address in the customer table, create a related
shipping address table, create an order form that would use both
addresses (selectable via combo boxes or something). This would create
a lot of shipping addresses that would seldom be re-used.

2. Add the shipping address to the orders table, so that shipping
addresses could be associated with an individual order. I could still
have a separate shipping addresses table to easily populate the order
form controls, but then I would have redundancies in my database (i.e.
shipping address stored in both the shipping address table and the
order table). Along this same line, I could store both billing &
shipping addresses in the customer table, then have shipping address
stored in the orders table, but again this results in redundancies.

3. Create a separate order table for drop-ship orders. Since I use
autonumbers as my invoice numbers, this might create confusion, and
would probably be a little tougher for the bookkeeper to track.

So, does anyone have any thoughts, tips, suggestions for how to
proceed with this? I am not a full-time programmer, this is just one
of the many hats I wear at my company

Thanks,
Kelly

 

[Klatuu]

Do not even consider #3. It is a really bad idea that will make your life
really miserable.

Don't worry about the shipping addresses you think may never get used again.
So what. They are there and convenient if you ever do. They really don't
cost that much.

Assuming your customer will always and ever have only one billing locations
and that there is absolutely no possibility they will establish a branch
office and want billing to go there, then storing the billing address in the
customer table is only slightly distasteful. The issue here is that if a
customer's billing and shipping address happen to be the same, you have just
violated the redundancy rule. It is not at all uncommon for both to be the
same.

The best practice is to keep all addresses in an address table. I would use
an autonumber primary key and in the order entry form, use combos to select
billing and shipping addresses for the order. Then you store only the
primary key of the address table in the order table as a foreign key. It can
be helpful to store the key values for the billing address and even the key
value for a default shipping address in the customer table.

 

[standridgekelly]

So, if you don't have an address stored in the customer table, how
would you identify stores that might have the same name - i.e. Mom &
Pop's, which happens to be located in Florida and Mom & Pop's located
in New York? Our customers rarely have their customer number, so using
the address is a cross-check for us to make sure we have the right
customer.

 

[Klatuu]

Each customer record should have a primary key. In this case, I would
suggest an autonumber. Then in the address table, you store that number in a
field as a foreign key so you know which customer it belongs to. And in the
customer table, you have a field that stores the primary key field of the
address that is their default billing address. Use a query for the form's
record source that links the customer and address table based on the field in
the customer table that identifies the billing address and the primary key
field of that address record.