仅包含主键和外键的数据库表的目的是什么？ [英] What is the purpose of a database table that contains only primary and foreign keys?

问题描述

我试图了解一个简单的音乐数据库设计。有些表仅包含外键和主键。我不确定如何以及何时使用这些表格或向其中插入内容。设计如下所示：

I'm trying to understand a simple music database design. There are some tables that contain only foreign keys and a primary key. I'm not sure how and when to use these tables or what to insert into them. The design looks like this:

Track:
      id primary key
      title
      duration
      live-performance (true or false)
      year

Artist:
      id primary key
      name

ArtistTrack:
      id primary key
      artistID
      trackID

Album:
      id primary key
      title

AlbumTrack:
      id primary key
      albumID
      trackID
      track-number   

Genre:
     id primary key
     name

GenreTrack:
     id primary key
     genreID
     trackID          

例如，如果我在 Track 表中插入曲目，并在 Artist 表中插入歌手，则应该插入什么？到 ArtistTrack 表中？我假设 ArtistTrack 表中的属性的数字与各自表中的主键相同？

For example, if I insert a track into the Track table and an artist into the Artist table, what should I then insert into the ArtistTrack table? I assume the attributes in the ArtistTrack tables are numbers identical to the primary keys in their respective tables?

我有看到一些与此相似的设计，但我不明白。我知道外键将表链接在一起。有人可以给我一个有关如何使用这些表的示例吗？

I have seen several designs that are similar to this and I just don't get it. I know a foreign key links tables together. Could someone give me an example on how to use these tables?

推荐答案

ArtistTrack 表是联结表，是表示M：N关系的经典方式。如果您在 Artist 表中引用 trackId ，则意味着每个艺术家都可以拥有（最多）一首曲目。假设这不是管理一个奇迹的数据库，那将是错误的。如果您在 Track 表中引用 artistId ，则每个轨道（最多）可以由一个轨道组成艺术家。如果要允许此数据库中的协作，那也将是错误的。

The ArtistTrack table is a junction table, a classic way of representing an M:N relationship. If you put a reference to the trackId in the Artist table, it would mean that each artist can have (at most) one track. Assuming this is not a database to manage one hit wonders, that would be wrong. If you put a reference to the artistId in the Track table, each track could be composed by (at most) one artist. If you want to allow collaborations in this database, that would also be wrong.

解决方案是使用 ArtistTrack 表格，正如您所指出的，它仅引用了相关的艺术家和曲目。例如：

The solution is to have an ArtistTrack table, which, as you noted, just has references to relevant artists and tracks. E.g.:

-- Insert the track:
INSERT INTO Track VALUES (1, 'some track', 10, false, 1999);

-- Insert a couple of artists:
INSERT INTO Artist VALUES (1, 'Jay');
INSERT INTO Artist VALUES (2, 'Silent Bob');

-- Make them collaborate on this track
INSERT INTO ArtistTrack VALUES (1, 1, 1);
INSERT INTO ArtistTrack VALUES (2, 2, 1);

这篇关于仅包含主键和外键的数据库表的目的是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！