SQL - 什么是最好的表设计，以存储人作为音乐家和艺术家？ [英] SQL - What is the best table design to store people as musicians and artists?

问题描述

我使用的是SQL Server 2008.

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.

艺术家：AC / DC，Led Zeppelin，Michael Jackson，Crosby，Still和Nash，Lady Gaga。这是个人或组的名称。如果它是一个组，它代表了一些音乐家。如果是个人，则代表音乐家的表演实体。

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.

我的表格如下：

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

当我想添加Lady Gaga和Michael Jackson这样的人时，会产生混乱。有没有一个标志，我应该用来表示迈克尔·杰克逊是一个个人和一个实体，然后同时添加两个记录？有没有办法添加两个记录？

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?

此外，还有像Steely Dan这样的艺术家。有两个主要音乐家组成的组，唐纳德Fagen和沃尔特贝克。但他们有大量的音乐家谁在每首歌。所以，我需要能够将音乐家与唱片以及歌曲联系起来。

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?

我会感谢好主意，我走下错误的道路，并创建一个大意大利面碗无用的数据。 p>

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).

要检查音乐家是否是独奏表演者，只需要加入alamusician< - artist< - musician，如果返回多行，则不是独奏表演者。

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.

这篇关于SQL - 什么是最好的表设计，以存储人作为音乐家和艺术家？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！