Allow duplicate entries in table based on additional fields in table

[darkmage1]

I am trying to build an MP3 database. What I would like to do is allow duplicate entries of a song title based on the artists name. For example lets say i have a song named "Buring Down The City" by XYZ and "Buring Down The City" by ABC. I would like to allow the song name to be duplicated only if the artist name is different.

 

[willir]

ChaoticEmpire

Fun topic.

My suggestion is not to use the song title as a primary key -- sure index it and allow for duplicates. But as you know, bands will re-release songs under different albums (not sure why that is -- perhaps it has something to do with money making on popular songs ;-) and other artists may release their version of same song too.

There are several ways to do this. Here one idea to start you off.

tblGroup
GroupID - primary key
GroupName
Genre

Note that even solo artists have to belong to a group.

tblArtist
ArtistID - primary key
ArtistLN - last name
ArtistFN - first name
DOB

Discussion:
Why is artist not assigned to to a group? Could be except that sometimes an artist will move from one band to another. This suggests a many-to-many relationship. A group or band can have many band members / artists; an artist can play on different bands.

tblGroupRoaster
GroupRoasterID - primary key
GroupID - foreign key to tblGroup
ArtistID - foreign key to tblArtist
StartDate - date
EndDate - date

This table assigns an artist to a group. An artist can play for more than one group either at the same time, or at diffferent times. If there is no EndDate, then the assumption is that artist is still playing for the group/band.

tblLabel
LabelCode - primary key
LabelName

tblAlbum
AlbumID - primary key
GroupID - foreign key to tblGroup
LabelCode - foreign key to tblLabel
AlbumName
Genre
ReleaseDate
Rating

Here, relationships are used to indicate the label of the album and the group.

tblSong
SongID - Primary key
AlbumID - foreign key to tblAlbum
SongName
SongLength
Rating

Here, the song is tied to an album, and therefore indirectly to the label and group. You could tweak the table to include genre and such to add useful information to the record.

I got off-track here because I needed to ensure the relationships are defined correctly. As mentioned previously, this is just one way of accomplishing the task.

A Music database can be a much more complicated thing than one would expect. For example, for a song, you will have artists and composer(s)

Both of these are many-to-many relationships...

tblSongComposer
SongID - foreign key to tblSong
ArtistID - foreign key to tblArtist

Primary key = SongID + ArtistID

tblSongArtist
SongID - foreign key to tblSong
ArtistID - foreign key to tblArtist

Primary key = SongID + ArtistID

These two tables are identicle in structure but capture different information. The logic for capturing the composer info is obvious. But some may argue why capture the artist name for the specific songs. As stated, artists may come and go within a band. Also, a song may include a guest artist.

For some background reading on how and why I came up with the above design...

Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard