Design for multi addresses and phones

[Guest]

I need to develop a sales order database that can allow a customer to have
many phones, many addresses, and even many contacts. For example, we sell
vehicle parts. A customer (may be a company or a person) places an order
(this is the first address set with multi phones), but because their vehicle
is getting fixed, the customer may have the parts shipped to the auto shop
(set #2 of address, contact with multi phones). In addition, because some of
what we sell is expensive, the customer may use someone else’s payment
information – say their father’s credit card (set #3 of address and contact
info with multi phones). Sometimes these 3 sets can be the same, sometimes
two of the 3 are the same, and sometimes they are all different. I want
salesrep to be able to easily re-enter all the data without retyping. We need
to track all of this information because of parts warranty and parts core
deposit reimbursement and follow-up after the sale. Of course, a customer can
have many vehicles, so they may order parts for each vehicle and we need to
track all the above info for each order (which may be different the next time
they place an order). However, if it is a repeat customer, we want the
salesrep to easily search for the previous customer info and again easily
re-enter any of the address sets that might still be correct but add new
address sets if the info has changed since they last ordered. It is for this
reason that I planned to have the orders as the focus of this database – it
is not about the individuals that place or pay for the order, it is about the
order (and the vehicle for which the parts are purchased).

Here are my questions:
1) Is that a good general focus for setting up the database?
2) Should I have the BillTo and ShipTo address sets incorporated into the
Order table or have a separate table for addresses, one for phones, another
for types to describe each address and phone, and then join them together?
3) If the answer to #2 is multi tables with joins, how do I develop the
order form based upon so many tables?

FYI – all the data will need to be uploaded to QuickBooks for accounting
purposes, then sent back to the db with updated info (like if the credit card
was rejected, when the order will be shipped, etc.). I don’t know if that may
change your answers to my questions.

I’m a basics+ level user working in Access 2007.

Thanks
CAC

 

[Jamie Collins]

I need to develop a sales order database that can allow a customer to have
many phones, many addresses, and even many contacts.

On a whiteboard next to my desk is written, "How would <<placeholder>>
do it?" and it is used for brainstorming to ensure we don't reinvent
(square) wheels. If, for example, we are considering new UI elements
we might ask, "How would Outlook 2007 do it?"

Perhaps in your case you could ask yourself, "How would Amazon.com do
it?"

Jamie.

--

 

[Allen Browne]

There are several ways to handle this. Choose the one that fits you best.

One way to have tables like this:
- Client: one record per customer, with fields:
ClientID AutoNumber primary key
IsCorporate yes/no distinguish company/person
MainName Text Surname or company name
etc ...

- Address: one record per address, with fields:
AddressID AutoNumber primary key
ClientID Number relates to Client.ClientID
plus the fields for the address ...

- AddressType: one record for each type (e.g. "Billing", "Delivery", ...)

- Order: one record per order, with fields:
OrderID AutoNumber primary key
ClientID Number relates to Client.ClientID
OrderDate, etc.

- OrderAddress: one record for each address that applies to an order:
OrderAddressID AutoNumber primary key
OrderID Number relates to Order.OrderID
AddressTypeID relates to AddressType.AddressTypeID
AddressID Number relates to Address.AddressID

Northwind (the sample database that installs with Access) illustrates
another approach, where the billing address is assumed to be the client's
current address, and the shipping address is stored with the order. This is
less normalized, but more flexible if the addresses really are specific to a
particular order and you need to import them back again from QuickBooks
without affecting the addresses in any other order.

The normalized approach is preferred if it suits what you need. You can
adapt it so that each address in the Address table has a default type, so
you have limiting dates on the addresses (starting date - ending date, so
you can retain a history of clients' past addresses), and a Priority field
to indicate a client's preferred address (useful when the software is trying
to help the user by supplying a default address for a client.)

A fully normalized approach could go even further, by removing the ClientID
field from the Address table. Then add another ClientAddress table, with
fields ClientID and AddressID. This resolves the many to many relation
between clients and addresses, where one client has many addresses, but one
address can apply to many clients.

You also asked about contacts, such as which people to associate with which
companies. That's a whole other question, so this article could help:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
That article doesn't get into addresses, but the principle is the same. The
additional aspect is that a person associated with company X could inherit
the addresses of that company as some of her addresses, when you address her
in the context of that company's business.

CAC, I have not given you a cut'n'dried answer: what I've tried for is to
stimulate possible approaches for you to think about. You seem to have an
adequate understanding of what you need, so hopefully this will help you
make an informed choice.

 

[Guest]

Thanks, your answer helped. After reading through both your reply and link I
realized that although I get the general concept, I won't be able to develop
the db fully normalized according to the "new business model" by myself (not
without a LOT of help, anyway). Maybe I can save that version 2.

Thanks again - for all your replies. I have spent the last few weeks
reviewing many of the forums (as well as MVP sites) and appreciate that
everyone tries to help.

CAC