Package DB Schema v3

Toshio Kuratomi a.badger at gmail.com
Tue Dec 5 19:32:29 UTC 2006


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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://lists.fedoraproject.org/pipermail/infrastructure/attachments/20061205/016cade7/attachment.bin 


More information about the infrastructure mailing list