Database normalization for addresses

Question

I am trying to create a database for a limousine company, and I am stuck on how much normalization I should do for Addresses related to clients, drivers, branches and orders.

Basically, the addresses of partners and drivers are as follows: address_line_1, address_line_2, city, state, zip code, country

My problem comes from orders and customer 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 should only be turned on for orders).

Thus, between all four tables, I have similarities in the address fields, with the exception of two fields that differ in the table of customers and orders.

I need to note that each record will be identified with unique identifiers. Example:

Customer ID - 10,000 - 99,999

Order ID - 100,000 - Unlimited

Driver ID - a1 - a999 (possible)

Partner Id - 1,000 - 9,999

These are just examples, so don't spend a lot of time understanding them.

How many address tables should I use to create a good normalized database?

At this moment, I have three thoughts:

• One address table with all fields included, plus additional information describing the type of address (customer, order, partner, driver). This is actually not the case.

• Two address tables. One with drivers and branches, one with customers and orders. For the second table, I would have a field that will always be NULL for clients. Don't like this one either.

• Three address tables. One for drivers and branches, one for customers and one for orders. No unused fields make me think this might be a better option than the other two.

Does anyone have any advice on these three options, or perhaps even a better option?

Many thanks.

UPDATE:

Do not worry about the numbering system for the table identifier. It was just an example. I have not yet managed to figure out the best numbering system. Let's go to this as soon as I find out that the address problem is fixed.

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

For clients, I definitely need an address table, because a client can have several addresses (home, business1, business2, favorite places, etc.) that I want to save in my profile for easy access.

I forgot to mention something about the order table, which can slightly change the equation of the problem. For any order I need to have PICK-UP and DROP-OFF. But it can be an address (street address) or an airport. This means that the fields associated with the street address cannot correspond to the fields indicated at the airport. Therefore, I am sure that in order to have four objects (pu_address, pu_airpot, do_address, do_airport) inside the table (all with their specific field), I would leave unused space and programming confusion. Example: for pickups: Address_type, Address_line_1, ..., state, country, airport, airline, Flt no, ... and discard the same thing as the pickup.

Therefore, I still have a problem with the order table, for which I am not sure how to move forward. I need both addresses, as well as pick-up and drop-off points at the airport, which will be included with or without additional tables.

UPDATE Thanks again. Firstly, yes, I will keep the addresses in separate fields. The problem remains for orders. I will give an example about what type of PU and use a limousine. Address: 123 Main St, Chicago, Il, 60640; Airport: ORD, AA, 123. I need all these fields to be somehow integrated into the table.

Parameters: order table

order_id, ..., fields for receiving, which should have fields of airports and addresses, fields for drop-down fields with fields of airports and addresses.

This option still doesn't sound right.

Next, you need to have two additional tables. One of them would be for addresses (including a pickup or pickup recognition field). Another will be for the airport (with a field for pu or do too).

I also do not like this parameter, because I will need to make two requests to get information only for recording an order. First, I will receive information about the order, and after I find out the type of pick-up and drop-off (airport or address), I would make another request to get information about a specific pickup and drop-off.

So again ... what am I doing wrong? Did I miss something?

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

Answer 1

It may already be too late, but I would suggest a 1 Addresses table ( address_id , address_line_1 , address_line_2 , city , state , zipcode , country , address_type (Table FK to AddressTypes ), as this would follow 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 about designing Customers , Drivers and Affiliates tables, but without a better understanding of how they are related, it is difficult to prescribe a solution.

One option (but I don’t know if it is right for you) should have a Parties table ( party_id , party_type ) that creates a supertype / subtype relationship (one to one -or-zero in each case) with Customers , Drivers and Affiliates , all of which are Party types. I suggest reading one or two articles by David S. High on data modeling for a better understanding.

Answer 2

I really work in the address verification industry with SmartyStreets , where address processing and storage is our area of ​​expertise. In my experience, I have seen several situations like yours.

First, I deal with your segment IDs depending on the type of record. If four types of records (Clients, Drivers, Affiliates, Orders) are stored in different tables, why are ID range limits necessary? (Update: this is not the main problem actually ...)

Now a little about database design. Ideally, your design should reflect the operation of your primary domain (that is, the coordination of customers, orders, drivers, etc.), without linking them only to address data. Although addresses may be important, they are not the core business of your business. On this basis and from what I have gathered from your initial message, I immediately hesitate to keep the addresses separate from the actual record.

Although you will have the same fields in each table, they represent different business goals, and you will not risk unused unnecessary fields. Therefore, the question is not so much “how many address tables I make”, it is rather a question even about creating any tables only for addresses.

While addresses come in many forms and forms, it is important that the limousine company has the correct address information and your database normalizes. USPS (I assume you are based in the USA) confirms that some providers provide address normalization services. This is called CASS & trade; Certification Run each address through CASS & trade; service, and you're done. Addresses will look the same, have full information and be accessible as well. I suggest you start your search with LiveAddress , which will check the addresses at the entry point or the CASS list cleanup service , which will check the batch of addresses at the same time (and warn you about duplicates).

UPDATE:. In the case of several addresses that the client may have, yes, I would recommend using a separate table for this. However, you still want to standardize / verify them using CASS, so if necessary you can pull out duplicates later (plus you will find out that the addresses really exist).

So, besides this, consider storing each address in a row with the actual record that it associates (not in separate tables).

For further questions or directions, I can personally help.

UPDATE

About separation of addresses from airports: this is a potentially acceptable difference depending on the needs of your business, but remember that airports have addresses. You can add a field to your spreadsheet to save the name of the company or location indicated by the address, for example, O'Hare International Airport. This can combine multiple fields. In addition, I suggest that you save the address in separate fields by component (street, city, state, ZIP, etc.).