Normalization?

[Barry]

Greetings
I am creating an application which is rather typical in it's need for
customers and orders. I have a table for Customers one for Orders and one
for Customer Shipping Addresses. My question is in trying to find the
correct order for the relationships between these tables. Should the order
be; Customers, CustomerShipAddress, Orders or CustomerShipAddress, Customers,
Orders? The goal is to allow the selection of a customer and fill in the
appropriate customer info and on a separate tab another combo to select the
ShippingAddress info. The Orders table contains the OrderID, CustomerID and
ShipToID and I should mention an OrdersDetails table for the line items. The
problem arises when trying to save the ShipToID to the orders table. When
selecting the ShipAddress info i have used unbound fields filled
programatically from a combo box and the form is bound to the Orders table
with the OrderID field displayed. I am having trouble with this design and
would like to know if anyone has tried something like this before, know of
any examples or can please give me some direction.
Thanks,
Barry

 

[mscertified]

I'd say the Shipping Address table should be related to the Customer Table
via Cutstomer ID.
ShipAddress.CustomerID --> Customer.ID
A problem could arise if you have an order to be shipped to two different
addresses.
Your foreign keys in the Orders table are ok but must be related as:
Order.ShipToID --> ShipAddress.ID
Order.CustomerID --> Customer.ID

-Dorian

 

[mnature]

Microsoft has this template for customer orders:

http://office.microsoft.com/en-us/templates/TC010185471033.aspx?CategoryID=CT011366681033

When you start a new database, there is a wizard for creating an Order Entry
database.

Here is a data model for customers and orders:

http://www.databaseanswers.org/data_models/customers_and_orders/index.htm

Here is a data model for tracking orders:

http://www.databaseanswers.org/data_models/tracking_orders/index.htm

 

[Barry]

Thanks for the info. The first data model most closely represents my
application however, the AddressID field does not appear in the
Customer_Orders table. I have the AddressID in my Orders table but, the
question is then how do I relate the AddressID from the CustomerAddress table
to the AddressID in the Orders table. This is using a relationship very
similar to the example.

Thanks,
Barry

 

[Barry]

Thanks for the comments. I certainly didn't think about one order shipped to
two different addresses. I suppose that I considered them as two different
orders. At present my tables are setup as CustomerAddress ---> Customers
with CustomerID in the CustomerAddress table as the link. One to many
Customers to CustomerAddress. Customers ---> Orders with the CustomerID in
the Orders table as well as the AddressID. I guess that my question then
really is how do I relate the AddressID from the CustomerAddress table to the
AddressID in the Orders table? I was using this AddressID to avoid
duplicating address information already existing in the CustomerAddress table.
Thanks again,
Barry

 

[mnature]

This is excerpted from the Microsoft web site, dealing with normalization:
http://support.microsoft.com/kb/283878/EN-US/

"EXCEPTION: Adhering to the third normal form, while theoretically
desirable, is not always practical. If you have a Customers table and you
want to eliminate all possible interfield dependencies, you must create
separate tables for cities, ZIP codes, sales representatives, customer
classes, and any other factor that may be duplicated in multiple records. In
theory, normalization is worth pursing. However, many small tables may
degrade performance or exceed open file and memory capacities."

It sounds as if you are placing addresses in a separate table, to avoid any
duplicate addresses in your customer table. Though that is a noble pursuit,
I believe in this case it is unnecessary, and possibly could cause you
problems. If you have two firms that exist in the same building, they could
have the same mailing address. If one of the firms moves out, then how do
you know whether you are changing an address for just one firm, or for two or
more firms, when you update the address? Better to have a little less
normalization, and have full control of what you are doing. You may not
remember, six months from now, that you have set the addresses up this way.

 

[Barry]

Thanks again for your reply. Just to clarify, the addresses in the
CustomerAddress table are tied to the Customer via CustomerID so the problem
of changing the wrong address is virtually impossible. Having all of the
data in a single table means that a unique autonumber field is out of the
question, and you would need two unique values to identify any one customer
with multiple ship addresses. I unerstand and appreciate what you are saying
about too much normalization. In this case there are approximately 1500
Customer records most having at least 2 Shipping addresses and I did not want
to expand the Customer table to nearly 3 times its' size. The question still
remains with this particular configuration, how does one add the ShipID to
the Orders table. Does the query need to contain all three tables? Should
the ShipID be bound to the same field in the Orders table?

Thanks,
Barry

 

[Tim Ferguson]

"EXCEPTION: Adhering to the third normal form, while theoretically
desirable, is not always practical. If you have a Customers table and
you want to eliminate all possible interfield dependencies, you must
create separate tables for cities, ZIP codes, sales representatives,
customer classes, and any other factor that may be duplicated in
multiple records. In theory, normalization is worth pursing. However,
many small tables may degrade performance or exceed open file and
memory capacities."

Yebbut they're wrong. This was probably written by one of the Excel team
who doesn't really understand databases or R theory. It looks as though
many of them moved over to the Access UI team (autofill, autocorrect...)
and now, GHUA, they're taking over the jet engine too.

Tim F

 

[Barry]

Tim:
I am trying to keep to normalization rules as much as possible and/or as
much as I understand them. I have no loyalties to any of the "teams" and
just would like to make my application run as best I can. If you have
suggestions on the setup or comments about the functionality of this
particular app I would be very interested in hearing them.
Thanks,
Barry

 

[mnature]

Thanks again for your reply. Just to clarify, the addresses in the

CustomerAddress table are tied to the Customer via CustomerID so the problem
of changing the wrong address is virtually impossible. Having all of the
data in a single table means that a unique autonumber field is out of the
question, and you would need two unique values to identify any one customer
with multiple ship addresses. I unerstand and appreciate what you are saying
about too much normalization. In this case there are approximately 1500
Customer records most having at least 2 Shipping addresses and I did not want
to expand the Customer table to nearly 3 times its' size. The question still
remains with this particular configuration, how does one add the ShipID to
the Orders table. Does the query need to contain all three tables? Should
the ShipID be bound to the same field in the Orders table?

I would suggest using two queries, with one query to include customers and
orders, and the other query to include customers and shipping addresses. Use
a main form, which will allow you to choose the order/customer, and then use
a subform that is tied to the main form through the customer ID, for choosing
the shipping address. You could have another subform, that would be for
choosing the products to go with that particular order. You usually cannot
put all of this information onto one form, without using subforms, because of
the types of joins that you have.

 

[mscertified]

The address table primary key will be AddressID (can be an autonumber) also a
foreign key to CustomerId
Your Order table refers to Customer via CustomerId and to Address via
AddressID.
The latter is a one-to many relationship (one address many orders) - that is
provided an entire order always goes to one address.

Dorian

 

[Barry]

Thanks for all the help. I think I have a much better grip on the situation
now. Again, I appreciate your expertise.
Thanks,
Barry

 

[Barry]

Thanks for all your help and suggestions. I believe that I have a pretty
good grip on the situation. I will have a go at revamping some of my forms
and seeing what happens. If anything turns up I will post another message.
Thanks again.
Barry