Re: RDBMS schema (MySQL)

Hello,

Thursday, August 29, 2002, 11:28:25 PM, [email protected] wrote:

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

Yes, that would be better.

> I take it that subid is DiscID.

Yes.

> 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)?

Yes, you're probably right. A read would mean
to get the album_id and the var_id from the disc_variations table
(along with disc length) by querying using discid and genre and use
the ids to get the rest of the required informations from the other
three tables in order to build the database entry.

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

Right, but we need to reference the disc variation in the discoffsets
table - and I thought it would be cleaner not to repeat discid and
genre category in there for every track, but rather just use the
var_id.

- Joerg