[SQL] тупняк (теория построения БД)

kill-still

таблица В ссылается на А
таблица сВ ссылается на сА
таблица А ссылается на сА
таблица В ссылается на сВ
все четыре таблицы помимо ссылок содержат также и данные.
вот так:


А -> сА

/\   /\

|    |

В -> сВ

при этом не должно быть так, что в В есть такие записи, которые через сВ ссылаются на такую запись в сА, которая отлична от сА, на которую этот элемент ссылается через А.
собственно это косяк из-за которого я полез разбираться в это г, и его надо поправить.
у меня 2 вопроса:
1) как правильно с точки зрения НФ и прочих постулатов теории БД делаются такие вещи?
2) как минимально затронув эту конструкцию переделать так, чтобы больше таких флуктуаций не возникало?

alexkravchuk

при этом не должно быть так, что в В есть такие записи, которые через сВ ссылаются на такую запись в сА, которая отлична от сА, на которую этот элемент ссылается через А.
   Или я торможу, или это значит, что в cB и A есть дублирующиеся данные? Чего по принципам НФ быть не должно...

katrin2201

Судя по условию, у тебя между B и cA связь много-к-одному.
Поэтому предлагаю залинковать В напрямую на сА, а А и сВ залинковать на В.
Будет косяк, если существуют строчки в А и сВ, у которых нету ссылающихся на них строчек из В. Тогда можно, например, подумать о создании заглушечных строчек в В для таких случаев. Скорее всего, это будет даже осмысленно в терминах моделируемой системы.

kill-still

если это поможет представить картину
сА - услуга, сВ - вариант предоставления услуги А - документ по контролю за услугой (на одну услугу может быть несколько док-ов В - строка документа (документ может охватывать не все варианты)
надеюсь это не подпадает под нарушение НДА )
у меня получается только если делать составной PK, часть из которого ссылка на "родителя"

kill-still

у меня получается только если делать составной PK, часть из которого ссылка на "родителя"
а не пля, не получается - на одно поле два констрейна же не повесишь :(

katrin2201

А какова взаимосвязь между вариантом предоставления и документом по контролю?
В смысле почему строчки документа завязаны на вариант предоставления услуги? Это выглядит странно.

kill-still

оябу? :(
я в этой предметной области пока вообще полный ноль.
повторюсь:
все четыре таблицы помимо ссылок содержат также и данные.
связка А+В1+В2 как бы расширяет связку сА+сВ1+сВ2+сВ3, при этом на одну может быть нанизано несколько.
при этом сА и сВ одновременно являются ещё и справочниками, и много где помимо этого места используются.
руки бы конечно поотрывать за такие солюшены. :(

katrin2201

связка А+Б1+Б2 как бы расширяет связку сА+сБ1+сБ2+сБ3, при этом на одну может быть нанизано несколько.
Ты ввел какиетто новые обозначения, и я не понял твою мысль.
Поясню свой вопрос. В исходной модели получается, что разные строчки одного документа могут ссылаться на разные варианты предоставления услуги. Звучит это довольно бредово, так как строчки документа в моем понимании не есть самостоятельная сущность, и в худшем случае документ бы ссылался на вариант предоставления.
Тогда их возможно получилось бы линейно выстроить.

kill-still

опечатался чуток (Б=В).
я имел в виду запись из таблицы А + ссылающиеся на эту запись записи из В.
аналогично сА и сВ.

katrin2201

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

kill-still

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

hwh2010

я нихрена не понял как там с документами и услугами
но квадратик из верхнего поста представил себе так:
B — жилые помещения, А — почтовые отделения, сB — улицы, сА — регионы
это та же задача?
можешь в этих терминах рассказать своё решение?

alexkravchuk

если это поможет представить картину
сА - услуга, сВ - вариант предоставления услуги А - документ по контролю за услугой (на одну услугу может быть несколько док-ов В - строка документа (документ может охватывать не все варианты)
А -> сА

/\   /\

|    |

Б -> сБ

ничего не понятно всё равно. Может быть и нет никакой ошибки... Ну как пример:
Пусть в cА — вид услуг, там Алиса, Катерина, Антуанетта и т.д.
В Б — запись вида "в 23:20 была оказана услуга вариантом представления сБ, на что имеются документы в А".
Задача, узнать, кто оказывал услугу.
Смотрим таблицу А, находим там, что для этой услуги есть чеки об оказании, на 200 евро, со ссылкой на сА, на Алису.
Смотрим таблицу сБ, там есть услуга "доставка шампанского на дом", со ссылкой на сА, но уже на Катерину.
Вопрос, так кто же работал, Алиса, или Катерина?
Почему сбой мог произойти — понятно, скажем, когда заказывалась услуга, планировалось, что её будет оказывать Катерина, но по факту получилось, что оказывала Алиса. Вполне себе нормальная ситуация.
В данном случае даже нельзя сказать, что грубая ошибка в проектировании есть. То есть нет проблемы в синхронизации данных. Просто цепочка Б --> сБ --> сА некорректна, она не отвечает на вопрос "кто работал", она отвечает на вопрос "кто должен был работать".
Вообще, IHMO, надо в структуре базы детально разбираться, чтобы понять, это база криво спроектирована, либо же она просто криво задокументирована.

katrin2201

Вроде да.
Рвешь все связи, делаешь табличку для тернарной связи между регион-отделение-улица. На эту табличку линкуешь дома.
Соответственно теперь, чтобы убедиться в корректности связей, достаточно одной новой таблицы. Проверяем что у всех строчек с заданным отделением одинаковый регион, и, аналогично, у всех улиц. Это, конечно, на дефолтных констрейнтах не.
Вообще, вся загвоздка в том, как реляционно красиво представить такую структуру:
  Регион, у которого много отделений, много улиц. Множества отделений и улиц между разными регионами не пересекаются. Дом может выбирать улицы и отделения только из одно и того же региона.
В принципе, можно оставить то, как есть сейчас (регион один-ко-многим отделениям, регион один-ко-многим улицам порвать связь между домами и всем остальным. Создать вьюху из примерно такого селекта:
select
  stable_unique_id(Region.id, Post.id, Street.id Region.id, Post.id, Street.id
from
  Region
  join Post on Post.RegionId = Region.id
  join Street on Street.ReionId = Region.id
и линковать дома на эту вьху. Тогда никакой валидации вьюхи делать не надо будет.

kill-still

я нихрена не понял как там с документами и услугами
ничего не понятно всё равно.
придумал такой вот пример:
сА - Банк (ИД(PK название, БИК, КПП и т.д.)
банк может обслуживать организации:
А - Организация (ИД(PK название, банк_ИД(FK год_основания, уставной_капитал и т.д.)
в банке есть банковские ячейки:
сВ - Ячейка (ИД(PK банк_ИД(FK объём_ячейки, на_какой_срок_арендована и т.д.)
каждый сотрудник организации может работать только в одной организации и иметь ячейку в банке, который обслуживает организацию (и только в нём или же не иметь.
В - Сотрудник (ИД(РК организация_ИД(FK ячейка_ИД(FK имя, фамилия, зарплата, рост и т.д.)

kill-still

stable_unique_id
Это что за зверь? не гуглится что-то.

kill-still

а что? вроде годная идея:


CREATE OR REPLACE VIEW MyView (О_ИД, Я_ИД)

AS

SELECT o.ИД AS , я.ИД AS Я_ИД

  FROM     Банк б 

         JOIN 

           Организация o 

         ON o.банк_ИД = б.ИД

       JOIN 

         Ячейка я 

       ON я.банк_ИД = б.ИД





ALTER TABLE Сотрудник ADD

    CONSTRAINT fk_cотрудник_яч_орг FOREIGN KEY (организация_ИД, ячейка_ИД) REFERENCES MyView (О_ИД, Я_ИД)

katrin2201

Выдуманная мной хрень. Функция от трех намберов, которая выдает на выходе стабильные уникальные идентификаторы.
Короче, это просто композитный ключ, на который можно слинковаться из домов/строчек. В примере выше ты без этого обошелся =)

katrin2201

Если твоя БД это проглотит, и коллеги тебя не побьют, то в общем да =)

kill-still

ещё не видел таких БД, которые не умели бы ссылаться сразу двумя полями.

kill-still

показал решение коллегам, не оценили.
от так всегда :( :cry2:

kill-still

Таки случилось. У клиента снова посыпались данные, зашевелились.
но тут случилось ОПАНЬКИ: You can't reference a view in a foreign key.
Есть у кого идеи? :(

katrin2201

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

6yrop

1) как правильно с точки зрения НФ и прочих постулатов теории БД делаются такие вещи?
в описаной тобой схеме с постулатами противоречий нет.
 
2) как минимально затронув эту конструкцию переделать так, чтобы больше таких флуктуаций не возникало?

В принципе это нафиг не нужно, по любому надо чинить приложение, которое пишет такие кривые данные. Но если все же хочется извращений — контроля со стороны базы, то можно добавить поле В.cAId. Повесить составные уникальные индексы на таблицах A и cB, и сделать в таблице B два FK на эти уникальные индексы (например, MS SQL Server такое позволяет).

6yrop

добавить поле В.cAId

если надо, то можно привести обоснование из предметной области почему это поле не противоречит нормальным формам.

kill-still

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

kill-still

Я тебя правильно понял, что ты предлагаешь:
 
 ALTER TABLE В 

  ADD cAId numeric(15)



ALTER TABLE В 

  ADD CONSTRAINT fk_b_A FOREIGN KEY (Aid, cAId) REFERENCES A(id, cAid)



ALTER TABLE В 

  ADD CONSTRAINT fk_b_сВ FOREIGN KEY (сВid, cAId) REFERENCES сВ(id, cAid)

т.е. на cAId будет два FK повешено. так можно?
UPD: и правда можно на одно поле два FK повесить.
Тогда вопрос такой: как в A и сВ создать индекс уникальный по этим полям id, cAid (чтобы было на что ссылаться FK)

6yrop

Я тебя правильно понял
да, правильно, FK, естественно, на таблице B (у меня была опечатка).
Тогда вопрос такой: как в A и сВ создать индекс уникальный по этим полям id, cAid (чтобы было на что ссылаться FK)
ну как-нибудь так:
 


ALTER TABLE A ADD CONSTRAINT AIndex UNIQUE(AId, cAId)  

ALTER TABLE cB ADD CONSTRAINT cBIndex UNIQUE(cBId, cAId)

Проще такое мышкой сделать, а потом скрипт посмотреть. У меня сейчас, к сожалению, нет доступа к SQL Server-у.
Вообще, вот отсюда и далее по ссылкам:
 
A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
http://msdn.microsoft.com/en-us/library/ms191166.aspx
  

kill-still

спасибо, буду пробовать.
только MS SQL S. у нас только раньше поддерживался, и мб в будучем отдалённом.

prehack

придумал такой вот пример:
сА - Банк (ИД(PK название, БИК, КПП и т.д.)
банк может обслуживать организации:
А - Организация (ИД(PK название, банк_ИД(FK год_основания, уставной_капитал и т.д.)
в банке есть банковские ячейки:
сВ - Ячейка (ИД(PK банк_ИД(FK объём_ячейки, на_какой_срок_арендована и т.д.)
каждый сотрудник организации может работать только в одной организации и иметь ячейку в банке, который обслуживает организацию (и только в нём или же не иметь.
В - Сотрудник (ИД(РК организация_ИД(FK ячейка_ИД(FK имя, фамилия, зарплата, рост и т.д.)
У нас это выглядит так:
 

         А (организация)           Поля(A): A_ID;

              /\

              |

         B (сотрудник)              Поля(B): B_ID; cA__OID; A__OID;

         /\         |

         |          \/ 

  cB(ячейка)  -->  cA (банк)      Поля(cA): cA_ID;



Поля(cB):cB_ID; B__OID; cA__OID

kill-still

У вас в одной организации сотрудники могут иметь ячейки в разных банках, что опять-таки противоречит :)
Оставить комментарий
Имя или ник:
Комментарий: