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
> table "StatusCodeTranslations."
>
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
implementations.
-Toshio