Package DB Schema v3

[Karel Zak]

On Tue, Dec 05, 2006 at 11:32:29AM -0800, Toshio Kuratomi wrote:
> >  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

  + Takes less space for data transfer between client and DB engine
 
> [Full words]
> + Less cryptic

 cryptic? Who will be typical client for your DB?  Human or script? ;-)

> 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

 Yes. It's a good way *in case* you need modify/add status types,
 otherwise it's over engineering. (FK is nothing cheap for DB engine.)

> 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.

 Well, the question is: is it expected that there will be same DistTag
 for different branches? If not.. define distTag as UNIQUE.

> > >   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.

 Hint: never think about programmers and DB usage when you work on DB
 design. Think about data and relatioships between data only :-)

 I don't have time to study data and real relatioships for this DB, but
 1:1 is usually very strange. (and 1:0 = PK:NULL in normal table).

    Karel

-- 
 Karel Zak  <kzak at redhat.com>