Re: RDBMS schema (MySQL)

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?

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

Yes, I think i misread it.  My fault.


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

I meant the program.  Fixing the last problem should take care of this
as well.


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

Yep.

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

That's ok.  I tested it out just to be sure before replying.  :)


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

Ok.


> But I was thinking if it wouldn't be better to have a separate table
> for the assignment of discids to our new IDs.

I had thought of that too, but I don't think there would be any
significant performance difference between the 2 ways, even under high
load.

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

Thanks for your feedback.  I'm certain I have a lot better feel for the
data structure now then when I started.

Thanks,
Ryan