Советы по проектированию отношений между таблицами

Question

У меня есть информация о музыкальных альбомах, которые я хочу упорядочить в таблицах RDBMS с отношениями между ними. У меня есть следующая информация для каждого альбома: исполнитель, название альбома, год, лейбл, жанр, стиль, рейтинг. Пока я думаю сделать 4 таблицы - исполнители, альбомы (название, год, лейбл, рейтинг), genre1 и genre2 (каждый жанр со своими стилями). На диаграмме это выглядит следующим образом:

Но пока не знаете, как установить связь между альбомами и тремя другими таблицами? Т.е. когда я буду запускать запрос select name from artists Я хотел бы получить альбом с соответствующим исполнителем и жанровым стилем.

Как мне установить связь между таблицами в этом случае?

Answer 1

Вам необходимо прочитать введение в таблицы реляционных баз данных и запросы.

"Отношения" [sic] между таблицами, о которых вы говорите, - это FK (внешние ключи). FK говорит, что значения для списка столбцов в таблице отображаются как значения для некоторого другого списка столбцов в таблице, которые образуют PK (первичный ключ) или UNIQUE, установленные там. Вам не нужно объявлять или использовать FK для запроса. Как и все ограничения, включая PK & UNIQUE, они предназначены для СУБД, чтобы исключить ошибочные состояния базы данных.

Таблица (базовый результат или результат запроса) представляет отношение (бизнес / приложение) (отправка)/ ассоциация. В таблице содержатся строки, которые составляют какое-то истинное предложение (оператор) из связанного предиката (шаблон оператора). Предикат базовой таблицы предоставляется администратором базы данных. Предикат таблицы результатов запроса следует из базовых таблиц, операторов отношений и логических операторов в пользовательском выражении запроса. Т.е. предикат JOIN - это И предикатов его таблиц; СОЮЗ ИЛИ; КРОМЕ И НЕ; ON и WHERE условия И это условие в предикате JOIN; и т. д.

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

Обратите внимание, что не имеет значения, сколько жанров может иметь альбом, сколько альбомов может иметь жанр или может ли жанр иметь несколько идентификаторов и / или имен, запрос по-прежнему возвращает строки, удовлетворяющие этому предикату. Ограничения (включая FK) не нужны для запроса или обновления.

Обратите внимание, что мы можем применить те же преобразования предикатов плюс другие для записи ограничений. (Я использовал A как для авторов, так и для альбомов, поэтому я бы привел здесь пример переименования.)

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

Вместо двух таблиц Album & AlbumGenre и их FK мы могли бы иметь только Album2, который является их объединением, с предикатом, который является AND/ соединением их предикатов. album A has name N and ... and album A is of genre G с FOREIGN KEY Album2 (G) REFERENCES Genre (G), Тогда нормализация скажет нам, что если для каждого альбома существует один жанр, то это нормально, но в остальном оригинал лучше. Аналогично для Artist2, объединяющего ArtistAlbum в Artist (разумно, если художник создает один альбом). Или оба ArtistAlbum & AlbumGenre в Album3 (разумно, если у альбома один автор и один жанр). Но независимо от того, что имеет значение для запроса и обновления, это предикаты, а не количество элементов или ограничения.

Таким образом, в вашем дизайне отсутствуют соответствующие предикаты / столбцы / таблицы, такие как ArtistAlbum & AlbumGenre. (Которые вы можете объединить с другими таблицами, как указано выше.)

PS Ваш вопрос не ясен по поводу "жанра", "жанра1" и "жанра2".

Answer 2

Суть в том, что вам нужны внешние ключи. Ваши таблицы в настоящее время имеют отдельный идентификатор для каждой таблицы с именем id:

artist.id
artist.name
album.id
album.album
album.year
album.label
album.rating
genre.id
genre.name
genre.id
genre.name

Ключевое слово здесь "реляционный". Вам нужно связать таблицы. Возможно, вы бы разработали это, назвав свои идентификаторы лучше:

artist.artist_id
album.album_id
genre.genre_id

Затем в таблицу альбомов вы добавите столбцы для artist_id и genre_id, чтобы вы могли присоединить их обратно к таблицам исполнителей и жанров.

Без ФК у вас будет декартово произведение. Просто как тот.