sublimer — Thu, 16 Aug 2007 06:02:40 +0000

so then how do i track how many times a file has been downloaded? do i make a new table that stores the download_id along with download_count? why would it be so bad just to leave download_count with the download table?

JeevesBond — Thu, 09 Aug 2007 19:34:00 +0000

sublimer wrote: download_count is a measure of how many times the file has been downloaded, it's not an ID.

Yes I realised that.

It's superfluous though, the download table is describing two things: downloads and files, which is incorrect.

sublimer — Thu, 09 Aug 2007 15:16:02 +0000

Thank you very much JeevesBond. download_count is a measure of how many times the file has been downloaded, it's not an ID.

brady.k — Tue, 07 Aug 2007 20:31:22 +0000

Sublimer: About the genres... yes.

JeevesBond — Tue, 07 Aug 2007 20:15:57 +0000

That diagram violates first and second normal forms. artist violates the first and downloads the second. Something like the attached might be more in order.

To be in first normal form all values must be atomic. Meaning no repeating groups like artist_genre1, artist_genre2, artist_genre3. So in the diagram I've split the table to form a many-to-many relationship, think of it like this: one genre has many many artists, whilst one artist has many genres. You could constrain it to one genre by removing the linker artist_genre table.

To be in second normal form all values must be dependent on the primary key. This means a table must describe one thing and one thing only: in the downloads table you're lumping information about available files in with the downloads, this means attributes like filename, filesize etc. are not dependent upon the primary key (download_id). A good clue to this is the attribute: download_count, if there's to be one row for each download how are you to include a download_count? How does this describe the download?

download_count is actually superfluous, since the information can be found by aggregating download and file information. Something like: [incode]SELECT COUNT(*) FROM download INNER JOIN file ON download.file_id=file.file_id WHERE file_name='test.mp3'[/incode].

*** EDIT ***
I had no idea what boot is so have guessed where it should go.

sublimer — Mon, 06 Aug 2007 13:31:59 +0000

So, you lets say I have a good 30 different genres, just make a table with the current artist table info, drop artist_genre1, 2 and 3, and then add 30 different columns with the names of the genres?

I will be using PHP to make my queries. The "this boot contains 15 tracks" was just something I used as a filler, it could be anything. I intend it to be a paragraph or so about the album. It would include things like where it was recorded, special guests on the album, like a bio of the album.

Once I get the table set up, I will create some indexes and all too. I really want to get this database done right.

brady.k — Mon, 06 Aug 2007 06:55:12 +0000

At a glance, it looks pretty good. You have the general right idea... id's and primary key's are your friend. They let you easily cross reference between tables.

As for your redundancy question... something I would do would be to possibly not have "artist_genre1", "artist_genre2", etc... but rather have a field for EACH genre.

So an artist entry would have the fields (in addition to what you have already):

rb
rock
metal
jazz
classical
rap
hip-hop
funk
spoken
...etc

What would you do with that? Those would all be booleans, basically: an int(1) type. 1 = genre is true for that artist, 0 = not.

So... for someone like... Metallica, you might have

metal = 1
rock = 1
neoclassical = 1
heavy = 1
jazz = 0
funk = 0
... etc

This allows you to easily query the database...

Looking for metal artists?

"SELECT * FROM artists WHERE metal = 1"

Looking for an artist that is funk, jazz, and metal combined?

'SELECT * FROM artists WHERE metal = 1, jazz = 1, funk = 1"

Does that help? I think I was pretty clear.

Are you going to be using PHP to run the queries? Or something else?

A suggestion I would have would be to absolutely decide on a db/table structure before implementing anything... I've learned that the hard way.

A final suggestion would be to keep the entries to the bare minimum possible... so instead of having "boot_info" say "this boot contains 15 tracks"... create a "boot_tracks" field and then just insert "15"... and use whatever language you're in to display the text differently. This would be across the board... keep the data either simple numbers, or simple phrases (besides things like names and titles), it will make your life easier.