Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Young
Starting Member
2 Posts |
 Posted - 2003-01-21 : 11:30:06
|
| I have two entities: Album and SongEach album has a set of tracks (1, 2, 3, etc.). Each track corresponds to a song.Which of the following is a better data model?Model#1:Album: AlbumNum(PK)Song: AlbumNum(PK,FK), TrackNum(PK)Model#2:Album: AlbumNum(PK)Song: SongNum(PK);AlbumNum(FK) and TrackNum are non-key attributesThanks |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-21 : 12:38:52
|
| CREATE TABLE Artists (Artist VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED)CREATE TABLE Albums (Artist VARCHAR(50) NOT NULL REFERENCES Artists(Artist), Album VARCHAR(50) NOT NULL, PRIMARY KEY CLUSTERED (Artist, Album))CREATE TABLE Songs (Artist VARCHAR(50) NOT NULL, Album VARCHAR(50) NOT NULL, Track TINYINT NOT NULL, Song VARCHAR(50) NOT NULL, FOREIGN KEY (Artist, Album) REFERENCES Albums(Artist, Album), PRIMARY KEY CLUSTERED (Artist, Album, Track), UNIQUE NONCLUSTERED (Artist, Album, Song))Of course it depends on how the business rules define such things ...can multiple real world artists have the same name? nocan albums from the same artist have the same name? most likely, not most are remixescan songs from an album have more than one track of the same track number? nocan songs from an album have more than one song name but on two different tracks? most likely, but lets force songs to have unique names per albumit all depends on the business rules and data that define these entities and the schema you use ... the normalized schema will always be the better (logical) model ... |
 |
|
|
Young
Starting Member
2 Posts |
Posted - 2003-01-21 : 18:24:08
|
| Thanks to Onamuji |
|
|
|
|
|
|
|
|
Identifying and Non-Identifying Relationships