• long_shanks64 (1/23/2013)


    The 'alternative' is to model all known attributes correctly in the first place using declarative referential integrity and constraints.

    Sometimes it's just not possible.

    Classic example is recording addresses.

    Here in NZ we have frequently deal with suppliers/distributors which do not separate Australian and NZ markets.

    Therefore, if I want to register on such site I need enter my address into the fields matching AUS address definitions.

    In Australia it's mandatory to fill "State" field, and in NZ we do not have neither states, nor provinces, only separation by North Island and South Island, which Australians do not understand. So, I have to play "guessing game" every time filling such form - what should I put into State field marked with a red star (mandatory) to match their expectations.

    In proper design the field should be not-nullable with limited set of allowed values for AUS addresses and NULL for all NZ addresses.

    Obviously, you cannot implement it with a "normal" design, as the number of fields is dynamic, and constraints are different from case to case.

    You can choose, of course, to go with separate tables for each coutry, but then you need to run search requests against multiple tables.

    And when you pull an address for a customer you need to use either dynamic SQL or include 2 or more tables into your SQL requests.

    To me it looks much more ugly than any "in code" check constraint implemented in "saving address" procedures.

    _____________
    Code for TallyGenerator