Обсуждение: Re: Almost relational PostgreSQL (was: one-to-one)

Josh Berkus wrote:
> My personal limit of denormalization stops at a few NULL columns and using
> cache tables to hold copies of views which are too slow.

Here's a trivial design problem involving NULLs (the only conscious
violation of Pascal I did in that database): I have a "gaddresses" table
that holds addresses of geographical points (such as your house):

    id       (PK and FK: specifies the geographical point of which we are giving the address)
    country  (FK)
    state    (FK, nullable: specifies the state of the US if the country is US)
    address  (the rest of the address)

Pascal says: use NULL only for missing, not for inapplicable. Here the
state is inapplicable unless the country is US.

What should I do instead? Create another table, "gstates"?

    id       (PK and FK to gaddresses)
    state    (FK)

Is this overkill?

Antonios,

> Pascal says: use NULL only for missing, not for inapplicable. Here the
> state is inapplicable unless the country is US.
>
> What should I do instead? Create another table, "gstates"?
>
>     id       (PK and FK to gaddresses)
>     state    (FK)
>
> Is this overkill?

Yes.  I'd say that null is an example of "acceptable denormalization".
Alternately, instead of using NULLs, I would suggest using a zero-length
string or "XX" to indicate non-applicability -- this would give you a clearer
indication, and allow you to avoid messy CASE WHEN IS NULL and COALESCE
queires.  The zero-length string is particularly attractive as it lends
itself to easy concatination of addresses.

Regardless of which approach you take, you want to make sure that it is *only*
used for non-US addresses.   So you will want to add a table constraint
enforcing the state code for US addresses.

Also, IME, many foriegn addresses have a region or province attached to them.
For databases including international addresses, I frequently have a generic
"province" field which covers both US states and foriegn regions, and enforce
consistency by using a reference list which includes both countries and
provinces/states.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco