SQL Server：多对多关系
所以当我试图解决这个问题时，我的大脑崩溃了。我拥有的是一些数据库，例如：
tblGenre
genreID
genreName
tblArtist
artistID
artistName
genreID
tblAlbum
albumID
albumName
artistID
一切都很好，工作正常。好吧，现在的问题是……
我想弄清楚如何链接一个有多个名字的艺术家。即艺术家“王子”。我有他的专辑，我将他分为 3 个不同的乐队； Prince（artistID=5）、Prince和新势力（artistID=17）和“Symbol”（artistID=43）……
那么，当我查找artistID为5的艺术家“Prince”时，如何我将其他 2 个乐队链接到这个乐队吗？我会创建一个单独的数据库，其中每个艺术家 ID 都有逗号分隔的值（即 5,17,43），还是有一种
我不想要 的更简单的方法？就专辑级别而言，我只想创建一个与多个乐队合作过的艺术家的链接，这只是我希望如何“联系在一起”的几个例子。 ..
汤姆·佩蒂（经典摇滚）
- 汤姆·佩蒂和汤姆·佩蒂伤心者乐队（经典摇滚）
- 汤姆·佩蒂和汤姆·佩蒂伤心者（原声带）
Prince (Funk)
- 王子与新发电（Funk）
Mark Knopfler (经典摇滚)
- 马克·诺夫勒（原声带）
- 恐怖海峡（经典摇滚）
感谢您的投入。
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 Generation (Funk)
Mark Knopfler (Classic Rock)
- Mark Knopfler (Soundtrack)
- Dire Straits (Classic Rock)
Thanks for the input.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你有两个选择：
1）ARTISTS表和ARTISTS_ALIASES表，其中artistid是ARTISTS_ALIASES表中的外键，并且你的ALBUM表中有一个artist_aliasid，这样你就知道艺术家出现在该专辑中的名字；或
2) 使用递归的单个艺术家（如 EMPLOYEE BOSS 表）：
当artistid 和primaryartistid 相同时，您将该行视为“基础”艺术家。
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):
When artistid and primaryartistid are the same, you treat that row as the "base" artist.
我将创建一个像 tblRelatedArtists 这样的表，它有两列：.artistID 和 .latedArtistID。
当您查询数据库时，您可以内部加入tblArtist
其中 .artistID 位于（从 tblRelatedArtists 中选择 relatedArtistID 作为innerArtists，其中innerArtists.artistID = externalArtists.artistID）。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，听起来 tblArtist 更像是乐队，而不是专辑的主要表演者。我看到您在问题中列出了 vb，所以我还假设您在此应用程序上有一个 vb 前端。考虑到所有这些假设，我建议您向 tblArtist 添加一个名为 altArtistId 的 varchar 列。将此列添加到前端的返回记录集中。解析逗号分隔的列表并返回 tblArtist 以获取任何相关信息。
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.