Re: RDBMS schema (MySQL)

Hello,

Thursday, August 29, 2002, 7:51:32 PM, Ryan Fox wrote:

> On Thu, 2002-08-29 at 12:58, 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?

>>>         revisionlead varchar(255),
>>>         revisionversion varchar(255) not null,
>>>         revisionrelease varchar(255),
>>>         revisionlevel varchar(255),
>>
>> What are these fields meant for? An entry has a revision, but why 4
>> different fields titles revision-something?

> They're meant for the "# Revision:" line.  The format docs i looked at
> said that this like could have up to 4 parts, and I figured if we're
> storing them, we might as well store them separate.  Not a big issue,
> either way.

The # Revision: line just contains one integer value of 0 or higher.
You must have misread something. Perhaps you read the part for the
"Submitted via:" line and thought it was about the "Revision:"-line?

>> btw: We also need to store the name of the submitting program and it's
>> version. Perhaps we should also store when an entry was submitted
>> (important for generating update-archives (we should still think about
>> releasing the database archives).

> Ok.  I added the lines

>         submitter varchar(255),

If you mean the person: privacy issue, should not be stored. If you
mean the program, we might think about storing the program version
separately.

>> Looks like you want to implement search via the database. As database
>> searches can be pretty slow (especially for left hand wildcard
>> searches) we should IMHO rather use Yuri's method for search.

> I should have been more clear.  If everything was in rdbms, this would
> work for the server software, and the web site search.

Performance is most important - there will most likely be lots of
users querying the database at the same time. So we should test this
carefully...

> That's fine.  An empty string in MySQL is different than a NULL value,
> so even though the column is defined as not null, it'll accept empty
> strings.

/me should have known that.

>>> The discoffsets table references an id of an album, and a subid (more on
>>> that in a minute), and an offset.  There would be 1 record per album,
>>> per unique variation, per offset.  For example, if the cddb database
>>> contained to different versions of album A, there would be 1 record in
>>> the disc table (as all of that info is the same between the 2 albums),
>>> and 1 set of records in the disc offset table (1 per offset) for each
>>> version of the album.
>>
>> So far so good. But have you thought about how you want to find the
>> matching entry easily? At least for exact matches the server software
>> needs to find a certain discid - which may not be the discid of the
>> master CD. searching in the discoffsets table for that might not be
>> optimal, I think.

> Eh.  I meant to go back and explain the subid field, but it looks like I
> never did.  That's ok, I got the schema wrong anyway.  It's
> specification should have been:
>         subid char(8) not null,
> This field would be the unique discid (not to be confused with the
> album's index in the disc table) of the version.  Thus the example album
> with 2 versions would have 1 set of entries in the discoffsets table
> with an disc_id of 1, and a subid of '348d8d33'.  It's matching sister
> album would have entries in the discoffsets table with a disc_id of 1
> (same album title, etc), but a subid of 'dc33989a'.  Then you can easily
> group together albums with more than 1 disc id without data duplication.

> I'm not sure what you mean by an 'exact match', but if you give me the
> criteria you're searching on, and what you want to return, I can craft
> an sql query for this schema that will do that.

I can also give you that query string:

SELECT DISTINCT disc_id from discoffsets where subid='xxx'

But I was thinking if it wouldn't be better to have a separate table
for the assignment of discids to our new IDs.
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.

- Joerg