MySQL – Single Table with Multiple NULL Columns vs Multiple Tables

database-designMySQLPHPsubtypes

I've read this. But I'm not sure if I understood correctly or my situation is slightly different.

So, Here is my situation: I've a single table with customer's delivery addresses and there are two types of delivery addresses – main delivery address (every column is required) & alternative delivery address(optional).

Here's my delivery_info table:

id(PK) 
city_id(FK) 
area_id(FK)
recipient_name(NULL)
street_address(NULL)
contact_no(NULL)

My orders table:

id(PK)
delivery_info_id(FK)
alt_delivery_info_id(FK)
status
...

The city (city_id) and area (area_id) of both addresses will always be available and must be same that means the alternative delivery address must be in the same city and same area of main delivery address. But other 3 columns – recipient_name, street_address, contact_no is required for main delivery address but optional for alternative delivery address.

So my question is should i separate these two types of delivery addresses in two different tables e.g. delivery_info and a alt_delivery_info OR Keep it as it is???

Best Answer

There is no hard-and-fast rule. Here's the logic I would use in coming to a decision.

In general, it is clumsy to have an array of things (delivery addresses) spread across multiple columns.

I would pull out the entire "delivery" set of columns and make a table for them. There would be one or two rows in this new table.

Then, I would do one of these approaches:

Plan A: In the main table, have two columns:

delivery INT UNSIGNED NOT NULL,
alt_delivery INT UNSIGNED  NULL, -- note Nullable

Plan B: The delivery table would have a column with 1 or 2 in it, indicating 1=preferred. (Or some variant on such). The include that column in the JOIN.

Plan B is especially good for something like phone numbers, since there can be an arbitrarily large set of such (cell, home, work, fax, etc.) On the other hand, for delivery you may have a rather strong limit today of exactly 1 or 2 addresses.

In both cases, it is probably not wise to do any kind of de-dupping of locations or phones. So what if there is an occasional dup; after all, cohabitation is common.

In a slightly different use case, a billing UI might ask the user "Is the shipping address the same as billing address?" This is giving the user the option to tie the two together. In this case, the main table has two non-null columns bill_to and send_to, that may or may not link to the same address.

In a "location" table, it is clumsy to 'normalize' city, country, etc. And it is not justifiable for either reason for normalizing: It does not save much space, and you aren't likely to need to change the spelling of the city/country in some central location. (Czechoslovakia notwithstanging)

Related Question