CODEWITHSUNDEEP
database-designforeign-keysrelational-database
Gramatik
Gramatik

Reputation: 133

SQL Create: How handle multiple attributes of same type for given key

I'm trying my hand at creating a relational database from scratch. My question is how to handle multiple of the same type of attribute.

I have a fact table that is as follows

____________________________________________
|Compound Key        |Album         |Artist |
|Jay-ZThe Blueprint  |The Blueprint |Jay-Z  |

And so on. I have a lookup table for what label an album was released under, but most albums are released under multiple labels. Is the correct way to do this:

____________________________________________
|Compound Key        |Label1      |Label2  |
|Jay-ZThe Blueprint  |Roc-A-Fella |Def-Jam |

Or like this?

__________________________________
|Compound Key        |Label      |
|Jay-ZThe Blueprint  |Roc-A-Fella|
|Jay-ZThe Blueprint  |Def-Jam    |

Upvotes: 0

Views: 604

Answers (1)

dfundako
dfundako

Reputation: 8314

I would suggest an artist table, a label table, an album table, and a label/album xref table. See below: 

CREATE TABLE artist (
artistID INT PRIMARY KEY, 
ArtistName VARCHAR(100)
) 

CREATE TABLE album (
albumID INT PRIMARY KEY, 
AlbumName VARCHAR(100), 
ArtistID INT

CONSTRAINT FK_ArtistID FOREIGN KEY (ArtistID) REFERENCES artist(artistID)
)

CREATE TABLE Label (
LabelID INT PRIMARY KEY, 
LabelName VARCHAR(100)
)

CREATE TABLE AlbumLabelXref (
AlbumLabelID INT PRIMARY KEY, 
AlbumID INT, 
LabelID INT

CONSTRAINT FK_LabelID FOREIGN KEY (LabelID) REFERENCES Label(LabelID),
CONSTRAINT FK_AlbumID FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)
)

INSERT INTO artist
VALUES 
(1, 'Michael Jackson')

INSERT INTO album 
VALUES 
(1, 'Thriller', 1)

INSERT INTO Label 
VALUES 
(1, 'Epic')

INSERT INTO AlbumLabelXref
VALUES 
(1,1,1)



SELECT 
ar.ArtistName, 
ab.AlbumName, 
l.LabelName


FROM AlbumLabelXref alx

INNER JOIN Album ab
    ON ab.albumID = alx.AlbumID

INNER JOIN Label l
    ON l.LabelID = alx.LabelID

INNER JOIN artist ar
    ON ar.artistID = ab.ArtistID

Upvotes: 1

Related Questions