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
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