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@xxxxxxxxxx>
