SQL Server: many-to-many relation

So I am having a brain meltdown when trying to figure this one out. What I have is a few database's like:

tblGenre

genreID
genreName

tblArtist

artistID
artistName
genreID

tblAlbum

albumID
albumName
artistID

Everything is all well and good, and working fine. Ok, now with the question...

What I am trying to figure out is how to link an artist who has multiple names. I.e. the artist 'Prince'. I have albums from him, who I would catalog under 3 different bands; Prince (artistID=5), Prince and the new power generation (artistID=17, and "Symbol" (artistID=43)...

So, When I lookup the artist "Prince", who has a artistID of 5, how can I link the other 2 bands up to this one? Would I create a seperate database the has a comma-separated values of each artistID (i.e. 5,17,43) or is there an easier way to do this?

I don't want to go as far as the album level, of who played on every album in my database. I just want to create a link of artists who have been with multiple bands. Just a few more examples of how I would want "hooked together"...

• Tom Petty (Classic Rock)

  • Tom Petty & the Heartbreakers (Classic Rock)
  • Tom Petty & the Heartbreakers (Soundtrack)

• Prince (Funk)

  • Prince & the New Power Ge开发者_JS百科neration (Funk)

• Mark Knopfler (Classic Rock)

  • Mark Knopfler (Soundtrack)
  • Dire Straits (Classic Rock)

Thanks for the input.

You have two choices:

1) ARTISTS table and ARTISTS_ALIASES table, where artistid is a foreign key in the ARTISTS_ALIASES table, and you would have an artist_aliasid in your ALBUM table, so you'd know under what name the artist appeared on that album; or

2) a single ARTISTS using recursion (like EMPLOYEE BOSS table):

                artistid
                artistname
                primaryartistid  (references artistid in this table

When artistid and primaryartistid are the same, you treat that row as the "base" artist.

I would create a table like tblRelatedArtists which has two columns: .artistID and .relatedArtistID.

When you query the database, you could inner join the tblArtist where .artistID is in (select relatedArtistID from tblRelatedArtists as innerArtists where innerArtists.artistID = outerArtists.artistID).

Drew, it sounds like the tblArtist is more band than the main performer for an album. I see you have vb listed in the question so I also assume you have a vb front end on this app. With all these assumptions I suggest you add a varchar column to tblArtist called altArtistId. Add this column to your retun record set in your front end. Parse the comma seperated list and return to tblArtist to get any related information.