Database

[Max Kossick]

Note: they blocked SMTP here at FOSDEM so I have to use webmail instead. I am simply going to paste the e-mail I wanted to send yesterday below.

On Friday 23 February 2007, Alexandre Oliveira wrote:

<SNIP>
>
> As we don't define foreign keys (only one for the labels!), the
> current picture of the database was actually bizarre, and wouldn't
> help at all with the planning. So I just got the tables and added what
> I think the proper links should be. Max and Jeff, please check if this
> is actually what you planned.
> Note that I'm not suggesting this schema, it's just supposed to be
> what we have now with 1.4.

The last time I checked Sqlite did not actually enforce foreign keys 
constraints. But that might have changed by now.



> The idea is using this current schema to discuss what we can do as
> improvements, and a picture helps a lot IMO.

At the moment our database isn't in 3NF. Normalizing our database won't 
prevent us from updating the tags table like we do now. We simply need a new 
table for deviceid/urls and move all urls/deviceids to that table. Martin and 
I talked about handling of albums yesterday and we agreed that we should not 
simply identify albums by name anymore, A reasonable assumption is imo that 
all files in the same directory with the same album name belong to the same 
album. If you find tracks with the same album title in another directory, 
create a new row in the albums table for it. Creating a class which 
represents albums will help a lot.

> Sadly litesql doesn't seem to be ready for us, so I fear we'll end up
> either using qt stuff, that doesn't actually help much, or coding the
> support ourselves (in a better way, hopefully).


In response to Alexandre's second e-mail:

Alexandre, you are correct, deviceids are never deleted. Bart, Martin, Sven and I talked about the database schema (among other things) at dinner. More on that later today from one of use, now I need a coffee:)

Max
-- 
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser