I should add that I am scanning a folder-tree for the files and are storing the metadata in the database like that so I don't have to read the actual files. That's why I used the path as primary key to begin with. No duplicate entries.
On 3/29/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:
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
