Normalization

Zzyzzyth · Oct 6th 2006

I'm stumped. I have a typical small company database designed for a transportation company. I have the tables busted out as follows:

Customers
Carriers
Consignees
Shipments
Order Details
as well as a few others. But those are the main tables involved in my dilema.

Our Orders table, as it stands right now, has foreign keys linked to the Carrier that hauls the product, the Customer that the shipment gets billed to, and the consignee that the product arrives at.

I encountered a problem the other day where we actually delivered a shipment for one of our carriers to one of our customers. So, the carrier has become the customer, and the customer became the consignee.

How do I avoid duplicating the two entities into two different tables? I can't use the primary keys assigned to them because they have been used in the contradicting tables already. Any help would be appreciated, let me know if you need more info. Thank you.

carlmack · Oct 6th 2006

Re: Normalization

This is a common problem but as far as I know there is no "correct" solution. It all depends on the company and the frequency of occurrence. Different systems go down different paths.

If this is unusual then I would suggest you do indeed create two master records (one in customers and one in consignee) for the same company. The problem is that throughout your system the two probably behave very differently and the small sacrifice you make to the normalisation ideal is probably not worth the effort of going through the whole system and trying to make one generic table.

Some systems (eg. SAP using the "partner" concept) do attempt it by creating a generic entity that you do business with, but it can become hard work. You end up needing to define "partner" properties that in turn define behaviour.

I would say learn the principles of normalisation but let common sense prevail.

HTH

Carl

Zzyzzyth · Oct 6th 2006

Re: Normalization

I would say that it is unusual to the tune of about 2-3 of 2,000 shipments per month. But, not unusual in the respect that I can count on a few per month to happen. I am actually still in the design phase, so I wanted to address it now before the database went into use. I contemplated a general "contacts" database where each record was identified with a ContactTypeID. But, wasn't convinced that is the way to go. I may just end up adding them to each table anyway, it just doesn't seem right that way either.

Any other suggestions would be greatly appreciated.

(Sorry about the delay in the response, we are short-handed today, so I'm putting in some warehouse time and can only check back occasionally.)

carlmack · Oct 7th 2006

Re: Normalization

I guess 2 -3 times a month is a bit much to ignore but not so bad particularly given that is transactions and you are talking materfiles.

For bigger systems one approach I have used is to split off an "Address" table where the bulk of the characters are. The various masterfiles then just contain an address code. You can make this transparent to the user and force them to setup the address first then the customer or hide it in the master addition process. I also use another table to link the customer record with the address so that you can have a many to one (A delivery address, billing address ....)

HTH
Carl

Normalization

Participate now!

Share