Здравствуйте. Нужен совет касательно оптимальной структуры БД.
Задача следующая (описываю без привязки к конкретной СУБД, названия пишу по-русски, чтобы было понятно):

Имеются таблицы:
Организация
id
id юридического адреса
id фактического адреса
id почтового адреса
...

Объект (примечание: это любой магазин, здание, офис, принадлежащий некоторой организации и указываемый в телефонном справочнике отдельной строкой)
id
id адреса
...

Отдел (примечание: каждый объект может иметь несколько отделов, причём отдел может находиться по другому адресу, нежели сам объект - бывает и такое)
id
id адреса
...

Адрес
id
тип
Улица
№ дома
...

Здесь возможны 2 варианта связей между организациями/объектами/отделами и адресами:
1. Каждая организация, объект и отдел имеют поле с идентификатором адреса.
2. Организации, объекты и отделы идентификаторов адресов не имеют, а таблица с адресами содержит поля с идентификаторами организации, объекта и отдела, которые расположены по данному адресу:
Адрес
id
id организации
id объекта
id отдела
Улица
№ дома
...

Какой из данных вариантов является более правильным ?
Полагаем, что:
1. Даже если 2 организации или объекта расположены по одному адресу, то в таблице Адрес для них выделяются разные строки.
2. Во 2-м варианте реализации для каждой записи может быть заполнено только одно из полей: "id организации", "id объекта", либо "id отдела".

Это сообщение отредактировал(а) CyraxZ - 2.7.2012, 10:00

Это предположение низводит на нет и без того спорный профит от выделения адресов в отдельную сущность.

Это сообщение отредактировал(а) Zloxa - 28.6.2012, 15:24

Дело в том, что таблица Адрес, помимо собственно адресных полей (улица, дом, корпус и т.п.) имеет ещё комментарии для справочника - комментарий к улице, комментарий к номеру дома, комментарий к офису. Вот эти комментарии в большинстве случаев как раз и будут различны даже в том случае, если организации/объекты расположены по одному адресу.
Не хотел усложнять структуру...

Zloxa, так какой Ваш вариант ?

Это сообщение отредактировал(а) CyraxZ - 28.6.2012, 15:28

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

Например в каких либо геосервисах, вроде гуглмапс, выделение его как сущности имеет смысл. По адресу предполагается поиск организаций, расположенных в непосредственной близости к нему. Адрес может жить и сам по себе, без привязки объектов к нему.

Если же адреса не являются самостоятельной сущностью, нафига ее выделять?

Добавлено @ 15:33

Так то вобще мне пофигу. Задача стоит не передо мной, отвечать не мне.

Ну так и привязывайте коментарий к тому объекту, который подлежит коментированию.

Это сообщение отредактировал(а) Zloxa - 28.6.2012, 15:35

Но если этого не сделать, то в таблицах "Организация", "Объект", "Отдел" придётся дублировать по 20 записей (именно столько записей у меня сейчас в таблице "Адрес", включая номер копуса, владение, номер офиса, подъезд, этаж, идентификатор ближайшей остановки, идентификатор микрорайона + комментарии ко многим полям).
Ну а такое масштабное дублирование, имхо, недопустимо.

Добавлено @ 15:47

Здесь опять придётся в таблицах "Организация", "Объект", "Отдел" дублировать 3 поля-комментария: к расположению здания, к расположению офиса внутри здания и общий комментарий к адресу.
Дополнительно придётся дублировать поля-статусы (для многих полей в таблицах предусмотрены состояния актуальности данных: "Не проверено", "Проверено", "Уточнить". Т.е. для поля с номером дома в таблице "Адрес" имеется поле состояния номера дома - то же самое для улицы, номера офиса и др. полей. И все эти поля-состояния будут различны даже для тех организаций, которые расположены по одному адресу). 
В итоге в таблицах "Организация", "Объект", "Отдел" придётся дублировать 3 поля-комментария и с десяток полей-состояний.

Это сообщение отредактировал(а) CyraxZ - 28.6.2012, 15:54

Т.е. дублировать в строках- нормально. В столбцах - плохо?   

Вам действительно нужно именно 20 полей?
Вы производите отбор по каждому/любому из 20?
На основе, информации, скажем, о номере корпуса, ваша система будет принимать бизнесс значимые решения?
С какой целью вы разделили адрес?

В википедии был хорший пример понятия атомарность. Так и увас, подумайте, если поменять местами номер офиса и подъезд, станут ли адреса разными?

Добавлено через 10 минут и 24 секунды

Вобще, да... это тезис к разбиению  

Добавлено через 13 минут и 36 секунд

Я совершенно запутался и не могу осилить. Вы сейчас рассуждаете о понятиях "коментарий", "статус", "состояние", которые не имеют никакого отношения к понятию "адрес". 

Это сообщение отредактировал(а) Zloxa - 28.6.2012, 15:56

Вобще нет. Определение атормарности позволяет ответить на вопрос может ли атрибут быть разделен, но не следует ли это делать.

Мой хороший знакомый в таких случаях любит говорить, мол "я могу насрать посередине офиса, это же не значит что мне это следует это делать"    

Да, все 20. Комментарии и статусы нужны для справочника.


Будет:
1. В режиме сортировки "По адресам" слева на форме у меня будет построено дерево объектов, сгруппированных по адресам:
Город 1
- улица 1
-- дом №3
--- объект 1
--- объект 2
--- объект 3
-- дом №3 к.1
--- объект 4
- улица 2
...
Здесь необходимо будет осуществлять поиск/отбор по конкретным полям, состявляющих адрес.

2. Становится возможным контроль данных:
- улица должна быть введена только та, что имеется в справочнике улиц (справочник улиц формируется заранее)
- номера домов, корпусов, владений, офисов, подъездов должны иметь числовой тип данных и лежать в определённом диапазоне

3. Разделение адреса на отдельные поля даёт возможность помечать флагами "Не проверено", "Проверено", "Уточнить" отдельные фрагменты адреса. Например, улица известна (имеет статус "Проверено"), а номер дома требует уточнения (имеет статус "Уточнить"). Без разделения такую информацию в БД хранить не будет возможности.


Ну как же не имеют ?
Комментарии идут к фрагментам адреса: один комментарий к фрагменту "улица-дом-корпус", другой - к фрагменту "этаж, офис". Третий - альтернативная локализация объекта (например, в "3 километрах к северу от такого-то лесничества" - для объектов с отстутсвующим/неизвестным адресом).
Поля-статусы как раз и позволяют отметить флагом фрагменты адреса, которые следут уточнить (неизвестным может быть не весь адрес, а только номер дома или номер офиса и т.д.).

Это сообщение отредактировал(а) CyraxZ - 28.6.2012, 18:04

Вернёмся к сабжевому вопросу.

Если реализовать 1-й вариант (сущности "Организация", "Объект" и "Отдел" имеют поле "id адреса"), то установка правила CASCADE DELETE приведёт к тому, что при удалении адреса будут удалены все организации, расположенные по этому адресу. Этого допустить нельзя. Ну а правило, по которому при удалении организации/объекта/отдела будут удаляться их адреса, на уровне СУБД реализовать не получится (триггеры не в счёт, так как в Access их нет).

Если реализовать 2-й вариант (сущность "Адрес" имеет поля "id организации", "id объекта" и "id отдела"), то установка правила CASCADE DELETE приведёт к тому, что при удалении организации/объекта/отдела соответствующие адреса будут удалены автоматически - это то, что нужно.
Собственно, по этой причине я пока и остановился на втором варианте.

Это сообщение отредактировал(а) CyraxZ - 28.6.2012, 20:17

каскадные операции - зло 


каскадные операции - зло 

Почему ?

На этапе разработки, они создают ощущение облегчения жизни.

В процессе же эксплуатации, при модернизации они постоянно приводят к вопросу "куда делись мои данные".

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

То же самое и при развитии системы. Допустим, через три - пять лет успешной эксплуатации написанной тобой системы, тебе потребовалось ее дорабоать. В уме каскадные делиты ты уже не держишь. С успехом тестишся на тестовом окружении, а при переезде на прод встаешь перед вопросом "куда делись мои данные".

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

Zloxa, это общепринятое мнение ?
Т.е. другие специалисты с Вами согласны ?

Это сообщение отредактировал(а) CyraxZ - 29.6.2012, 10:46

Те, кому я помогал объяснить куда делись их данные, определенно - согласны   

Т.е. предлагаете для всех связей:
а) оставить включенной опцию обеспечения ссылочной целостности
б) убрать правила каскадного удаления
в) оставить правила каскадного обновления
Так ?

На самом деле каскадные операции накладывают еще и ряд ограничений на манипуляции с таблицами. Тут на разных платформах по разному.

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

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

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

Че там в акцессе, есть ли подводные камни - не зна .

Добавлено через 5 минут и 8 секунд

Нет не так. Я против каскадов полностью.

Каскадные обновления - вообще странная операция. В проектируемой вами системе действительно есть процессы, требующие модификации значения PK? Если да, вероятно, что-то не то с дизайном. У меня лишь из пальца высасываются примеры, кода это действительно может быть нужно, и те примеры - не бесспорны.

В Access'е слежки за рекурсивными каскадами нет. Триггеров тоже нет.
Для каждой связи есть только 3 опции:
- обеспечение ссылочной целостности (запрет удаления или изменения, если в связанной таблице есть записи с данным значением FK)
- каскадное обновление
- каскадное удаление


А обеспечение ссылочной целостности - против этого, наверное, не будете ?


У меня - нет, поскольку все PK у меня - числовые идентификаторы.
Но как минимум, в учебных БД ключами могут быть и имена. В этом случае каскадное обновление пригодится.

Это я в курсе. Я не вкурсе не очевидных, глубинных проблем.  


Здесь я всеми конечностями за.
Порой, даже черезчур.   
Коллеги часто жалуются, что присунуть данные в создаваемые мною структуры, порой, бывает весьма проблематично. Уж больно я склонен утянуть все ограничениями. Но тут я работаю над собой. 


Значит вам эта опция не нужна, бритвой Оккамы ее безжалостно отсекем. 


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


Это сообщение отредактировал(а) Zloxa - 29.6.2012, 20:23

Zloxa, как насчёт 2 вариантов реализации связи между таблицей Адрес и таблицей организации/объекта/отдела ?
Какой из двух вариантов связи Вы бы реализовали в случае, когда адрес выделен в отдельную таблицу ?
а) сущности "Организация", "Объект" и "Отдел" имеют поле "id адреса"
б) сущность "Адрес" имеет поля "id организации", "id объекта" и "id отдела"

P.S. Гляньте ещё вот эту тему, если занимаетесь программированием.

Это сообщение отредактировал(а) CyraxZ - 1.7.2012, 22:58

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