Круговые отношения один ко многим, родительско-дочерние таблицы в MySQL

Я столкнулся с этой проблемой:

У меня есть родительская таблица и дочерняя таблица, у одного родителя может быть несколько дочерних, стандартная история.

Это ограничения:

  • у каждого родителя должен быть хотя бы один дочерний элемент,
  • у каждого родителя должен быть один любимый ребенок,
  • у каждого родителя может быть один наименее любимый дочерний элемент

Как сделать это в SQL?

Я не уверен, что стандартные родительско-дочерние таблицы можно использовать из-за циклических отношений:

Parent table:
parentId
favouriteChildId NOT NULL
leastFavouriteChildId NULL

Child table:
childId
parentId

Я думал об использовании таблицы мостов, но не знаю, как смоделировать эти ограничения.

РЕДАКТИРОВАНИЕ. Чтобы добавить ясности, вот часть контекста проблемы:

Есть таблица Price (дочерняя) и таблица PriceGroup (родительская).

PriceGroup имеет несколько цен, одну обязательную основную цену (favouriteChild) и может иметь одну официальную цену (leastFavouriteChild).

Следующее не связано с проблемой, но проливает некоторый свет на контекст: цены сгруппированы в соответствии с продуктами, к которым они относятся, и один продукт может иметь несколько цен — затем они группируются в ценовые группы, и для каждой группы нужна ссылка на основную цену и официальную цену (если она есть).


mysql sql parent-child circular-reference
person Martin Vrkljan    schedule 16.05.2011    source источник
comment
Это только родители и их прямые потомки или это может быть несколько уровней? редактировать Кстати: вы не можете «заставить» (заставить) родителя иметь ребенка. edit Связи с базой данных переоценены. Вы должны исправить эти отношения в своих моделях, это намного важнее, чем все эти ссылки MySQL.   -  person Rudie    schedule 16.05.2011
comment
У вас возникнут проблемы, так как вы не можете вставить дочерний элемент без parentId, а также вы не можете вставить родителя без childId (упоминается в файле favouriteChildId). Чтобы иметь возможность вставлять что-либо вообще, вам нужно (временно) отключить ограничения внешнего ключа, вставить обе строки, а затем снова включить ограничения внешнего ключа, а затем зафиксировать вставку. Я бы попробовал спроектировать таблицы по-другому. Вероятно, было бы полезно, если бы мы лучше понимали определение проблемы.   -  person PatrikAkerstrand    schedule 16.05.2011
comment
Я боялся, что кто-то скажет (временно) отключить ограничения внешнего ключа. Пожалуйста, не делайте этого и переосмыслите свою модель БД.   -  person Rudie    schedule 16.05.2011
comment
@PatrikAkerstrand Да, я знаю об этом, поэтому я отметил, что не уверен, сработает ли этот дизайн. Отключение FK сработало бы, но это просто доказывает плохой дизайн, не так ли? :) Я вырвал проблему из контекста для простоты, но отредактирую вопрос, чтобы добавить больше ясности.   -  person Martin Vrkljan    schedule 16.05.2011

Ответы (3)


arrow_upward
3
arrow_downward

Из бизнес-правил, которые вы дали

  1. у каждого родителя должен быть хотя бы один ребенок,
  2. у каждого родителя должен быть один любимый ребенок,
  3. у каждого родителя может быть один наименее любимый ребенок

Ваше решение

Parent table:
parentId (PK)
favouriteChildId NOT NULL (FK)
leastFavouriteChildId NULL (FK)

Child table:
childId (PK)
parentId (FK)

удовлетворяет 2 и 3. Но также он удовлетворяет 1 (поскольку избранныйChildId NOT NULL не позволит создавать родительские записи без дочерних элементов).

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

Обычно в SQL есть возможность сделать что-то вроде

BEGIN TRANS
INSERT INTO TABLE1 (FK not checked yet)
INSERT INTO TABLE2 (FK not checked yet)
COMMIT (All integrity checked)

в этом случае «циклическая ссылка» не будет проблемой (см. DEFERRED )

Mysql не поддерживает его, поэтому у вас есть следующие варианты

Триггеры:
Можно предположить, что во время вставки родительской записи любимый дочерний элемент уже известен, тогда у вас может быть триггер, который будет запускаться перед вставкой в ​​родительскую таблицу и

  • вставьте любимый дочерний элемент в дочернюю таблицу, получив его идентификатор
  • вставьте родительскую запись с идентификатором ребенка

ПРИМЕЧАНИЕ. Проблема в том, что таким образом вы можете формально удовлетворить критерии, но чтобы сначала вставить дочернюю запись, вам придется либо использовать дополнительные столбцы в родительской, чтобы триггер мог знать о других полях в дочерней таблице, либо вставить пустую запись (в в любом случае дизайн не чистый)

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

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

РЕДАКТИРОВАТЬ: Что касается триггеров, есть также способ реализовать правило с триггерами, и это означает, что вам придется вставлять записи отдельно и что вы можете иметь данные, которые в какой-то момент нарушают ваши бизнес-правила.

В этом случае вы можете иметь атрибут STATUS для родительской записи (например: COMPLETE vs INCOMPLETE) и сделать favouriteChildId FK, допускающим значение NULL, но при обновлении статуса на COMPLETE вы можете запустить проверку целостности.

Это требует дополнительного столбца, но может сделать вещи довольно чистыми (на самом деле вы можете создать представление для этой таблицы, которое будет отображать только записи, которые являются ЗАВЕРШЕННЫМИ, фактически делая его выглядящим как таблица с FK NOT NULL).

person Unreason    schedule 16.05.2011

arrow_upward
2
arrow_downward

Вы можете смоделировать (в некоторой степени) другие ограничения:

Parent Table
parentId (PK)

Child table:
childId  (PK)
parentId (FK)

Is Favorite table:
childID (PK)(FK)

Is Least Favourite table:
ChildID (PK)(FK)

Строка всегда будет вставлена ​​в Is Favorite Child; один будет вставлен в список «Наименее любимый» только в том случае, если есть наименее любимый дочерний элемент: вставка, обновление, удаление выполняются триггером в представлении; выбор по левому соединению на столах.

Это не имеет отношения к обязательному характеру отношения «Избранный дочерний элемент», который должен обрабатываться триггерами вставки/обновления/удаления.

person Chris Walton    schedule 16.05.2011

arrow_upward
0
arrow_downward

Если только один уровень в глубину:

Parents ( ParentID, Title, etc )
Children ( ChildID, ParentID, Title, etc )

У каждого Child всегда должно быть ровно 1 Parent, а у Parents всегда должно быть >= 0 Children. (Нет никакого способа обойти это.)

Если несколько (неизвестных) уровней глубины:

Items ( ItemID, ParentItemID NULL, Title, etc )

Очень просто: Items.ParentItemID = Items.ItemID

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

person Rudie    schedule 16.05.2011
comment
Это базовая модель таблиц «один ко многим», поэтому нет многоуровневой иерархии. - person Martin Vrkljan; 16.05.2011