Thanks for the feedback!
On Tue, 2006-12-05 at 14:57 +0100, Karel Zak wrote:
> On Mon, Dec 04, 2006 at 09:59:11PM -0800, Toshio Kuratomi wrote:
> > create table Collection (
> > id serial primary key,
> > name text not null,
> > version text not null,
> > status text not null default 'development',
> > owner integer not null,
> > publishURLTemplate text null,
>
> From PostgreSQL docs:
>
> NULL
> The column is allowed to contain null values. This is the
> default.
> ^^^^^^
>
> This clause is only provided for compatibility with
> non-standard SQL databases. Its use is discouraged in new
> applications.
>
Okay. I'll make this implicit.
>
> > pendingURLTemplate text null,
> > summary text null,
> > description text null,
> > unique (name, version),
> > check (status = 'development' or status = 'active' or status = 'maintanence'
> > or status = 'EOL' or status = 'rejected')
> > );
>
> From my point of view the status column is odd. I think you should
> use an abbreviation or one char only.
>
> status text not null default 'D'
> check ( status IN ('D','A','M','E','R') )
>
What's the justification? Here's my reasoning:
[Single letter code]
+ Harder to misspell
+ Takes less storage space
[Full words]
+ Less cryptic
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.
> >
> > create table Branch (
> > collectionId integer not null primary key,
> > branchName varchar(32) not null,
> ^^^^^^^^^
> > distTag varchar(32) not null,
> ^^^^^^
> is it right define duplicate tags and branch names?
>
distTag and branchName are different pieces of data.
From the comments:
-- :branchName: Name of the branch in the VCS ("FC-3", "devel")
-- :distTag: DistTag used in the buildsystem (".fc3", ".fc6")
It may be unfortunate that we don't use the same names for both, but
it's the way things are.
> > parentId integer null,
> > foreign key (parentId) references Collection(id),
> > foreign key (collectionId) references Collection(id)
> > );
>
> Hmm.. here I see 1:1 model (PK=FK). Strange. (It usually means that
> you should merge the tables to one table only.) Maybe:
>
> create table Branch (
> id serial primary key,
> branchName varchar(32) not null unique,
> distTag varchar(32) not null unique,
> parentId integer references Collection(id),
> collectionId integer not null references Collection(id)
> );
>
We're actually modeling a 1:[0 1] relationship. For programmers, this
would be inheritance.
> Also, I think there should be defined some FK policy for update and
> delete. It means "ON DELETE" and "ON UPDATE" definition for the
> references.
>
You're correct. It's something I forgot to add to my list of todo items
at the bottom.
[snip lots of comments on 1:1 and succinct status fields]
If you can show that inheritance is bad or that there's a compelling
reason to make status fields a single character, I can change all the
instances.
-Toshio
Attachment:
signature.asc
Description: This is a digitally signed message part
