Как правильно использовать составные первичные ключи в таблицах?

Question

[Fox 777]

вопрос по поводу использования составных первичных ключей (PRIMARY KEY, состоящий из двух и более колонок) в таблицах баз данных.
разрабатываю проект, в котором требуется связывать несколько таблиц между собой через промежуточные таблицы. Например, есть таблицы Staff, Services и промежуточная таблица StaffServices, которая связывает сотрудников с услугами, которые они предоставляют. Вот пример структуры таблицы:

CREATE TABLE StaffServices (
    staffId INT,
    serviceId INT,
    PRIMARY KEY (staffId, serviceId),
    FOREIGN KEY (staffId) REFERENCES Staff(id),
    FOREIGN KEY (serviceId) REFERENCES Services(id)
);

Также есть аналогичные промежуточные таблицы для связи между другими сущностями:

CREATE TABLE StaffBusiness (
    staffId INT,
    businessId INT,
    PRIMARY KEY (staffId, businessId),
    FOREIGN KEY (staffId) REFERENCES Staff(id),
    FOREIGN KEY (businessId) REFERENCES Businesses(id)
);

CREATE TABLE StaffBusinessSchedules (
    staffId INT,
    businessId INT,
    scheduleId INT,
    PRIMARY KEY (staffId, businessId, scheduleId),
    FOREIGN KEY (staffId) REFERENCES Staff(id),
    FOREIGN KEY (businessId) REFERENCES Businesses(id),
    FOREIGN KEY (scheduleId) REFERENCES Schedules(id)
);

1. Насколько правильным и общепринятым является использование составных первичных ключей в таких случаях?
   
2. Какие могут быть плюсы и минусы данного подхода?
   
3. Есть ли альтернативы, которые стоит рассмотреть в подобных ситуациях?
   
4. Как это влияет на производительность базы данных и целостность данных?
   
5. Какие best practices существуют для таких случаев?
   
6. Буду рад услышать ваши мнения и советы!
   

Comments

[Akina]

Как правило, три и более внешних ключей в одной связующей таблице, ссылающихся на сущность, а не на словарь - это ошибка проектирования.

Answer 1

[ThunderCat]

0)

Staff, Services и промежуточная таблица StaffServices

https://brainstation.io/learn/sql/naming-conventions , https://www.sqlshack.com/learn-sql-naming-conventions/

1)

Насколько правильным и общепринятым является использование составных первичных ключей в таких случаях?

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

2)

Какие могут быть плюсы и минусы данного подхода?

Да нет тут плюсов. Уникальность записи через 2 значения даже из приложения юзать неудобно, особенно когда работаешь с фреймворками, которые соблюдают какой-никакой конвеншн.

3)

Есть ли альтернативы, которые стоит рассмотреть в подобных ситуациях?

Нормальный первичный ключ через автоинкремент и уник на составной.

4)

Как это влияет на производительность базы данных и целостность данных?

Зависит. Если внешние ключи настроены правильно, то консистентность бд будет гарантироваться правилами ключей (в рамках их описания конечно), отсюда все вытекающие, которые сильно зависят от остального построения связей. Производительность будет сильно зависеть от конкретной насторйки бд, машины/образа, но в основном от количества записей, так то движку фиолетово какие индексы использовать, главное чтобы они были.

5)

Какие best practices существуют для таких случаев?

Собсно выше основное описал.

6)

Буду рад услышать ваши мнения и советы!

Велком.

Comments

[Fox 777]

Спасибо большое за ваш ответ и советы по составным первичным ключам. Благодаря вашим рекомендациям я пересмотрел структуру базы данных и внедрил автоинкрементные первичные ключи с уникальными индексами для составных ключей.

[MVV]

1) в данном конкретном случае эти таблицы никакую сущность не описывают. Это - чисто таблицы связи, которые нужны исключительно из-за ограничений реляционнной можели: здесь нет атрибутов, которые могут быть сопоставлены именно комбинации первичных ключей, а не ключу таблицы с какой-то связываемой сущностью по отдельности.

2) Плюс - минус лишнее поле в таблице под суррогатный первичный ключ. И см. следующий пункт

3) Суррогатный ключ + UNIQUE на составной - это лишний индекс на таблицу связи, со всеми вытекающими последствиями.

4)Если БД локальная - влияния нет. Если есть (или будет) репликация с несколькими источниками, то с суррогатный ключ - лишняя сложность, т.к. придется думать о его уникальности. Впрочем, типовые решения для этого тоже есть.

5) Best practices применять слепо не следует, иногда, наоборот, "аннти-паттерны" оказываются более применимы к конкретным условиям. А так, оба варианта применимы.

[ThunderCat]

MVV,

в данном конкретном случае эти таблицы никакую сущность не описывают.

И? Сегодня не описывает, завтра описывает. Алсо, как я написал, есть готовые решения/фреймворки, требующие определенной структуры, в структуре полей в том числе. Наличие отдельного первичного автоинкрементного ключа в частности. Если сомневаешься ставить или нет - ставь. Если знаешь зачем не ставить - не ставь. Все просто.

Плюс - минус лишнее поле в таблице под суррогатный первичный ключ... это лишний индекс на таблицу связи, со всеми вытекающими последствиями.

Оочень притянуто за уши. Единственная возможная проблема приходящая в голову - катастрофическая нехватка памяти/места на сервере. Ну или нагрузка по записи в пивот такая, что 2 индекса уже не вывозит (больше из области "чем вы думали при выборе инструмента" или реально кривое проектирование).

Best practices применять слепо не следует, иногда, наоборот, "аннти-паттерны" оказываются более применимы к конкретным условиям.

Рано. На базовом уровне лучше именно фигачить по бестпрактисам, когда уровень вырастет до условного понимания "как все оно между собой е**тся" можно уже и творчески подходить к процессу.

[MVV]

И? Сегодня не описывает, завтра описывает.

Между прочим, это - ключовй вопрос проектирования: что мы тут имеем - чисто связь или таки сущность, связанную с двумя другими. Так что "завтра" должно быть предусмотрено. И я в своем ответе особо подчекнул именно этот момент. А идея всюду закладывать возможность расширения - это так себе идея: практика (моя) показывает, что расширение хоть и обязательно потребуется, но - почти обязательно в непредусмотренную сторону. А предусмотренное расширение может, наоборот, помешаться (пример - с репликацией из нескольких источников - я приводил).

Алсо, как я написал, есть готовые решения/фреймворки, требующие определенной структуры, в структуре полей в том числе.

Не пользуйтесь дурными фреймворками - пользуйтесь хорошими, годными фреймворками. Если для вашего языка нет хорошего, годного фреймворка - смените язык. Например, переходите на .NET/C#, там Entity Framework в составной ключ умеет и по умолчанию для таблиц связи именно его и использует, впрочем в связь через сущность со своим суррогатным ключом он тоже умеет.

Оочень притянуто за уши. ... Ну или нагрузка по записи в пивот такая, что 2 индекса уже не вывозит

Автор вопроса просил перечислить недостатки? Вот я и перечисляю. Не нравится - но они есть. Насколько существенны? Зависит от проекта.
Ну и, индексов там, скорее всего, больше, чем 2 (для второго поля в PK полезно иметь индекс для навигации в обратную сторону, ну, и некоторые СУБД (вспоминается Interbase) не умеют делать FK без индекса).

Рано. На базовом уровне лучше именно фигачить по бестпрактисам

Задумываться никогда не рано. Если не задумываться - уровень никогда не вырастет, так и будешь слепо следовать "священным заповедям", не понимая их смысл. А пока уровень не вырос, лучше работать под руководством старших товарищей, которые отвечают за архитектуру этого конкретного проекта и в архитектуру уже умеют - они, если чо, поправят.
Ну и понимать, что эти "наилучшие практики" - не догма, полезнее с самого начала. Короче, не боги горшки обжигают. Но - мастера.

PS Есть ощущение, что автор вопроса эти комментарии так никогда не прочитает, так что не вижу смысла стараться дальше.

Answer 2

[Everything_is_bad]

По опыту, в подавляющем большинстве случаев, используют суррогатный первичный ключ, а если хочешь именно составной первичный ключ, ты точно должно понимать и главное доказать, почему нужен именно он.

Answer 3

[Tannenfels]

Объясните, зачем вам потребовался составной первичный ключ? Он абсолютно излишен тут.

Answer 4

[MVV]

Хоть помеченный ответ уже есть, но лучше поздно, чем никогда.
Ответ на вопрос зависит от того, являются ли ли эти таблицы отображением самостоятельных сущностей или просто реализацией связи "многие-ко-многим". Понять, являются ли эти таблицы сущностями, можно ответив себе на простой вопрос: есть ли и будут ли у этих кандидатов в сущности какие-либо атрибуты, которые зависят сразу от комбинации PK обеих связываемых таблиц с сущностями. Если ответ на это вопрос - "нет", то эти таблицы самостоятельных сущностей не содержат а потому составной первичный ключ для них - правильное решение, хотя и вопреки существующей моде.
Если же такие атрибуты есть или, хотя бы, допускаются, то к этим таблицам надо относиться как к таблицам для сущностей, и здесь суррогатный первичный ключ может оказаться полезен.

PS Вообще, при выборе решения надо исходить из условия задачи, а не из соответствия решения какому-нибудь "единственно верному учению", или из текущей моды AKA "best practices".

PPS Разницы по скорости вы, скорее всего не заметите.