• Jeff Moden (1/23/2010)


    Jeff Moden (1/23/2010)


    So, when YOU create an address table, do you have a "second address" line or do you normalize it? Enquiring minds want to know. 🙂

    I'd still like an answer to the question above, David.

    You are asking how I would implement optional parts of an address within the limitations of a fixed set of numbered address line attributes? If an address has fewer lines than there are attributes then I'd populate the extra lines with zero-length strings. Null wouldn't be appropriate because all the parts of the address are known and present and using null would give undesirable results. For example if you compare all the parts of an address in a join or subquery you ought to expect two identical addresses to be returned as equivalent, but that wouldn't work if you start adding nulls into addresses.

    I'm not much of a fan of the "AddressLine" design pattern. There are alternatives. For example the BS7666 address standard we have in the UK. Even a single attribute for the whole address (except postal code) can make more sense than having numbered lines in an address table.