SQL: table design for ship/invoice address and SELECT CONCAT issue

Question

I have a simple customers table designed this way (I report only some fields, the ones regarding this qeustions):

+ ----------- + --------------- + ---------------- +
+ customer_id + invoice_address + ship_address     +
+ ----------- + --------------- + ---------------- +
+ 33          + 234, Walnut Ave + null             +
+ ----------- + --------------- + ---------------- +
+ 47          + 66, Smart Ave   + null             +
+ ----------- + --------------- + ---------------- +
+ 47          + 4, Cool Ave     + 45, Dark Street  +
+ ----------- + --------------- + ---------------- +

Rows with null ship_address means we must use customer's invoice address also for shipping.

1st question: is this a good enough design, or should all the null ship_address fields be filled with the invoice address (even if identical) and not left null.

2nd question: keeping such a design (even in case it's bad design), how do I create a SELECT query (if it's possible) that returns always one single address for each row: the ship_address when NOT null, otherwise just the invoice_address, something like:

SELECT CONCAT_IF_SHIP_ADDRESS_NOT_NULL_OTHERWISE_USE_ONLY_SHIP_ADDRESS(invoice_address, ship_address) AS address FROM customers;

Query for MySQL DB.

Thanks,

score 3 · Answer 1 · answered Dec 13 '10 at 19:24

3

i think the schema is fine. if you're using ms sql server you can use coalesce, like this:

select coalesce(ship_address,invoice_address) as address 
from customers

coalesce basically takes a list of items, and displays the first item in the list that is not null.

answered Dec 13 '10 at 19:24

nathan gonzalez

11,817
4
41
57

Your answer fits very well to my question and works, +1. I choose Joe Stefanelli answer because it works too and it's more complete and answers well also to my 1st question. – Marco Demaio Dec 16 '10 at 17:38

Joe Stefanelli · Accepted Answer · 2010-12-13T20:11:51.933

2

You have a 1-to-many relationship between customers and addresses, so I'd pull address out into a separate table with a "type" to distinguish between them. I'd consider breaking the address itself into separate attributes. Optionally, you could even add reference tables for valid cities/states/countries.

alt text

As for querying the data in this structure, assuming 1 address always exists and you create AddressType records in the desired order (i.e., Invoice=1, Shipping=2):

select c.CustomerID, a.AddressLine1, a.AddressLine2, a.AddressLine3,
       a.City, a.State, a.PostalCode, a.Country
    from Customer c
        inner join (select MIN(cax.AddressTypeID) as MinAddressTypeID
                        from CustomerAddressXref cax
                        where cax.CustomerID = c.CustomerID
                        group by cax.CustomerID) mincax
        inner join CustomerAddressXref cax
            on c.CustomerID = cax.CustomerID
                and mincax.MinAddressTypeID = cax.AddressTypeID
        inner join Address a
            on cax.AddressID = a.AddressID

edited Dec 13 '10 at 20:11

answered Dec 13 '10 at 19:44

Joe Stefanelli

132,803
19
237
235

Well these are the type of answers that makes SO really a unique place to learn. This design is great, the only side effect is that a simple SELECT on customer (with address) will always require a JOIN. But for prodcution and looking forward when the DB might get huge this is definitely the right path to go. – Marco Demaio Dec 16 '10 at 17:36
Actually Customer - Address is a an n:m relationship cause the same Address could be for more than one customers too (i.e. different employees inside the same company, I use the same company address as ship address), but from my understanding the CustomerAddressXref table can handle this too. BTW: I had already split the City, State, .. fileds, I just did not write them out in the question in order to keep it simple. – Marco Demaio Dec 16 '10 at 17:36
what happens though if the customer changes addresses? The previous invoice addresses of the customer should not change for historical purposes...what should be done then? – greaterKing Oct 05 '14 at 03:39

score 1 · Answer 3 · answered Dec 13 '10 at 19:24

1

This will do

SELECT customer_id, ISNULL(ship_address,invoice_address) AS address
FROM customers

answered Dec 13 '10 at 19:24

wizzardz

5,664
5
44
66

Oh, I see you were looking for the query in MySQL? I dint see the MySQL tag initially,I thought it was for MS SQl .Im MS SQL ISNULL function is used to set a replacement value if expression value (vaule on left side) is NULL, ie in your case if ship_address is null then it returns value of invoice_address. and In MySQL ISNULL function is for NULL value checking , right? – wizzardz Dec 17 '10 at 08:31

score 1 · Answer 4 · edited Dec 30 '10 at 15:23

1

You should leave the ship address null as copying invoice address will lead to redundancy.

As for the query:

SELECT invoice_address
from table 
where ship_address IS Null

UNION

SELECT ship_address 
from table 
where ship_address IS NOT NULL

edited Dec 30 '10 at 15:23

Bill the Lizard

398,270
210
566
880

answered Dec 13 '10 at 19:29

Programmer

6,565
25
78
125

exactly that is what 'ISNULL' does. – wizzardz Dec 13 '10 at 19:36

SQL: table design for ship/invoice address and SELECT CONCAT issue

4 Answers4