database design help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CrazyJimmy
Forum Commoner
Posts: 34
Joined: Tue Nov 19, 2002 1:40 pm

database design help

Post by CrazyJimmy »

Hello,

I am trying to design a db to store a music collection. This is a collection of bootleg titles for 1 artist and most titles have multiple discs.

I originally had the table structure as follows :-

table title
id
title
date
location
no_of_discs
label
cat_no
notes
duration

table tracks
id
title_id
track_position
name
disc_number


This structure works ok for what I want, but the one track will appear on multiple discs which means im having multiple entries of the one track in the tracks table which seems inefficient. I'd welcome any suggestions of setting up a table structure that is most efficient. I had thought of having table just for individual discs but im out my depth a bit now :)

thanks
CrazyJimmy
Forum Commoner
Posts: 34
Joined: Tue Nov 19, 2002 1:40 pm

Post by CrazyJimmy »

C'mon ppl, someone must have a clue. im desperate here :wink:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

but somehow the relationship has to be marked ;)
Is the field name in tracks the name of the cd?
If you store complete discs it might be useful to separate the disc's info from the tracks. Then it should be easy to get rid of the no_of_discs field in title, too, as count(...) can tell you.

But if the system is working and there are not too many entries, why not keep it that way ;)
CrazyJimmy
Forum Commoner
Posts: 34
Joined: Tue Nov 19, 2002 1:40 pm

Post by CrazyJimmy »

Think i will keep it as is :)

No need to complicate matters when i dont need too, should it expand or the requirements evolve I will change it then :)
Post Reply