Shared Many to Many Relationship

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Shared Many to Many Relationship

Author

Topic

Danio
Starting Member

2 Posts

Posted - 2009-03-07 : 09:37:44

Hello all!

I have a question i hope you SQL geniuses could she a little light on for me.

I have 3 Entities : Track, Album and Artist. Track and Album both have many to many relationships with Artist.

Should I have one join table for both Track and Album to Artist ?
EX fields : ID,ItemID, ItemType, ArtistID

Where ItemType is a byte value 0=track, 1=album and ItemID is the Primary key ID of Album or Track.

Or should Track and Album have their own seperate join tables to Artist EX: ID, TrackID, ArtistID 2nd join table : ID, AlbumID, ArtistID

Hope i wasn't too confusing,
Thanks!!

Daniel

Which one would make more sense and have better performance running queries with many records?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-07 : 16:12:30

To Comply with 3rd Normalization I would create like this:

1) Track
TrackID PK
TrackName 
AlbumID FK

2) Album
AlbumID PK
Albumname 

3) Artist
ArtistID PK
Artistname

4)TrackArtist
TrackID PK,FK
ArtistID PK,FK

5) AlbumArtist
AlbumID PK,FK
ArtistID PK,FK

Danio
Starting Member

2 Posts

Posted - 2009-03-07 : 16:41:54

thanks for the response.

I know what normalization means. But what do you mean by 3rd normalization?

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-07 : 16:53:34

Means there is no repeating Multi-valued columns,No-partially dependent column on PK and are non-transitively dependent on PK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources