On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote:
On Thu, 29 Mar 2007, Jonas Sandman wrote: > 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 Jonas, I strongly recommend that you read a book on database design. You have a single, flat-file database that should be a multiple table, relational database (e.g., multiple titles for the same artist: what if you mis-spell an artist's name? Or that name changes?)
I guess I could split it into more tables, but would that really make anything faster? The whole key is the filename, I query for files in specific filepaths, so it doesn't matter if something is mis-spelled. Rather I encourage the possibility that it can be. Having the path as the primary key means that each database record is in
a different subdirectory, and that they're all unique. What value does the path provide?
So what should I use as a primary key then? The path? How do you handle having a title (or artist) classified in more than a
single genre? What if one of those changes, can you be sure you've made all the changes scattered throughout the database?
Since one file is one row in the database I rather liked the idea that I didn't have to scatter around updating a lot of tables. I am using the database instead of a large memory table like I used before.
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)"; Stop what you're doing and learn how to do it properly and more efficiently. Rich
