SQL - What is the best table design to store people as musicians and artists?

I am using SQL Server 2008.

I am setting up a new database to store music album information. I need to know the best way to set up my tables and need some input. Let me give you some defintions so that you can help me.

Artist: AC/DC, Led Zeppelin, Michael Jackson, Crosby, Still and Nash, Lady Gaga. This is the name of the individual or group. If it's a group, it represents a number of musicians. If it's an individual, it represents the musician's performance entity.

Musician: This is clearly an individual, a human, such as Angus Young, Michael Jackons, or Stevie Ray Vaughn.

My tables would look like this:

ARTISTS // table name
ArtistID // pk
Artist // name of artist, such as AC/DC / Michael Jackson / Lady Gaga

MUSICIANS // table name
MusicianID // pk
FirstName // first name of artist such as Angus, Michael, or Stephanie
LastName // such as Young, Jackson, or Germanotta 
Pseudonym // such as Lady Gaga

ARTISTS_MUSICIANS // junction table name
RecordID // pk
ArtistID // fk
MusicianID // fk

The confusion comes in when I think about adding people like Lady Gaga and Michael Jackson. Is there a fl开发者_JAVA百科ag that I should use to indicate that Michael Jackson is an individual as well as an entity and then add two records at the same time? Is there a way around adding two records?

Also, there are artists like Steely Dan. There are two key musicians who make up the group, Donald Fagen and Walter Becker. But they have a ton of musicians who play on each song. So, I will need to be able to associate musicians with records as well as with songs.

Specifically, what's the most efficient way to be able to see that Michael Jackson is both a performing entity as well as a musician?

I'd appreciate good ideas before I go down the wrong path and create a big spaghetti bowl of useless data.

---

For the sake of completeness, I'd keep solo artists registered as both a musician and an artist, since some artists might both solo perform and be a part of another band.

I would not add any "flag" fields to Artist to indicate that it is a solo-performer, since it might potentially change later, and unless you add a trigger or similar it might give wrong information (ie. you add a new musician to a group, but forget to change the flag -> mismatch).

To check whether a musician is a solo performer you simply do a join ala "musician <-> artist <-> musician" and if it returns more than one row it is not a solo performer.

---

I would create 5 tables: Record, Record_Artist, Artist, Artist_Musician, Musician. Musicians/Artists like Michael Jackson have a dataset in both tables: Artist and Musician. In addition Michael Jacksons musician record is also associated with the Artist "USA for Africa" for example. I would not associate any musician directly with a Record - just Artists!

---

I built a cd database years ago, not necessarily very well. I soon found that I needed a 'tracks' table - a cd is composed of tracks, musicians play on tracks, people wrote the song for a track, etc. Also, the artist of the cd is not necessarily the artist of the track - think about compilations which might be homogenous (the Steely Dan example is good as it might contain solo tracks by Fagen or Becker) or heterogenous (top hits of 2011).

With regard to the Led Zeppelin example above, the track's artist would be LZ and the track's musicians would be the four people.

---

One way to do it would be to make ARTIST recursive, with foreign keys into itself, something like this:

create table ARTIST (
    id identity not null,
    name varchar(80), 
    group_id integer,
    primary key(id),
    foreign key(group_id) references artist(id)
);

The ARTIST row for "Led Zeppelin" would have four rows ("Robert Plant", "Jimmy Page", "John Bonham", and "John Paul Jones") in ARTIST whose foreign keys would point to the "Led Zeppelin" row.

An ARTIST row with a null foreign key group_id would be an entity unto itself.