Re: RDBMS schema (MySQL)

On Thu, 2002-08-29 at 12:58, Joerg Hevers wrote:

> > create table disc (
> >         id int unique auto_increment not null,
> >         length int not null, 
> >         discid char(8) not null unique primary key,
> 
> You cannot make the discid a unique key - currently the
> discid/genre category pair is unique, which is certainly not optimal.
> As there are lots of collisions we would loose too many entries by
> using that solution - this is simply not feasible. You would need a
> new discid for that!

Good to know.  Actually looking my schema back over, I realize that I
made some mistakes.  Sounds like the above lines should be

        id int unique auto_increment not null primary key,
        length int not null,
        discid char(8) not null,




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

> 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),
        submitteddate date,


> We don't need to store anything here - the PLAYORDER= line is always
> empty.

Ok.  Removed.

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

With MySQL's fulltext, text searches like this are very quick. 
(Actually, I'm not sure that MySQL's fulltext supports left hand
wildcards).  I wrote a search engine, using mysql as a backend, and
fulltext indexes on the page title and description.  Searching a couple
million rows happened in less than a second on a low cost intel
machine.  Creating the indexes takes a few days, but that only needs to
happen after the initial import.

Check out
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search for some more info.

> At least currently track names can have a zero length. This is useful
> e.g. for "fake tracks" like on CDs, which have a bonus track on track
> 99. 

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.

> We should also have a separate field for track artist name - e.g
> for samplers.

Ok.  A added an artist field in the tracknames table.


> For the production system we shouldn't make changes too often - as all
> the mirrors have to do them and we need to verify that the changes
> were done properly etc.
> Question is, if we really need this separate tables, if we use the
> method proposed by Yuri for searching.

I believe Yuri and I's methods are similar, except as he is suggesting
the general idea of using indexes, I am suggesting the specific idea of
MySQL's fulltext indexing.  If I'm wrong, please correct me

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


> Well, this were my first thoughts. Now you can flame me or whatever ;)

They were great.  Thank you!
Ryan