On Tue, 2006-12-05 at 16:14 -0600, Jeffrey C. Ollie wrote:
On Tue, 2006-12-05 at 12:35 -0800, Toshio Kuratomi wrote:
> On Tue, 2006-12-05 at 13:43 -0600, Jeffrey C. Ollie wrote:
> > On Tue, 2006-12-05 at 11:32 -0800, Toshio Kuratomi wrote:
> > >
> > > We could solve both concerns by having a foreign key constraint into a
> > > table with the valid status phrases for each table that needs statuses
> > > but that makes things more complex. This would allow us to select the
> > > list of valid statuses from a database table which is a plus. But it
> > > would also require a join for the common case of giving a human readable
> > > name for the status. So I'd like to hear your justification.
> > You could include localized versions of the status names in the database
> > table rather than doing the localization in the front-end (of which
> > there might be several).
> Okay. What do you think about doing it like this:
> create table StatusCode (
> id serial primary key,
> create table Translations (
> statusCodeId integer references StatusCode(id),
> language text not null default 'C',
> statusName text not null,
> primary key (statusCodeId, language)
Yeah, that looks like what I had in mind, except that I would call the
That would be fine.
> create view CollectionStatusCode as select id from StatusCode
where id = 1 or
> id = 3 or id = 7;
> create table Collection (
> status integer references CollectionStatusCode(id)
> Overengineered or good?
Yeah, looks good to me.
> (The view allows us to query which statuses are available for this
> table. I don't know of a sane way to do that with a check constraint.)
Why not make CollectionStatusCode a table like this:
create table CollectionStatusCode (
id integer primary key references StatusCode(id)
That should be fine. With a dataset as small as the possible status
codes I don't know that there would be much difference between the two