Re: Almost relational PostgreSQL (was: one-to-one)

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