Database normalization for Addresses

Question

I am trying to build a database for a limousine company and I got stuck on how much Normalization should I do for Addresses related to Customers, Drivers, Affiliates and Orders.

Basically the Affiliate and Driver addresses look like this: address_line_1, address_line_2, city, state, zipcode, country

My problem comes from orders and customers addresses. They should look like this: address_line_1, address_line_2, city, state, zipcode, country, address_type_1 (home, business), address_type_2 (pick-up, drop-off - this only needs to be included for orders).

So between all the four tables I have similarities in address fields except for two fields which differ in customer and orders table.

I need to mention that every record will be identified with unique ids. Example:

Customer ID - 10,000 - 99,999

Order ID - 100,000 - no limit

Driver ID - a1 - a999 (maybe)

Affiliate ID - 1,000 - 9,999

These are just examples so don't spend to much time trying to understand them.

How many Addresses table should I use to create an good normalized database?

In this moment I have three ideas in my mind:

1. One Addresses table with all the fields included plus an extra one describing the type of address (customer, order, affiliate, driver). Not really like this one.

2. Two Addresses tables. One with drivers and affiliates, and one with customers and orders. For the second table I would have and field that will always be NULL for customers. Don't like this one too.

3. Three Addresses tables. One for drivers and affiliates, one for customers, and one for orders. No unused fields leads me to think that this could be an better option than the other two.

Does anyone has an advice regarding these three options or maybe even a better option?

Thanks a lot.

UPDATE:

Don't bother yet about the numbering system for the tables ID. That was just an example. I still didn't had time to figure out the best numbering system. Will get to that once I got my addresses problem sorted out.

From Matt's answer I am tempted to leave the driver and affiliate tables with the addresses included and just sort out somehow the customer and order tables.

For customers, I would definitely need an Addresses table because a customer can have multiple addresses (home, business1, business2, favorite places, etc) that I want to have stored in their profile for easier access.

I forgot to mention something about the orders table which may change a little bit the equation of the problem. For any order I would need to have a PICK-UP and DROP-OFF location. But this can be either an address (street address) or an airport. This means that the fields related to a street address cannot match the airport specific fields. So I am pretty sure that to have four entities (pu_address, pu_airpot, do_address, do_airport) inside a table (all with their specific field) would leave me to unused space and with a programming mess. Ex: for pick-up fields: Address_type, Address_line_1, ..., state, country, Airport, Airline, Flt no, ... and for drop off same thing as pick-up.

So I still have a problem with the Order table for which I am not sure on how to move forward. I would need both addresses and airport pick-up and drop-off locations to be included with or without the use of extra tables.

UPDATE Thanks again Matt. First, yes I will store addresses in separate fields. The problem still remains for orders. I will give an example on what type of pu and do a limo service use. Address: 123 Main St, Chicago, Il, 60640; Airport: ORD, AA, 123. I need to have all those fields somehow integrated into the table.

Options: Order table

order_id, ..., pick-up fields which need to have both airports and addresses fields, drop-off fields with both airport and address fields.

This option still doesn't sound right.

Next would be to have two extra tables. One would be for addresses (including a field for recognizing pick-up or drop-off). The other one would be for airport (with a field for pu or do as well).

I don't like this option as well because I will need to do two queries in order to retrieve the information for only an order record. First I will retrieve the order information, and after I know the type of pick-up and drop-off (airport or address), I would do another query to retrieve the specific pick-up and drop-off information.

So, again... what am I doing wrong? Do I miss something?

And yes, I will definitely use some verification system to make sure that the addresses would be correct.

Answer 1

It's probably too late now, but I would suggest 1 Addresses table (address_id, address_line_1, address_line_2, city, state, zipcode, country, address_type (FK to AddressTypes table)) as this would follow the standard normalization rules. Your Orders table will have two Foreign Key relationships with the Addresses table - pickup_address_id and delivery_address_id. I have questions around the design of the Customers, Drivers and Affiliates tables, but without a better understanding of exactly how they relate it is difficult to prescribe a solution.

One option (but I don't know if it is the right one for you) would be to have a Parties table (party_id, party_type) which creates a supertype / subtype relationship (one to one-or-zero in each case) with Customers, Drivers and Affiliates, all of which are types of Party. I suggest reading one or two of David C. Hay's articles on data modelling for a better understanding.