Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Привет.

Имеется такая, стандартная, конструкция со свойством IsDeleted

CREATE TABLE [My].[Parent]
(
	[Id] INT NOT NULL IDENTITY PRIMARY KEY,
	[IsDeleted] bit not null
)

CREATE TABLE [My].[Child]
(
	[Id] INT NOT NULL IDENTITY PRIMARY KEY,
	[ParentId] int not null
	foreign key ([ParentId]) references [My].[Parent]([Id])
)


Вопрос. Как стандартизировать запросы к [My].[Child] в плане фильтрации записей принадлежащих удаленным Parent'ам ?
Дело усложняется ведь и тем, что уровень вложенности может быть и большим (у Child могут быть свои Childs).

Меня сейчас, в частности, интересует вопрос с точки зрения кодирования клиентского кода. Когда я, например, хочу извлечь все СТРОКИ из ДОГОВОРА какого-то ПОЛЬЗОВАТЕЛЯ, который может быть удален.
Получается мне придется от таблицы СТРОК сделать джоин к ДОГОВОРУ а потом и к ПОЛЬЗОВАТЕЛЮ. Насколько это плохо в плане производительности ? Мы же тут идем "против шерсти" - не от Договора до его деталей, а наоборот.

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

Кто сталкивался, какие есть решения для этого ?
21 окт 16, 17:56    [19810524]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ProBiotek,

А две таблицы для дерева зачем?
21 окт 16, 18:07    [19810555]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
aleks2
Guest
ProBiotek
Когда я, например, хочу извлечь все СТРОКИ из ДОГОВОРА какого-то ПОЛЬЗОВАТЕЛЯ,
Кто сталкивался, какие есть решения для этого ?


Женицца тебе, барин, надо.

Ты, ужо, определись: ДОГОВОРА или ПОЛЬЗОВАТЕЛЯ.

Ибо договор, с пусть удаленным пользователем, все ж реальный договор.
А если тебе нужны договора только не удаленных пользователей - ну дык, проверяй.
21 окт 16, 18:09    [19810560]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
TaPaK,

Не понял вопроса. А сколько нужно таблиц ? Я написал, что в реальности там вложенность может быть больше чем 2-3 таблицы. Так, что в дереве вполне можно получит и 5 таблиц. И получается при выборке из последней (REST подход же) мне нужно заджоинится со всеми 5тью предыдущими "против шерсти". Чревато ошибками, ищу решение упрощающее жизнь.

aleks2,

Ну я же привел лишь один частный случай :) Используется эта штука у меня везде, и нужна какая-то унификация, чтобы не плодить ошибки.


Может нужно как-то переделать структуру таблиц, но как ? Можно конечно в каждую таблицу добавить IsDeleted - но на самом деле там и так они уже есть, но смысл имеют только в данной таблице (удален пользователь, удален документ, удалена строка документа). Если добавлять IsDeletedParent то это чревато тем, что в таблице СТРОК будет такие столбцы:
-IsDeleded
-IsDeletedДокумент
-IsDeletedПользователь
т.е. вся цепочка. Не реалистично, и триггеры сложные будут, как бы там дедлока вообще не возникло.
21 окт 16, 18:33    [19810625]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ProBiotek,

Для дерева достаточно одной таблицы
21 окт 16, 20:16    [19810997]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
TaPaK,

Я Вас не совсем понимаю. Ну ок. Можно одно дерево.
И что из этого следует ? Примеры реализации бы посмотреть, чтобы понять идею. Как это поможет решить заданный вопрос.
21 окт 16, 20:33    [19811035]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31785
ProBiotek
Не нравится, что где-то в цепочке запросов можно забыть про эту проверку вывалив ошибочные данные.

Кто сталкивался, какие есть решения для этого ?

ProBiotek
Как это поможет решить заданный вопрос.

Вопрос какой? Как я понял, единственная проблема - как не забыть сделать проверку?
Ну, можно директору приказ издать.
Или наладить процедуры тестирования.
22 окт 16, 00:03    [19811523]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
alexeyvg
Вопрос какой?


Вопрос. Как стандартизировать запросы к [My].[Child] в плане фильтрации записей принадлежащих удаленным Parent'ам ?
Дело усложняется ведь и тем, что уровень вложенности может быть и большим (у Child могут быть свои Childs).
24 окт 16, 11:20    [19814791]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
ProBiotek
alexeyvg
Вопрос какой?


Вопрос. Как стандартизировать запросы к [My].[Child] в плане фильтрации записей принадлежащих удаленным Parent'ам ?
Дело усложняется ведь и тем, что уровень вложенности может быть и большим (у Child могут быть свои Childs).
Рекурсивное CTE, вестимо.
24 окт 16, 11:27    [19814857]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
iap,

Нет. Данные хранятся не в одной таблице. Есть таблицы: Пользователей, ИхДокументов, СтрокДокументов. И цепочка таких таблиц может быть глубокой.

CTE поможет если все в одной таблице хранится, ну максимум в двух (когда начальные данные берем из первой таблицы, а рекурсию уже применяем ко второй).
24 окт 16, 12:02    [19815079]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
ProBiotek
iap,

Нет. Данные хранятся не в одной таблице. Есть таблицы: Пользователей, ИхДокументов, СтрокДокументов. И цепочка таких таблиц может быть глубокой.

CTE поможет если все в одной таблице хранится, ну максимум в двух (когда начальные данные берем из первой таблицы, а рекурсию уже применяем ко второй).
Каждый уровень вложенности - в своей таблице?
Количество уровней произвольно и непостоянно?
А когда проектировали, то как предполагали с этим работать?
24 окт 16, 12:09    [19815117]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20978
ProBiotek
Вопрос. Как стандартизировать запросы к [My].[Child] в плане фильтрации записей принадлежащих удаленным Parent'ам ?
Дело усложняется ведь и тем, что уровень вложенности может быть и большим (у Child могут быть свои Childs).

Меня сейчас, в частности, интересует вопрос с точки зрения кодирования клиентского кода. Когда я, например, хочу извлечь все СТРОКИ из ДОГОВОРА какого-то ПОЛЬЗОВАТЕЛЯ, который может быть удален.
Получается мне придется от таблицы СТРОК сделать джоин к ДОГОВОРУ а потом и к ПОЛЬЗОВАТЕЛЮ. Насколько это плохо в плане производительности ? Мы же тут идем "против шерсти" - не от Договора до его деталей, а наоборот.
Вопрос твой упирается в то, является ли твоя иерархия истинным деревом.
Если да - тогда волоки своё IsDeleted по всем таблицам, места много он не съест, а каскадное обновление этого поля вряд ли будет частым. Как вариант - по таблицам расставить не копию IsDeleted, а ссылку на корневой элемент, тогда джойнить придётся не всю цепочку, а только "верхнюю" таблицу.
Ну а если нет, и где-то в середине у тебя имеется связь many-to-many (и частично - если ветки любят мигрировать от одного корня к другому), то джойни всё, предварительно продумав систему индексов.
24 окт 16, 12:16    [19815157]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20978
TaPaK
Для дерева достаточно одной таблицы
Да не дерево у него. В разных таблицах - экземпляры разных сущностей.
24 окт 16, 12:17    [19815161]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
sti
Member

Откуда:
Сообщений: 769
Иногда принимают соглашение, что все выборки делаются только из представлений и пишут их на все необходимые случаи. В представлениях уже либо стоит isDeleted=0, либо возвращается isDeleted из соответствующей таблицы.
24 окт 16, 12:35    [19815283]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina
Вопрос твой упирается в то, является ли твоя иерархия истинным деревом.
Если да - тогда волоки своё IsDeleted по всем таблицам, места много он не съест, а каскадное обновление этого поля вряд ли будет частым. Как вариант - по таблицам расставить не копию IsDeleted, а ссылку на корневой элемент, тогда джойнить придётся не всю цепочку, а только "верхнюю" таблицу.
Ну а если нет, и где-то в середине у тебя имеется связь many-to-many (и частично - если ветки любят мигрировать от одного корня к другому), то джойни всё, предварительно продумав систему индексов.


Я выше написал о таком варианте 19810625. Хорошо вот, что идея подтверждается еще кем то.

В принципе это хороший вариант. Легче 1 раз пройтись по всей иерархии и дописать триггеры, при изменении модели - чем в десятке запросов прикладного уровня это делать постоянно. Можно даже действительно оставить лишь одно поле: IsParentDeleted (в таблице ведь будет и свой IsDeleted). Нам в целом не важно на каком уровне там удален Парент. Это можно сделать отдельным запросом если где-то необходимо - и пусть он выйдет медленным за счет джоина всей иерархии.

PS. IsDeleted я сейчас итак использую везде. И при выборке самой нижней сущности (СтрокаДокумента или еще хуже СловаСтрокиДокумента) приходится в селекте это учитывать - джоинится по всей иерархии наверх и смотреть, не удален ли кто в предках. Все это мне не нравится, т.к. усложняет разработку и чревато ошибками, если забыли дописать это. Решил задать вопрос, может кто решал это уже. Похоже придется переделывать принцип работы :)
24 окт 16, 12:51    [19815336]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20978
ProBiotek
В принципе это хороший вариант.
Я считаю, что в принципе это плохой вариант. Потому что в нём целостность и непротиворечивость информации обеспечивается не штатной подсистемой, а кривыми триггерными костылями (не говоря уж об избыточности данных, так что ещё и минус нормализация). А так да, хороший...
24 окт 16, 12:55    [19815354]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
Я вообщем не понял - зачем дерево для удаленных записей. Тормоза гарантированы.
Вообще вьюхи строят для такого.
24 окт 16, 13:02    [19815381]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina,

Есть алтернатива лучше ? Я для этого тему и создал ).
24 окт 16, 13:08    [19815405]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Владислав Колосов
Я вообщем не понял - зачем дерево для удаленных записей. Тормоза гарантированы.
Вообще вьюхи строят для такого.


В принципе наверное да, нужно вьюхи сделать. Это действительно уберет мою проблему. Но не нравится, что производительность пострадает. Т.к. при каждой выборке листового узла будут джоины аж до самого корня. Хотим мы получить одну строку документа а получаем в нагрузку джоины с таблицей документа и его пользователя.

Так, что лучше, наверное, через триггеры обновлять IsParentDeleted у зависимых таблиц.Селекты будут летать.
Хотя тут вопрос о том, насколько часто удаляются записи. Если это происходит очень часто то решение не очень. Например если у данных есть "срок жизни" после которого все записи помечаются как устаревшие/удаленные.
24 окт 16, 13:15    [19815430]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
ProBiotek,

почему бы записи не удалять по-настоящему с логгированием?
24 окт 16, 13:18    [19815443]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
iap,

Это практически не возможно. На любую удаляемую сущность могут быть внешние ссылки.
Нужно будет каскадно все удалять.И придется это поддерживать. Тот, кто сделал внешний ключ на некую сущность должен будет дописать в процедуру удаления этой сущности - удаление и своей сущности. Сложно это все. Поддерживать флаг IsDeleted легче. Хотя и с ним же проблемы есть, о чем и данный топик.

Да и не принято данные физически удалять же. Всегда должна оставаться история. Для аналитики какой-либо, или расследований.
24 окт 16, 13:30    [19815512]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
iap
удалять по-настоящему с логгированием?


Вопрос в том, что в лог пойдет. Если слишком мало, то не сможем потом аналитику делать, расследования. А если полные данные и пойдут, то в чем смысл переноса строчки из одной таблицы в другую ? Только лишняя работа для перестройки индексов и усложнение аналитических запросов, вынужденых оперировать двумя таблицами с идентичными полями. Уж лучше пусть будет поле IsDeleted :)
24 окт 16, 13:33    [19815530]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20978
ProBiotek
Есть алтернатива лучше ? Я для этого тему и создал
Да понимаешь... у тебя сейчас система противоречивая. С одной стороны, юзер удалён. И по тому, какую проблему ты решаешь, связанные с ним документы тоже удалены. С другой стороны, они ни хрена не удалены, на них даже внешние ссылки есть. И вот всю эту бодягу ты хочешь как-то разрулить дополнительным костылём. Вовсе не странно, что куда не плюй, везде грабли...

Как по мне, так идеологически оно разруливается именно жёстким заданием того, что удаление папашки автоматом влечёт за собой удаление его деток. А в терминах твоей БД это собственно и есть каскадно обновляемое поле IsDeleted, введённое в структуру каждой таблицы. Просто требуется окончательно признать, что любой экземпляр любого объекта твоей схемы, а не только верхний папка, может быть удалён. И такое удаление влечёт за собой удаление и всех детей удалённого объекта. А программно эта ситуация обрабатывается каскадным обновлением связанных записей.

Насколько это нагрузочно (особенно по сравнению с вечным джойном для добычи этого атрибута из корня дерева) - решать тебе. Но как по мне - скорее чаще идут выборки, чем обновления, так что решение нормальное. Ну и непременно следует предусмотреть процедуру контроля (и, возможно, корректировки) этого атрибута у зависимых записей по запросу либо шедулеру - а ну как триггер сбойнёт? а заодно понять, удалённый деть живого папашки - это всегда криминал. или штатная ситуация.
24 окт 16, 13:49    [19815631]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina,

Удаление сущности вовсе не должно вести к физическому удалению данных относящихся к ней. Для истории они могут быть полезны. По моему совершенно стандартный подход иметь поле IsDeleted. Не я первый его придумал :)

Какой тут плюс я вижу в решении с каскадной пометкой IsParentDeleted: то, что нужно будет лишь один раз это прописать в триггерах и все. Дальше с системой достаточно просто и быстро работать. Помнить нужно лишь о 2х поля IsDeleted в каждой таблице (кроме корневой). Это как раз и стандартизирует работу - то, что я искал создавая топик.

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

Вьюха тоже поможет "сделать один раз", но, как вы и сказали, чаще происходит считывание, чем изменение. Поэтому выгодней чтобы при редком удалении триггеры сделали кучу джоинов и пометили признак удаления, и иметь легкий быстрый селект - чем наоборот.
24 окт 16, 14:13    [19815758]     Ответить | Цитировать Сообщить модератору
 Re: Как стандартизировать работу с полем IsDeleted в БД ?  [new]
aleks2
Guest
Не, орлы, птицам денег не нужно! (с) Василий Иванович Чапаев.

Почему никто не сказал магическое слово: "индексированное представление"?
И летать будет и триггеры отдыхают.

ЗЫ. Тока фуфлом тредстартер занимается. Изобретает квадратное колесо.
24 окт 16, 14:16    [19815772]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить