表间关系设计建议 [英] Advice on design relations between tables

问题描述

我有关于音乐专辑的信息，我想在 RDBMS 表中组织它们之间的关系.我有每张专辑的以下信息:艺术家、专辑名称、年份、标签、流派、风格、评级.到目前为止，我想制作 4 个表格 - 艺术家、专辑(名称、年份、标签、评级)、流派 1 和流派 2(每种流派都有其风格).上图如下:

I have information about music albums that I want to organise in RDBMS tables with relations between them. I have the following info for each album: artist, album name, year, label, genre, style, rating. So far I think to make 4 tables - artists, albums (name, year, label, rating), genre1 and genre2 (each genre with its styles). On the diagram it looks as follows:

但还不知道如何在相册和其他三个表之间建立连接?即，当我将运行查询从艺术家中选择名称时，我希望收到一张带有相应艺术家和流派风格的专辑.

But don't know yet how can I establish a connection between albums and the other three tables? I.e., when I will run a query select name from artists I would like to receive an album with corresponding artist and genre-style.

在这种情况下我应该如何建立表之间的关系?

How should I establish a relation between the tables in this case?

推荐答案

需要阅读关系型数据库表的介绍&查询.

You need to read an introduction to relational database tables & querying.

您所说的表之间的关系"[原文如此]是 FK(外键).FK 表示表中列列表的值显示为表中其他一些列列表的值，这些列在此处形成 PK(主键)或 UNIQUE 集.您无需声明或使用 FK 即可查询.像所有约束一样，包括 PK &UNIQUE，它们用于 DBMS 排除错误的数据库状态.

The "relations" [sic] between tables that you are talking about are FKs (foreign keys). A FK says values for a list of columns in a table appear as values for some other list of columns in a table that form a PK (primary key) or UNIQUE set there. You don't need to declare or use FKs to query. Like all constraints, including PK & UNIQUE, they are for the DBMS to exclude erroneous database states.

一个(基础或查询结果)表代表一个(业务/应用)关系(船)/关联.一个表包含从关联的谓词(语句模板)生成一些真正的proposition(语句)的行.基表的谓词由 DBA 给出.查询结果表的谓词来自基表、关系运算符和用户查询表达式中的逻辑运算符.即 JOIN 的谓词是其表的谓词的 AND；联合手术室；除了 AND NOT；条件的 ON 和 WHERE 以及带有 JOIN 谓词的条件；等.

A (base or query result) table represents a (business/application) relation(ship)/association. A table holds the rows that make some true proposition (statement) from an associated predicate (statement template). The predicate of a base table is given by the DBA. The predicate of a query result table follows from the base tables, relation operators & logic operators in the user's query expression. Ie the predicate of a JOIN is the AND of its tables' predicates; UNION the OR; EXCEPT the AND NOT; ON and WHERE of a condition AND that condition in with the JOIN predicate; etc.

-- artist A has name N
Artist(A, N)
-- album A has name N and ...
Album(A, N, ...)
-- genre G has name N
Genre(G, N)
-- artist A authored album A2
ArtistAlbum(A, A2)
-- album A is of genre G
AlbumGenre(A, G)

SELECT DISTINCT ...
FROM
    --     album ag.A is of genre ag.G AND genre g.G has name g.N ...
    -- AND ag.G = g.G ...
    AlbumGenre ag JOIN Genre g ON a.G = g.G ...

注意无关紧要一个专辑可以有多少个流派，一个流派可以有多少个专辑，或者一个流派是否可以有多个 id 和/或名称，查询仍然返回行满足那个谓词.查询或更新不需要约束(包括 FK).

Notice that it does not matter how many genres an album can have or how many albums a genre can have or whether a genre can have multiple ids and/or names, the query still returns the rows that satisfy that predicate. Constraints (including FKs) are not needed to query or update.

请注意，我们可以应用相同的谓词转换以及其他转换来编写约束.(我对作者和专辑都使用了 A，所以我必须在这里给出一个重命名示例.)

Notice that we can apply the same predicate transforms plus others to write constraints. (I used A for both authors & albums so I'd have to give a renaming example here.)

-- for all A & A2, if artist A authored album A2 then artist A has some name
-- for all A & A2, if artist A authored album A2 then for some N, artist A has name N
-- for all A & A2, if (A, A2) in ArtistAlbum then for some N, Artist(A, N)
-- SELECT A FROM ArtistAlbum ⊆ SELECT A FROM Artist
FOREIGN KEY ArtistAlbum (A) REFERENCES Artist (A)

-- for all A & A2, if artist A authored album A2 then album A2 has some name
-- for all A & A2, if artist A authored album A2 then for some N, ..., album A2 has name N and ...
-- for all A & A2, if (A, A2) in ArtistAlbum then for some N, ..., (A2, N, ...) in Album
-- SELECT A2 FROM ArtistAlbum ⊆ SELECT A AS A2 FROM Album
FOREIGN KEY ArtistAlbum (A2) REFERENCES Album (A)

-- for all A & G, if album A is of genre G then album A has some name and ...
-- for all A & G, if album A is of genre G then for some N, ..., album A has name N and ...
-- for all A & G, if (A, G) in AlbumGenre then for some N, ..., (A, N, ...) in Album
-- SELECT G FROM AlbumGenre ⊆ SELECT A FROM Album
FOREIGN KEY AlbumGenre (A) REFERENCES Album (A)

-- for all A & G, if album A is of genre G then genre G has some name
-- for all A & G, if album A is of genre G then for some N, genre G has name N
-- for all A & G, if (A, G) in AlbumGenre then for some N, (G, N) in Genre
-- SELECT G FROM ArtistAlbum ⊆ SELECT G FROM Genre
FOREIGN KEY AlbumGenre (G) REFERENCES Genre (G)

而不是有两张桌子专辑和AlbumGenre 和他们的 FK 我们可以只有 Album2 是他们的连接，谓词是他们的谓词 album A 的名称 N 和 ... 的 AND/连词，并且专辑 A 属于流派 G 和 <代码>外键专辑 2 (G) REFERENCES Genre (G).然后规范化会告诉我们，如果每张专辑有一个流派，那么这是一个好的设计，但否则原版更好.同样，对于 Artist2，将 ArtistAlbum 合并为 Artist(如果艺术家创作了一张专辑，这是合理的).或者 ArtistAlbum &AlbumGenre 到 Album3(如果专辑有一位作者和一种流派，则合理).但不管所有重要的查询 &update 是谓词，而不是基数或约束.

Instead of having two tables Album & AlbumGenre and their FK we could have just Album2 that is their join, with predicate that is the AND/conjunction of their predicates album A has name N and ... and album A is of genre G with FOREIGN KEY Album2 (G) REFERENCES Genre (G). Then normalization would tell us that if there is one genre per album then that's an OK design but otherwise that the original is better. Similarly for Artist2 combining ArtistAlbum into Artist (reasonable if an artist authours one album). Or both ArtistAlbum & AlbumGenre into Album3 (reasonable if an album has one author and one genre). But regardless all that matters to query & update are the predicates, not the cardinalities or constraints.

因此，您的设计缺少像 ArtistAlbum &专辑流派.(您可能希望与上述其他表格结合使用.)

So your design is missing appropriate predicates/columns/tables like those of ArtistAlbum & AlbumGenre. (Which you might want to combine with other tables as above.)

PS您的问题不清楚流派"，流派1"和流派2".

PS Your question is not clear about "genre", "genre1" & "genre2".

这篇关于表间关系设计建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！