> 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
> fdb-dev@xxxxxxxxxx
> http://dtype.org/mailman/listinfo/fdb-dev
>
|
