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