Как правильно составить запрос на создание промежуточной таблицы многие-ко-многим?

Question

[Filarru]

Здравствуйте!

Есть две таблицы "магазин" и "товар". У них есть свои неключевые поля.

CREATE TABLE STORE (
store_ID INT NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
PRIMARY KEY (store_ID )
);

CREATE TABLE ITEM (
item_ID INT NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
PRIMARY KEY (item_ID)
);

Много разных магазинов и много разных товаров.
Это дает третью таблицу для реализации связи "многие-ко-многим".

НО! Не ясно, как именно должен выглядеть запрос при создании такой таблицы.
У меня написано два варианта:

CREATE TABLE STORE_ITEM (
store_ID INT NOT NULL,
item_ID INT NOT NULL,
PRIMARY KEY (store_ID, item_ID)
FOREIGN KEY (store_ID) REFERENCES STORE(store_ID)
FOREIGN KEY (item_ID) REFERENCES ITEM(item_ID)
);

или

CREATE TABLE STORE_ITEM (
store_item_ID INT NOT NULL AUTO_INCREMENT,
store_ID INT NOT NULL,
item_ID INT NOT NULL,
PRIMARY KEY (store_item_ID, store_ID , item_ID)
FOREIGN KEY (store_ID) REFERENCES STORE(store_ID)
FOREIGN KEY (item_ID) REFERENCES ITEM(item_ID)
);

ВОПРОСЫ:
1. Какой вариант и в каком случае должен применяться?
2. Первый вариант подходит для промежуточной таблицы, а второй для самостоятельной сущности где могут быть свои неключевые поля?
3. Приемлемо ли для сущности-потомка все поля foreign keys указывать точно так, как они указаны у родителя или лучше добавлять префикс? Например, для третьей таблицы из примеров: store_ID или store_item_store_ID?

Answer 1

[Everything_is_bad]

Если сочетание (store_ID, item_ID) уникально, то достаточно первого варианта
Если нет, то второй, только вот без этого извращения PRIMARY KEY (store_item_ID, store_ID , item_ID), зачем тут оно? достаточно просто PRIMARY KEY(store_item_ID)
Ну и по названиям, достаточно во всех таблицах первичный ключ назвать просто ID, а не store_ID, item_ID и извращений уровня store_item_ID

Comments

[Filarru]

только вот без этого извращения PRIMARY KEY (store_item_ID, store_ID , item_ID), зачем тут оно?

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

[Everything_is_bad]

Filarru, у тебе и так уже store_item_ID уникален, зачем еще что-то добавлять?

[Filarru]

Everything_is_bad, о том и вопрос.
Где и как тогда должен применяться составной ключ?
В каких именно случаях в промежуточную таблицу добавляется её собственный ключ, помимо ключей родителей?
Опять же, если это взаимозаменяемые понятия, тогда не ясна суть миграции родительских ключей в составной ключ потомка.

[Everything_is_bad]

Filarru, я тебе вот прям про это и написал в ответе.

[Filarru]

Everything_is_bad, просто я с трудом тогда понимаю суть логической структуры



Таблица МАРКА или МАРКА_В_КОЛЛЕКЦИИ
Тут что, тоже получается все обходится одним лишь уникальным ключом?
Но зачем тогда родительские ключи (FK) в зоне ключевых атрибутов у потомка, если потомок итак имеет свой уникальный ключ?

[Everything_is_bad]

Filarru, без понятия, отвык я от таких схем описания, давай реальный DDL, да и в реальной жизни, такие конструкции без нормально ТЗ, можно не рассматривать. По секрету, академически задачи для SQL, очень сильно оторваны от реальной жизни.

[Akina]

достаточно во всех таблицах первичный ключ назвать просто ID, а не store_ID, item_ID

Вот это сомнительная рекомендация.
Во-первых, при таком именовании сразу очень хорошо видно поля-ссылки на другие таблицы.
Во-вторых, при связывании гораздо проще (и опять же нагляднее) написать USING (table1_id), чем строгать ON table1.id = table2.table1_id. Особенно актуально для комплексных запросов, где и без того достаточно чего в голове держать.
В третьих, не возникает интерференции имён полей.

и извращений уровня store_item_ID

А вот для non-entity таблиц - согласен.

[Everything_is_bad]

Akina, а вот кстати это вытекает от того как генерируют SQL, у меня уже давно 98% через ORM, поэтому нет проблем с table1.id = table2.table1_id, там даже лучше чтобы названия были проще, поэтому и ID.