RE: RDBMS schema (MySQL)

> Sorry, I don't understand - why would we need that here? We 
> can reference all the information via the id field. All 
> information that can change between variations should not be 
> stored in the disc table (i.e. maingenre, length, discid).

I see, you've taken care of it in your discvariations table.  I justed
wanted to be sure I could lookup all the offset variations given a disc
(master/album/whatever).  For the reasons I mentioned in the 'searching'
E-mails.
 
> Suggestion (based on Ryan's scheme):
> 
> create table disc (
>         id int unique auto_increment not null,
>         revision int not null,
>         program varchar(255) not null,
>         programversion varchar(255),
>         submitteddate date,
>         artist varchar(255) not null,
>         title varchar(255) not null,
>         releaseyear decimal(4),
>         genre varchar(255) not null,
>         numtitles int not null,
>         extendeddata text,
>         fulltext key(artist), fulltext key(title), fulltext 
> (artist,title), key(maingenre), key(releaseyear) );
> 
> create table discvariations (
>         var_id int unique auto_increment not null,
>         disc_id int not null, (--> see id in table disc)
>         subid int not null,

Instead of 'disc_id' could we call it 'ablum_id' or something, or else
it'll get confused with the existing DiscID field.

I take it that subid is DiscID.  I'm missing something: how is the
freedb read implemented?  Doesn't this table (discvariations) need to be
keyed on genre+subid (as well as disc_id)?  And then doesn't var_id
become redundent?  var_id is better then genre+subid because it is
shorter and less cumbersome, but we'll always need genre+subid.

Tom.

>         maingenre char(16) not null, (we can use a number 
> here as well)
>         length int not null, (length can vary between the variations)
>         key(disc_id), key(subid)
> );
> 
> create table discoffsets (
>         var_id int not null, (--> see var_id in table variations)
>         number int not null, (we need the track number the offset
>                               belongs to)
>         offset int not null,
>         key(var_id), key(number)
> );
> 
> create table disctracks (
>         disc_id int not null,
>         number int not null,
>         name varchar(255) not null,
>         artist varchar(255) not null,
>         extendeddata text,
>         key(disc_id), key(name)
> );
> 
> This way we don't even have to define a "master-entry". Disc 
> information and trackoffsets etc. are clearly separated.
> 
> - Joerg
> 
> _______________________________________________
> fdb-dev mailing list
> [email protected]
> http://dtype.org/mailman/listinfo/fdb-dev
>