Re: A quick question about domains

Hi Bruno,

> > I want to use domains in the project I'm working on right now. I'd like
to
> > hear from anyone with experience of using them. Basically I'd like to
know
> > if you think it's a good idea, and whether there are any pitfalls that I
> > should be aware of. I did a quick search on the lists and just came up
with
> > a few old bugs, apparently fixed. It's a fairly big job to get the
domains
> > in place, so I'd hate to regret it afterwards.
>
> It is going to be hard to answer this without knowing more about how you
> are planning on using them.
>
> You probably do want to be running 7.4 to use them. In 7.3 there weren't
> domain specific check constraints other than NULL and NOT NULL.

The test/development DBs are now 7.4 (or will be from today).

I did some testing and was satisfied that I could drop and re-create check
constraints without affecting existng data, it even checked that the data
fitted the new check constraints.

Principally I want to use domains to standardise data type definitions for
all master table primary keys, price, money field and things like boolean
flags, status fields and their defaults and checks.

For example:

create domain kaiin_status_cd_dom as char(1) not null default 'A' constraint
kaiin_status_check check (VALUE in ('A', 'S', 'D'));

create domain flg0_dom as char(1) not null default '0' constraint flag_check
check (VALUE in ('0', '1')) ;

I read about some (potential?) problems regarding use of db_dump and
domains, but I'm not sure of the status of these as of 7.4.

Also, this is a rework of an existing system and dates are stored as
strings. I don't like it but I'm stuck with it for now. I was hoping to be
able to provide some DB level validation of the dates as 'YYYY-MM-DD' but I
havn't come across a neat way to do it yet. I also hope that domains might
facilitate a smooth change to date type dates someday in the future (in my
dreams, i guess). In the mean time, if anyone knows a 'neat' way to validate
the date strings I'd be happy to hear about it. I got bogged down in very
much over involved (an probably slow) code that couldn't guarantee a valid
date anyway. The date conversion function to_date is far too forgiving to
fulfill this purpose, it seems.

Regards
Iain