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:


My orders table:


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???

  • Both mail and alternative addresses are the instances of the same entity. Storing them in two separate tables is not considered reasonable. Moreover, there may be several alternative addresses... – Akina Mar 13 at 10:49
  • So, I'm going to the right direction? @Akina – Bipul Mar 13 at 10:58
  • In general one sparse table is correct. But there exists cases when denormalized scheme is more suitable... I cannot say does your situation is general one or an exception - you must decide it by yourself. But if you cannot prove yourself that your situation is an exclusion, then consider that it is general. – Akina Mar 13 at 11:03

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:

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.