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
Attachment:
signature.asc
Description: This is a digitally signed message part
