Databases
StackOverflow2013
Postsdbo
POSQL - What is the best table design to store people as musicians and artists?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL - What is the best table design to store people as musicians and artists?
    text
    copied!<p>I am using SQL Server 2008.</p> <p>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.</p> <p>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.</p> <p>Musician: This is clearly an individual, a human, such as Angus Young, Michael Jackons, or Stevie Ray Vaughn.</p> <p>My tables would look like this:</p> <pre><code>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 </code></pre> <p>The confusion comes in when I think about adding people like Lady Gaga and Michael Jackson. Is there a flag 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? </p> <p>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.</p> <p>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?</p> <p>I'd appreciate good ideas before I go down the wrong path and create a big spaghetti bowl of useless data.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload