Создание записей в связанных таблицах — через MySQL триггеры или в коде приложения?

Question

[hostadmin]

Имеем БД MySQL (InnoDB) или похожую.
Предположим данные разбиты на две таблицы: user и user_profile. Соответственно данные в user_profile связаны 1 к 1 с данными в user.

Программисты, в таких случая, чаще всего в коде в транзакцию заворачивают создание записи в user, с последующим созданием связанной записи в user_profile.

DBA-щики (например) настаивают на том, что правильнее это выносить в БД (database first), повесить триггер, который при создании записи в user, создаст связанную запись в user_profile.

У способа через код такие плюсы и минусы:
+ Наглядность. Любой программист сразу понимает, что вот тут создается запись и другие записи в связанных таблицах.
+ Поддержка любой БД
- Все должны всегда помнить, что надо создавать записи в связанных таблицах (их может быть несколько, они могут меняться).
- Новые люди могут не "уметь транзакции", не знать о всех связях и т.д.
- Использование БД как "тупое" хранилище при кучи возможностей.

У способа через БД такие плюсы и минусы:
+ 100% консистентность
+ Упрощение кода приложения - коду кодово, базе - базово.
- Для новых программистов такое поведение может оказаться "магией".
- Поддерживается не всеми БД (наверно)
- Есть вероятность забыть добавить триггер при добавлении новой связанной таблицы
- При переезде на другую БД надо помнить о триггерах.

Comments

[65536]

всегда был противником размазывания логики приложения куда либо

[hostadmin]

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

[Дмитрий]

hostadmin, триггеры так же и чистят, там вообще вариантов много, я за них

[Vitsliputsli]

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

[hostadmin]

Vitsliputsli, вот про перемещение в историю при удалении интересно. Есть примеры реализации?

[Vitsliputsli]

hostadmin, что именно интересно? Просто по событию удаления - вставка удаляемых данных в другую таблицу.

[hostadmin]

Vitsliputsli, т.е. у основной таблицы есть полный клон (по структуре), в который помещаются данные при удалении из основной таблицы?

Может пример SQL кода есть?

[Vitsliputsli]

hostadmin, не обязательно клон, вставку ведь можно любую сделать. Если клон, то можно сделать то же самое партиционированием, но нужно будет везде таскать where или делить на таблицу и вьюху. Нет, под рукой кода нет, но как я написал там все просто, один инсерт с условием.

Answer 1

[kifirch]

Предположим, что данных стало оч много и нам понадобилось разнести эти таблицы про разным БД на наразных серваках
Тогда DBAшники жестко пососут писос с хранимками и FK ( мы рассматриваем это в теме MySQL)
Тогда только транзакции или поддержание консистентности данных на уровне аппликейшена

Answer 2

[Vitsliputsli]

Триггеры очень не очевидная вещь, поэтому если хочется в БД перетащить часть логики, то лучше сделать процедуру для этого.

Comments

[hostadmin]

Но, как я понимаю, и в коде это будет не совсем стандартно. Т.е. "обычный фреймворковский способ"

$user = new User();
$user->username='vasya';
$user->save();

уже не подойдёт.

[Vitsliputsli]

hostadmin, когда мы решаем часть логики держать в базе мы уже убиваем множество автоматически реализуемых решений, которые дают фреймворки. Не только запись, еще и с валидацией нужно что-то выдумывать, с контролем версий процедур, с их дебаггингом (я не знаю хорошего дебаггера для СУБД) и прочее.
Дальше больше, программист будет и кодить, и процедуры писать? Или нужно набирать отдельно для этого специалистов по процедурному языку? Вопрос финансов. Когда база просто хранилище и проблемы в ней попроще, а когда это множество процедур (кстати, их даже систематизировать гораздо сложнее), то проблемы уже такие, что нужен опытный DBA.
Имхо, лучше перетягивать логику в БД, когда есть четкое понимание зачем это нужно. Но в большинстве случаев, когда нужна быстрая разработка и хорошо сопровождаемый код, то я бы задействовал БД по-минимуму. И это мы еще не говорили о переезде на другую базу и горизонтальном масштабировании.

[Дмитрий]

hostadmin, я так понимаю вы работаете без фрэймворков, как минимум стоит попробовать, если это прогинг для души без затрат. то тем более

[Vitsliputsli]

Имхо, когда пишешь что-то без фреймворка, то начинаешь писать свой фреймворк, если же такого не наблюдается, значит у тебя проблемы с повторным использованием кода. Т.к. в большинстве случаев все равно нужен роутинг, работа с БД, авторизация и прочее. Т.е. либо пишешь велосипед, либо собираешь по частям из готового, по-сути тратя время, когда за тебя уже все сделали, но "для души", а вернее для самообразования очень полезно, чтобы понимать почему в фреймворках сделано именно так, а не иначе.
Кстати, очень часто коммерческое ПО в силу исторических причин написано без фреймворков, или задействуя фреймворк процентов на 30, или используя очень простой фреймворк, такой, что его по-сути и нет.

[hostadmin]

Дмитрий, с фреймворком, но тут же принципиальный вопрос.

Vitsliputsli, ну процедуры, это уже действительно другой уровень. Я-то именно про триггеры думал. Но в общем и целом, наверно вы правы - если нет отдельного DBA, то лучше держать всё в коде.

[Vitsliputsli]

hostadmin, почему другой уровень? Триггер по сути механизм запускающий процедуру по событию.

[hostadmin]

Vitsliputsli, в смысле простых триггеров, типа создания записи в связанной таблице.

[Vitsliputsli]

hostadmin, то что между BEGIN и END это по-сути процедура.

[Vitsliputsli]

Посмотрел почти до конца ролик в примере. С одной стороны он говорит об элементарных вещах, которые действительно должны быть. И действительно часто делают в коде то, что в запросе решается в разы проще и быстрее. Хотя мне все равно кажется, что нечисловой натуральный первичный ключ хуже решение, чем суррогатный числовой. С другой стороны, он явно не понимает зачем нужны ORM. Ведь ORM мы применяем не для того, что они лучше нас сформируют структуру БД, а для ускорения разработки. Понятно, когда у нас высоконагруженная система, то странно запускать ее на MySQL с ORM. А жаловаться что MySQL не делает того, что по документации и не должен - странно.

Answer 3

[d-stream]

Третий вариант: stored procedure[s]* - на входе получающая нужные данные для добавления/изменения, которая уже в рамках транзакции выполнить проверки-записи-вычисления... притом тогда "магию" подвязанных к профилю контактов (еще таблички уже не 1:1) можно спрятать там же... так же как уведомления о смене чувствительных данных, логгирования и еще чего-нибудь потенциально нужного... притом последнее - собственно не потребует ничего менять за пределами такой процедуры.

Comments

[hostadmin]

Тут уже предлагали, но справедливо отметили - кто процедуры писать будет?

[d-stream]

hostadmin, а это что - нечто выдающееся и доступное только 2-3 гениям на планете? )
Возможно это слегка отссет тяпляпщиков из серии "ща все выдерну из базы и потом в цикле пройдусь", но не более того)

[hostadmin]

d-stream, "А платить за нетяпляпщиков кто будет?"

[d-stream]

hostadmin, "дешево, быстро, качественно" - тут выбор даже не два из трех...