Here you have hit a grey area in relational database theory.
From a certain point of view, it can be useful to have some data redundancy. From another point of view, this goes against normalisation rules.
Looking at things from the maintenance side, it is better to put everything into its own table. Keeps everything neat and tidy, and any changes need to be done only in one place. Also, the database is more flexible like this. From a performance point of view, you lose a little bit, because in this case, in order to get "meaningful" data out (ie: data that the end user can read and understand), you need to create more joins.
Look at this database design:
Code: Select all
CREATE TABLE cd_songs (
id_cd INT(5) NOT NULL,
song_position INT(2) NOT NULL,
song_name VARCHAR(50) NOT NULL,
PRIMARY KEY (id_cd, id_song));
and this one:
Code: Select all
CREATE TABLE cd_songs (
id_cd INT(5) NOT NULL,
song_position INT(2) NOT NULL,
id_song INT(5) NOT NULL,
PRIMARY KEY (id_cd, song_position));
CREATE TABLE songs (
id_song INT(5) NOT NULL AUTO_INCREMENT,
song_name VARCHAR(50) NOT NULL,
PRIMARY KEY (id_song));
According to normalisation rules, the first design is not as good as the second, because you are storing the song name in the cd_songs table, and in theory the same song can appear on different cds (therefore there could be some redundancy of data). In the second design, we are better normalised, but we need another join to get meaningful data out to the user.
Personally, in cases like this, you need to use a bit of common sense: How many times am I going to have the same song in my collection? 5 times maximum? Either way, does it really warrant another table?
In this case, I think you'd be excused for "breaking" the rules of normalisation.