Как лучше сделать ссылку на 2 сущности ? (Oracle и MS SQL)

← →
ANB © (2005-12-20 12:01) [0]

Пример из налоговой (бухгалтерии) :
Есть 2 сущности : налогоплательщики юридические лица и физические лица. Естественно, набор полей - атрибутов у них отличается и логично хранить их в разных таблицах.
Из многих таблиц ссылка может быть как на ЮЛ, так и на ФЛ.
Видел несколько способов реализации подобного :
1. Отдельные таблицы ЮЛ и ФЛ. Завести в подчиненных таблицах по 2 ссылочных поля - отдельно на ЮЛ, отдельно на ФЛ. Повесить честные FK. Проблема - надо либо анализировать оба поля на null/0 либо добавлять поле - тип связки. Все это потом сильно усложнит запросы. Плюс придется вешать излишние составные индексы.
2. Отдельные таблицы ЮЛ и ФЛ. Организовать уникальность ID в этих таблицах (исключить пересечение). В ссылочное поле пишем просто ID. Проблемы - FK идут в лес, проверка целостности только хранимкой, запросы тоже не простые, в MS SQL в лес идут автоинкрементальные ID (В оракле просто общий секвенсер).
3. Дерево с ветками. Делаем 3 таблицы : Налогоплательщики (с общими полями для ЮЛ и ФЛ), отдельно ФЛ и ЮЛ, ссылающиеся на стержневую общую таблицу всех налогоплательщиков. Запросы писать легко. Работать будет намного шустрее первых 2-х способов. Минус - если удалять записи из таблицы, например, ФЛ, а в основной - не удалять, то получим висячие записи, т.к. FK это не отследит. Т.е. без проверки на хранимке тут не обойтись.

Какой способ лучше или есть свои мысли ?

← →
Johnmen © (2005-12-20 12:07) [1]

4. Отдельные таблицы ЮЛ и ФЛ. Завести в подчиненных таблицах по 1 ссылочному полю - на ЮЛ/на ФЛ. И добавить поле признак ЮЛ/ФЛ.

← →
ANB © (2005-12-20 12:15) [2]

> Johnmen © (20.12.05 12:07) [1]
> 4. Отдельные таблицы ЮЛ и ФЛ. Завести в подчиненных таблицах
> по 1 ссылочному полю - на ЮЛ/на ФЛ. И добавить поле признак
> ЮЛ/ФЛ.

Это вариант №1 + 2. Со всеми минусами - в том числе - невозможно завести честные FK.

ЗЫ. В вариант №3 забыл дописать - повесить честный FK.

← →
Курдль © (2005-12-20 12:19) [3]

Для таких типов данных существует совершенно однозначное решение в РБД.

Это наследование.

На концептуальном уровне это выглядит так:
Создается сущность "СУБЪЕКТЫ", от нее наследуются (inharitance) "ЮР_ЛИЦА" и "ФИЗ_ЛИЦА", причем с уникальным ID (mutually exclusive).

На физическом уровне получается 3 таблицы.
1. "СУБЪЕКТЫ" с идентификатором SBJ_ID и общим набором полей.
2. "ЮР_ЛИЦА" с внешним уникальным ключом по SBJ_ID и своим набором полей.
2. ФИЗ_ЛИЦА" с внешним уникальным ключом по SBJ_ID и своим набором полей.

← →
Johnmen © (2005-12-20 12:22) [4]

>ANB © (20.12.05 12:15) [2]
>Это вариант №1 + 2. Со всеми минусами - в том числе - невозможно завести честные FK.

Почему же невозможно? И какова в твоём понимании степень "честности"?

← →
ANB © (2005-12-20 12:23) [5]

> Курдль © (20.12.05 12:19) [3]

Это и есть вариант №3.

← →
ANB © (2005-12-20 12:27) [6]

> Johnmen © (20.12.05 12:22) [4]
> >ANB © (20.12.05 12:15) [2]
> >Это вариант №1 + 2. Со всеми минусами - в том числе - невозможно
> завести честные FK.
>
> Почему же невозможно? И какова в твоём понимании степень
> "честности"?

Честные - add constraint. Т.е. честно создать ограничение, а не проверять целостность в триггерах и хранимках.
Почему невозможно - а попробуй :)
Делать ссылку по условию "ИЛИ" на две таблицы синтаксис SQL не позволяет.
Если сделать 2 отдельных внешних ключа, то это будет уже условие "И", что тут совершенно не нужно. Если знаешь, как создать внешний ключ на 2 таблицы с условием "ИЛИ" - запости, плз.

← →
Курдль © (2005-12-20 12:28) [7]

> Это и есть вариант №3.

Наверное. Только меня смутило слово "Дерево с ветками..."
Это термин какого-то CASE инструментария?
Мне сразу представился вариант именно древовидной структуры, где все субъекты записаны в одной базе, но в иерархическом виде.
Типа

SBJ_ID = 1, "Налогоплательщики"; SBJ_ID = 2, "Прочие"; SBJ_ID = 3, SBJ_ID_PR = 1, "Физ. лица"; SBJ_ID = 4, SBJ_ID_PR = 1, "Юр. лица"; SBJ_ID = 5, SBJ_ID_PR = 3, "Петров А.В.";

← →
Johnmen © (2005-12-20 12:33) [8]

>ANB © (20.12.05 12:27) [6]

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

← →
ANB © (2005-12-20 12:34) [9]

> Курдль © (20.12.05 12:28) [7]

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

← →
Курдль © (2005-12-20 12:40) [10]

> ANB © (20.12.05 12:34) [9]
> Вообще то класическое парентовое дерево удобно при проектировании,
> но довольно много проблем при реализации.

А куда от него деваться? Любая реализация "справочниковой идеологии" на нем зиждется. Это только апологеты MS SQL могут в холодном бреду посоветовать использовать GUID-ы для таких целей :)

← →
Desdechado © (2005-12-20 13:29) [11]

я предпочитаю (и постоянно использую) вариант 3
например, гдавная таблица разнообразных объектов с общими полями названия, примечания и типа
и куча других таблиц, в которых в зависимости от типа могут быть, а могут и не быть ссылки на главную
вся работа строится от главной, без нее ни одна выборка просто не идет
поэтому опасения, что при удалении из подчиненной главная запись осиротеет, имхо, надуманны
нельзя удалить "номер плательщика НДС" без удаления самого плательщика

← →
Sergey13 © (2005-12-20 13:46) [12]

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

Почему то все решили, что надо обязательно 2 таблицы. Не вижу причин не рассматривать и возможность 1 таблицы со всеми возможными полями того и другого типа. Ничего криминального, ИМХО.

← →
evvcom © (2005-12-20 14:02) [13]

> 1. Отдельные таблицы ЮЛ и ФЛ. Завести в подчиненных таблицах
> по 2 ссылочных поля - отдельно на ЮЛ, отдельно на ФЛ. Повесить
> честные FK. Проблема - надо либо анализировать оба поля
> на null/0

Повесить констрейнт на обязательное одно поле null, другое not null, а в запросах анализировать всего одно поле в case.
2 - мне совсем не нравится.

> 3. ... Работать будет намного шустрее первых 2-х способов.

Не уверен, что шустрее, ведь здесь джойнить надо уже 3 таблицы, а не 2.
У меня по 3 способу сейчас работает одна задача. Я подумываю иной раз для увеличения скорости все 3 собрать в одну. Это уже 5 вариант. Но у меня здесь своя кухня, свои проблемы, может и не выйдет.
Плюсы: экономия на джойнах, соответственно выигрыш во времени; отсутствие висячих записей.
Минусы: увеличение общего количества полей; каждый констрейнт not null специфичного для каждой сущности поля придется прописывать отдельно, а не привычной установкой/снятием галки; скан по индексу оптимизатор будет выбирать еще в меньшем количестве случаев, нежели в предыдущих вариантах.
По поводу "занимания места" null-полями: насколько я понял из курса лекций, Oracle физически не расходует место под поля записей, не содержащих значения. Так что тут все ок.

← →
evvcom © (2005-12-20 14:03) [14]

О, мой 5 вариант уже в
> Sergey13 © (20.12.05 13:46) [12]

:)

← →
ANB © (2005-12-20 14:09) [15]

> поэтому опасения, что при удалении из подчиненной главная
> запись осиротеет, имхо, надуманны

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

> Sergey13 © (20.12.05 13:46) [12]

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

> А куда от него деваться?
- деваться некуда. Но если есть возможность сделать без дерева - лучше сделать без дерева.
Вот даже в оракле хотя и есть connect by, но попробуй сделать выборку, так чтобы в результате сортировка шла по группам, а сами группы и то, что внутри группы - по алфавиту.
Пример таблицы : Tree(ID, Parent_ID, Name)

← →
evvcom © (2005-12-20 14:14) [16]

> Как только повесишь ссылочную целостность на хранимки

А ты вешай на триггера, тогда и умники по барабану. Мне, как разработчику, тоже гораздо удобнее работать sql-запросом с группой записей, чем через хранимку по одной. Я тоже выходит умник.

← →
ANB © (2005-12-20 14:18) [17]

> evvcom © (20.12.05 14:14) [16]

Мне тоже так больше нравится. Но если защита стоит на хранимках, значит надо через хранимки :(((.
А тем, кто так базы чистил, строго настрого наказывали, чтобы данные меняли только через хранимки, т.к. констрейнтов вообще не было (это был случай 2).

← →
evvcom © (2005-12-20 14:25) [18]

> Но если защита стоит на хранимках, значит надо через хранимки

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

← →
Курдль © (2005-12-20 14:48) [19]

> Плюсы: экономия на джойнах, соответственно выигрыш во времени

Ты чего это за временем-то гоняешься? Миллиарды записей клиенту выдаешь?
Если в погоне за временем нарушать законы хранения данных - это только осложнит работу с базой. Данные должны храниться максимально денормализованно. А скорость выборки - это проблема СУБД и железа.
У меня сейчас много сущностей - трехступенчатые иерархии, а среднестатистический запрос обращается к 10 и более таблицам по разнообразным джоинам. И никаких проблем.
Правда для модификации и удаления данных служит специальный ДатаАдаптер,
который автоматически разруливает все каскадные закономерности (но такое возможно только при использовании многотабличных ДатаСэтов ADO.NET).

PS: Кто это мог придумать "защиту на хранимках"? Это еще что за звэрь?

← →
ANB © (2005-12-20 14:56) [20]

> evvcom © (20.12.05 14:25) [18]
>
> > Но если защита стоит на хранимках, значит надо через хранимки
>
>
> Неверный в корне подход к решению задачи. Значит переписать
> защиту нафиг.

:))) На эту граблю я больше наступать не буду. Честные констрейнты - рулез форева.

> PS: Кто это мог придумать "защиту на хранимках"? Это еще
> что за звэрь?
- еще на триггерах бывает. Но там хоть на что то похоже. А это я работал с одним крутым ядром. Типа в оракле метаданные описываешь, а система сама генерит пакеты для защиты ссылочной целостности и в веб интерфейсе все это отображает. Идея неплохая была, но реализация - ой ой. Так я точно делать не буду.

← →
Курдль © (2005-12-20 14:58) [21]

> - еще на триггерах бывает. Но там хоть на что то похоже.

В этом как раз никто не сомневается. Если триггер умеет отзываться на нужное вмешательство в базу, значит он способен поддерживать целостность данных.
А вот как это на ХП?..

← →
evvcom © (2005-12-20 15:06) [22]

> Ты чего это за временем-то гоняешься? Миллиарды записей
> клиенту выдаешь?

Причем тут клиент? Джойн на сервере делается. Другое дело, если ты скажешь, что изначально (перед джойном) данные плохо фильтруются (высокая селективность). А если клиент хочет получить отчет, а для него во внутреннем селекте высокая селективность, потом джойн (может даже многомиллионный, но один к одному), а потом только аггрегирование. Клиенту выдается всего пара сотен записей в отчет, но сервер перелопатил и наджойнил несколько миллионов записей.

> а среднестатистический запрос обращается к 10 и более таблицам
> по разнообразным джоинам

У меня тоже их не мало и тоже, можно сказать, все ок. Но таблица таблице рознь.

> но такое возможно только при использовании многотабличных
> ДатаСэтов ADO.NET

Не только ADO. Ты вроде сейчас с Ораклом работаешь? ODAC тоже такое поддерживает, если я правильно понял, о чем ты говоришь. Ты имеешь ввиду многокурсорные НД?

← →
Sergey13 © (2005-12-20 15:06) [23]

2[15] ANB © (20.12.05 14:09)
>Только проблема навыворот - тогда можно будет удалять запись из столбовой таблицы, а конкретизирующие из веток останутся.

Если на нее ссылок нет - то конечно. Но можно и тригерок повесить. 8-)
По сути столбовая табла тут работает как вьюха на объединение нескольких таблиц.

← →
evvcom © (2005-12-20 15:14) [24]

> многокурсорные НД?

все-таки многокурсорные DataSet"s

← →
ANB © (2005-12-20 15:15) [25]

> Sergey13 © (20.12.05 15:06) [23]

Если вздумается довесить еще одну вектовую таблицу, то придется столбовую альтерить.

> А вот как это на ХП?..

А вот так. Организационно программистам запрещают работать с базой напрямую. Только через хранимки. Ессно, программисты это требование игнорят и понеслась . . .

← →
evvcom © (2005-12-20 15:28) [26]

> Ессно, программисты это требование игнорят и понеслась . . .

Ессно! Кому хочется сидеть, выбирать данные и подставлять по одиночке в ХП? Даже курсор написать и то лень. Гораздо проще один delete (update, insert). А чтобы не "понеслась ..." надо эту логику в триггер зашивать, а не в хранимку

← →
Sergey13 © (2005-12-20 15:29) [27]

2[25] ANB © (20.12.05 15:15)
>Если вздумается довесить еще одну вектовую таблицу, то придется столбовую альтерить.

Так потому я и уточнял
[12] Sergey13 © (20.12.05 13:46)
Если число типов сущностей невелико и постоянно

Впрочем, не такая уж и проблема, ИМХО, если таблица добавляется, то и другую изменить можно. Чай не бетонная. 8-)

← →
Курдль © (2005-12-20 15:33) [28]

> evvcom © (20.12.05 15:14) [24]
> > многокурсорные НД?
> все-таки многокурсорные DataSet"s

Повторяю: многотабличные ДатаСэты

В ADO.NET датасэт представляет собой не линейный список данных, как в Delphi, а совокупность таблиц (с полями идентификаторами и ключами), связей и правил. Т.е. ДатаСэт представляет собой фрагмент диаграммы базы данных, наполненный данными.
Например ДатаСэт, исползуемый в форме редактора физ. лица может содержать таблицу "ФИЗ_ЛИЦА", "ПАСПОРТА", "АДРЕСА". Последние связаны с первой "много-к-одному" по ключу SBJ_ID. Для них по умолчанию установлено правило каскадного обновления.

← →
Sergey13 © (2005-12-20 15:42) [29]

2[28] Курдль © (20.12.05 15:33)
> В ADO.NET датасэт представляет собой не линейный список данных, как в Delphi
Так и в Делфи можно замутить вроде.

> Например ДатаСэт, исползуемый в форме редактора физ. лица
> может содержать таблицу "ФИЗ_ЛИЦА", "ПАСПОРТА", "АДРЕСА".

Ну так в Оракле это NestedTables, и ODAC это тоже поддерживает.

> В ADO.NET датасэт представляет собой не линейный список
> данных, как в Delphi, а совокупность таблиц

ммммм... В Delphi нет таблиц данных (в смысле СУБД), а если говорить, про данные (не СУБД) в Delphi, то они тоже не все линейные. Или ты про BDE? Но это уже и не Delphi. :)

> evvcom © (20.12.05 15:43) [30]
> Ну так в Оракле это NestedTables, и ODAC это тоже поддерживает.

Это другое. Какая разница, что поддерживает оракл, если это не поддерживает среда?

В "Visual Studio .NET" (Думаю и в Delphi 8) ДатаСэты не просто "перетаскиваются на форму" и получают неоходимые свойства и т.п., а перед этим создаются разработчиком, как новый класс - наследник от System.Data.DataSet. Т.е. рисуются в графической оболочке VS, как диаграмма данных (таблица - поля - ключи - связь таблица поля - внешние ключи, констрэйнты и т.д.). После этого они именуются и хранятся в проекте отдельными файлами. Если форма будет использовать такой ДатаСэт, то в дизайн-тайме он "привязывается" к форме. Это значит, что в методе инициализации формы будет создан нужный ДатаСэт и его поля (таблицы) будут привязаны (binding) к соотв. элементам отображения и управления данными.

> ммммм... В Delphi нет таблиц данных (в смысле СУБД), а если
> говорить, про данные (не СУБД) в Delphi, то они тоже не
> все линейные. Или ты про BDE? Но это уже и не Delphi. :)

Я имел в виду линейность данных типа "запись1, запись2, запись3 ... записьN".

Иерархия классов.

1.Налогоплательщик.
1.1 Налогоплательщик->Юрик
1.2 Налогоплательщик->Физик

ANB © (20.12.05 14:09) [15]
>> поэтому опасения, что при удалении из подчиненной главная
>> запись осиротеет, имхо, надуманны
> Если бы. Как только повесишь ссылочную целостность на хранимки
Где я предлагал на хранимки что-то весить? Нормальный FK сделай с каскадным удалением (если надо).

Создать иерархию классов в оракле и одну таблицу основанную на базовом классе. В ней будут и те и эти.

А... Т.е. то что реализовано в серверных СУБД (в частности Oracle) теперь еще и на клиента вытащили? Ну в смысле структуру. Ну не зря, наверное.

> Я имел в виду линейность данных типа "запись1, запись2,
> запись3 ... записьN".

тогда

> В Delphi нет таблиц данных (в смысле СУБД)

А если говорить про то, что ты видел или не видел, то NestedTables как раз и есть эти вложенные таблицы. И тут уже линейности нет.
Просто ты думал о "среде", а говорил об "отсутствии нелинейности".

> Какая разница, что поддерживает оракл, если это не поддерживает
> среда?

"Среда"-то поддерживает, просто не в том виде и не том, может быть, объеме.

> Нормальный FK сделай с каскадным удалением (если надо).

Тогда 2 FK (друг на друга) :) Разговор был о том, что если в главной удаляешь, то в подчиненной естественно каскадно записи удалятся, а если наоборот? Если в подчиненной грохнуть, то в главной-то останутся "висяки".

> evvcom © (20.12.05 16:29) [35]
> А... Т.е. то что реализовано в серверных СУБД (в частности
> Oracle) теперь еще и на клиента вытащили? Ну в смысле структуру.
> Ну не зря, наверное.

Еще как не зря! Я прочувствовал, как начал работать с трехзвенками, где клиент удаленный. Теперь можно заполнить необходимую структуру данных на клиенте не опасаясь скрытых ошибок (их выявит сам ДатаСэт на этапе заполнения), и отправить эти данные на сервер, будучи уверенным, что с ними все Ок.
Еще один плюс многотабличных датасэтов - возможность передавать данные "всем скопом" через механизм .NET Remoting.
Т.е. пишешь метод удаленного объекта, который принимает параметром (или возвращает) такой датасэт и все! Данные ходят между доменами, строго придерживаясь описанной структуры.

> Теперь можно заполнить необходимую структуру данных на клиенте
> не опасаясь скрытых ошибок (их выявит сам ДатаСэт на этапе
> заполнения), и отправить эти данные на сервер, будучи уверенным,
> что с ними все Ок

А если в это время кто то правит справочник ?
Имхо, такой проверятельный автомат можно и на делфи + одак замутить. Плотной работы максимум на месяц. Зато все преимущества одака перед адо сохраняться.

> можно заполнить необходимую структуру данных на клиенте
> не опасаясь скрытых ошибок (их выявит сам ДатаСэт на этапе
> заполнения)

А если в "ДатаСэт" самом ошибка?

> ANB © (20.12.05 17:03) [38]
> А если в это время кто то правит справочник ?

Тогда уже СУБД ругнется, если что-то не так. Не в этом дело.

> Имхо, такой проверятельный автомат можно и на делфи + одак
> замутить. Плотной работы максимум на месяц. Зато все преимущества
> одака перед адо сохраняться.

ODAC (DOA), ADO - это уже все не важно. Замутить тоже ничего не надо.
ADO.NET - это уже новая ступень эволюции. При всем моем ... э-э-э... скептическом отношении к Microsoft, здесь я снимаю шляпу. Сравнивать невозможно. К Delphi я возвращаюсь только для создания очень простых мимолетных проектов. Все остальное - VS.NET И вряд ли теперь что-то изменится...

> evvcom © (20.12.05 17:18) [39]
> А если в "ДатаСэт" самом ошибка?

ДатаСэты тоже компилятся и многие ошибки в них выявляются на этапе создания. Однако, я не могу себе представить ошибку при создании такого ДатаСэта, если его "рисуют" прямо с ER-модели БД. Некоторые CASE-интрументы умеют создвавть ДатаСэты прямо "не отходя от кассы" (но я не пробовал).

Как лучше сделать ссылку на 2 сущности ? (Oracle и MS SQL) Найти похожие ветки