jonas,
here is a quick stab at normailzation for you:
This is your file table. It only contains the file infomration !
CREATE TABLE Files ( id integer primary key,
, path VARCHAR(255)
, title VARCHAR(255) ?? song name?
, track INTEGER
, length INTEGER
, bitrate INTEGER
,playcount INTEGER
,changed INTEGER
, size INTEGER ?? is this really
needed, possibly dup of length?
, tagged INTEGER
, extensionVARCHAR(5)
, file_exists INTEGER)";
crate table artist (id integer primary key
, artitst_first varchar(60)
, artist_last varchar(60) ) ;
crate table genre (id integer primary key
, genre varchar (255) );
CREATE TABLE album( id integer primary key
, idFile integer
, artitstId integer
, genreId integer
, name VARCHAR(255)
, comment VARCHAR(255) );
so say you want a list of all files for an album?
select path, length, bitrate
from files f , album a,
where f.id = a.idFile
and a.name = 'xyz' ;
That should do it....
similar stuff for genres, artitst etc....
and indices where appropriate on say
file.title
album.artistid
album.name
album.genreId
artitst.last_name, artitst.first_name
the above may not be syntactically correct but you should get the idea.
Jonas Sandman <[EMAIL PROTECTED]> wrote: Hello again,
I am quite new at database and how to set them up properly so keep in mind
that the obvious might stare into your face but just point it out to me :-)
I am having this database with mediafiles (even anyone recall my previous
mails) and I create it like this:
CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate INTEGER,
playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER, extension
VARCHAR(5), file_exists INTEGER)";
and I add some indexation like this:
setup = "CREATE INDEX title Files (title, artist, album, genre, comment,
extension)";
I insert data into the database and I can query it kinda fast, but when
there is lots of files (> 15000) the database seems sluggish and doesn't
return the results at all as fast as I would expect (or like!).
Is there something I can do with this that can make it faster?
Best regards,
Jonas
