Normalization for music/concerts database

Question

My understanding with normalization after reading my book and going through a few youtube tutorials is that one of the important things is to not have repeating values. More specifically the primary key (ID) should not repeat.

So if I am working with some Tables in a Music/Concert database, then the following would be bad:

**CREATE TABLE Artists**                                                     

ArtistID INT *PRIMARY KEY*
ArtistName  VARCHAR(30)                        
Albums      

• ^Having Albums in the Artists table would be bad as there is One Artist for Many Albums. Therefore the ArtistID would have to show up in multiple rows [once for each album by the artist]

Question: Should a table like this have a foreign key that ties to another Table? The related table I would think about is obviously Albums.

Albums would have columns like:

CREATE Table Albums (
       Album_ID INT NOT NULL AUTO_INCREMENT,
       Album_Name VARCHAR(30), 
       Artist VARCHAR(30),
       Release_Date DATETIME,
       Genre or GenreID,
       Primary Key (Album_ID) );

Question: But albums have songs. However I can't have Album ID as the Primary Key and then have all the songs with the repeating Primary Key of Album ID for each can I? Should I therefore keep the attribute 'Songs' out of the Album table?

Answer 1

Question: Should a table like this have a foreign key that ties to another Table? The related table I would think about is obviously Albums.

If the relationship between artist and album is one to many... (if the rules are that an "artist" can have zero, one or more "albums", and an "album" belongs to exactly one "artist")

We would implement that relationship by storing artist_id as a foreign key column on the album table.

Something like this:

artists:

artist
------
id           'PK'
name varchar

albums:

album
-----
id           'PK'
artist_id    'FK ref artist.id'
title
year
artwork

By storing a value in the artist_id column of album, that is a reference to a row in the artist table.

artist:

 id    name
 ----  --------
    1  Kansas
    2  Styx

album:

 id    artist_id  name                  year
 ----  ---------  --------------------  ----
  432          1  Leftoverture          1976
  435          1  Point of Know Return  1977
  438          1  Monolith              1979
  561          2  Grand Illusion        1977

Question: But albums have songs. ... Should I therefore keep the attribute 'Songs' out of the Album table?

Yes. If it's a one to many relationship between album and song, then store the album_id on the song table.

Like this:

songs:

song
----
id           'PK'
album_id     'FK ref album.id'
song_title
lyrics

song

id    album_id  song_title         
----  --------  ------------------------
6777       435  Dust In The Wind
6801       438  People of the South Wind
5555       561  Come Sail Away 

From a row in the song table, we can use the value stored in the album_id column to find the related album.

From a row in the album table, we can use the value stored in the artist_id column to find the related row in artist.

---

These examples are based on the simplistic one-to-many relationships that are described.

It's important that we get these rules for the relationships between the entities into a reasonable representation. We need to know if a relationship is one-to-many or many-to-many.