Re: RDBMS schema (MySQL)

Hello,

Thursday, August 29, 2002, 9:35:01 PM, Ryan Fox wrote:

> On Thu, 2002-08-29 at 14:51, Joerg Hevers wrote:
>>>         id int unique auto_increment not null primary key,
>>>         length int not null,
>>>         discid char(8) not null,
>>
>> Why would you need the discid here at all?

> I'm certainly not understanding something.  Doesn't anyone ever search
> on discid?

Yes, but if you search on discid you need to search through all the
entries, not just the "master" entries! So you need to look in the
trackoffsets table anyway - so why would you need the discid here?

>> btw: Don't forget that currently the genre category is part of our key
>> - I don't think that we can leave it out as easily as you probably
>> thought. So we might need to put the maingenre in the discoffsets
>> table as well, even though all LINKed entries _should_ be in the same
>> mein genre category, they most likely are not at the moment and we
>> might not be able to clean this mess up easily, as we cannot "move"
>> all entries to one genre because of discid collisions.

> I think my schema is ok in this aspect.  The main reason being that all
> the tables are linked to the main album via the id index in the disc
> table, not the discid.  This would allow multiple albums to have the
> same discid, and still be uniquely identified by the id index.

The discid/genre pair must be unique - also for all the variations of
a disc. Otherwise the "cddb read" command won't work anymore. Let's
say you have an entry with master discid 11111111 in genre category
"rock" and a variation with discid 12111111. You have another master
entry with discid 12111111 in genre "rock". Which entry do you return
if someone does a "cddb read rock 12111111". The variation of the
first entry as well as the second entry would match this "cddb read".
We must not change the existing protocol! Backwards-compatibility
should be our main goal!

An interesting question for the implementation might be, how we can do
the transmission of new entries from the master to the mirrors, so
that they keep in sync - but that question can most likely not be
answered in this stage.

- Joerg