Ссылочная целостность - помогите разобраться на простом примере.

← →
RDA (2002-10-10 15:08) [0]

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

← →
still (2002-10-10 15:16) [1]

> как мне кажется имеет смысл вынести их отдельными справочниками
>

мыслишь правильно :)

Если юр. и почтовый адреса имеют разные параметры, то имеет смысл завести две таблицы, если же одинаковые - то достаточно одной.

> Как быть в таком случае.

А в чем конкретно проблема?

← →
Johnmen (2002-10-10 15:28) [2]

Если строго по науке, то конечно область, район, город, ... в отдельные таблицы и ссылки на них. Но это голая теория !
На практике же, когда реально работаешь с базай данных, такое слепое следование теории может привести к весьма нежелательным явлениям. Например, замедление выполнения запросов, более сложный и неудобный интерфейс пользователя и т.д.
С др.стороны, такое следование теории может полностью обеспечить однозначное выполнение запросов типа "все юр.лица в городе N".

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

← →
passm (2002-10-10 15:31) [3]

ИМХО хранить юридический и почтовый адрес лучше вместе - меньше мороки.
Но по опыту знаю что адреса - дело тонкое, поскольку пользователи имеют стойкую привычку делать опечатки. К тому же возможны сокращения. Понимаешь, к чему это может привести...
Но вобщем это не существенно, если тебе не понадобиться производить группировку (детальный поиск) по адресу/его части.
Выносить адрес (город, улица...) в отдельный справочник имеет смысл, если ты хочешь:
а. четко следить кто где проживает :), считать количество народа по одному адресу/в городе...
б. хочешь сэкономить место в БД.
Вобщем, если понадобится работать с адресом (а, б...) то желательно с этим заморочиться. В противном случае легче хранить одной строкой.

← →
MsGuns (2002-10-10 15:51) [4]

>passm © (10.10.02 15:31)

По поводу отдельного справочника адресов сказано так, что нечего добавить.

По поводу 2-х таблиц для юр. и физ. лиц. Совершенно непродуманное решение разъединять их в две таблицы. Как прикажете мне поступать с Семен Семенычем Семеновым, который частный предприниматель и одновременно и юр. лицо и физ. лицо ?
ДВАЖДЫ заносить его в две РАЗНЫЕ таблицы ? Вот туточки-то самый продвинутый юзверь не может не ошибиться и введет РАЗНЫЕ адреса.

Я бы сделал одну таблицу, а в ней ДВА поля адреса, телефона и пр. Ну и для фильтрации ввел бы еще доп поле "Категория" из 1 символа - "Ю" - ирид., "Ф" - физический, "*" - и то и другое.
(Хотя можно и без него обойтись, анализируя наличие/отсутствие соотв.адреса)

← →
RDA (2002-10-10 16:48) [5]

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

2. Адреса
-----------
Область
Район
Город
Населенный пункт
.....

И соответственно таблицы
3. Область
-----------
Название

4. Район
-----------
Название

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

Всем откликнувшимся заранее благодарен.

← →
passm (2002-10-10 16:59) [6]

RDA © (10.10.02 16:48)> в таблице Адреса будут ссылаться и другие таблицы, а не только Организации, которые тоже будут хранить там свои записи
А здесь тебе, вероятно, помогут триггеры.

← →
RDA (2002-10-10 17:04) [7]

>> passm - опечатка
на таблицу Адреса будут ссылаться и другие таблицы, а не только Организации, которые тоже будут хранить там свои записи
Но смысл понял - спасибо.

← →
RDA (2002-10-10 17:05) [8]

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

← →
MsGuns (2002-10-10 17:08) [9]

ИМХО, таблица 2 не нужна вообще, т.к. содержит поля-атрибуты собственно адреса, который есть в таблице 1.
2 стамма:
1. Линейный. Все в одном
Просто в первой таблице надо предусмотреть по каждому адресу N полей (поле области, поле района и т.д.), каждое из которых "залукапено" на соотв.спр-к. При удалении орг-ции удаляется и ее адрес,- вопрос о целостности отпадает.

Плюс: простота в программировании интерфейса, минимум таблиц.

Минусы: только по одному адресу на орг-цию, при достаточно большом объеме ВСЕ запросы на такую таблицу, включая те, в которых не запрашивается адрес, будут подтормаживать.

2. Иерархический. Master-Детал
На один ключ - организацию добавляется 2 таблицы: адреса юр. и адреса физ.лиц. Связаны по первичному ключу - организации (желательно вместо наименования исп-ть короткий уникальный идентификатор или код)

Плюс: Малые размеры физ.таблиц и, как следствие, запросы "летают", сколь угодно много адресов (телефонов и пр)

Минус: вместо одной таблицы - 3, усложненный дизайн интерфейса, обработка BeforeDelete на Гл.таблице и др.

← →
RDA (2002-10-10 17:16) [10]

>> MsGuns
А нельзя ли рассмотреть вариант когда в таблице Организации содержится два поля со ссылками на таблицу Адреса (юр и физ адреса) и по ним построены внешние ключи?

← →
passm (2002-10-10 17:18) [11]

Можно, например, завести таблицу ТИП_АДРЕСА(ID, NAME) - юр, физ. и построить таблицу АДРЕСА ОРГАНИЗАЦИИ (ИД_ОРГАНИЗАЦИИ, ТИП_АДРЕСА, ГОРОД...) а таблица 2, как сказал MsGuns © (10.10.02 17:08), тогда тебе не нужна.

← →
MsGuns (2002-10-10 17:33) [12]

RDA © (10.10.02 17:16)
>> MsGuns
>А нельзя ли рассмотреть вариант когда в таблице Организации >содержится два поля со ссылками на таблицу Адреса (юр и физ >адреса) и по ним построены внешние ключи?

Я же Вам объяснил в >MsGuns © (10.10.02 17:08)
Создавать отдельную таблицу, содержащую собственно адреса, придумывать для нее некий уникальный идентификатор, потом связываться с основной таблицей организаций по этому суррогатному идентификатору и при этом игнорировать напрашивающуюся реляцию - это путь, ведущий к геморрою. И поддержка ссылочной целостности в такой базе - это семечки по сравнению с другими траблами, которые неизбежно возникнут после первых недель РЕАЛЬНОЙ работы юзеров с такой программой и такой БД.
И главное, зачем. Я бы понял еще, если Вы пишете прогу для крупного арендодателя, у которого КОНЕЧНЫЙ список адресов и к нему привязываются организации-арендаторы. Но проблема другая, не так ли ?

← →
RDA (2002-10-10 17:50) [13]

MsGuns © (10.10.02 17:08)
1. Минусы перечисленны - не подходит.
2. В процессе эксплуатации может возникнуть необходимость ввода дополнительной таблицы которая должна будет хранить свои записи (адреса) в таблице Адреса. Деталь разве может ссылаться на два мастера - уверен что нет. Отпадает. А так скорее все и будет, не заводить же еще одну таблицу адресов для каждого дополнительного объекта. К примеру сотрудник организации - его домашний адрес, где прописан, или структурное подразделение данной организации (филиал) расположенное по отдельному адресу.

Есть ли еще какие-либо варианты? Очень надо.
По поводу моего варианты что там за геморой-то такой?
Внешний ключ на таблицу адресов. Тригером на BeforeDelete удалять запись в таблице Адреса - все.

← →
passm (2002-10-10 17:51) [14]

MsGuns © (10.10.02 17:33)> Проблема решаема. Повторюсь.
Создаем таблицу-справочник содержащую типы адресов (ID, NAME)
Создаем таблицу соответствий между организацией, типом адреса и адресом - это страна,город, улица... Где имеются ссылки на ID организации, ID типа адреса и на справочники адресов. Назавем ее CLIENT_ADDRESS (ID_CLIENT, ID_TYPE_ADDRESS, ID_COUNTRY...)
В таблице CLIENT_ADDRESS прописываем внешние ключи с каскадным удалением на таблицы организации и типом адреса и прописываем внешние ключи с опцией SET NULL на справочники адреса.
Т. о. RDA получает таблицу 2 (см. выше MsGuns © (10.10.02 17:08) ) с необходимыми ему условиями.

← →
Romkin (2002-10-10 18:01) [15]

Не понимаю, в чем проблема. Просто надо выставить иерархию, есть таблица Клиенты, в которой содержатся общие для всех клиентов поля (Краткое наименование, контактный адрес, телефон и тд) и две дочерние таблицы, для юридических и физических лиц. Получается примерно следующее:
CREATE TABLE CLIENT ( ID INTEGER NOT NULL, NAME CHAR(250), OWNER_ID INTEGER, PHONE CHAR(40), /* контактный телефон */ FLAG CHAR(1) default "U", /* U - юридическое лицо P - физическое */ NOTES VARCHAR(254), ADDRESS VARCHAR(254) /* контактный адрес */); CREATE TABLE CLIENT_PERSON ( CLIENT_ID INTEGER NOT NULL, NAME VARCHAR(100), SECONDNAME VARCHAR(100), FAMILY VARCHAR(250), SEX CHAR(1) default "M" NOT NULL, BIRTHDATE DATE, BIRTHSITY VARCHAR(100), DOC_TYPE VARCHAR(100), DOC_SER CHAR(20), DOC_NUM CHAR(20), DOC_DATE DATE, DOC_PLACE VARCHAR(254), DOC_ADDRESS VARCHAR(254), constraint FK_CLIENT_PERSON_CLIENT foreign key (CLIENT_ID) references CLIENT (ID) on delete cascade on update cascade ); CREATE TABLE CLIENT_URE ( CLIENT_ID INTEGER NOT NULL, URE_NAME VARCHAR(250), URE_ADDRESS VARCHAR(250), PHONE CHAR(40), RS CHAR(30) NOT NULL, BANK VARCHAR(250), KS CHAR(30), OTD VARCHAR(100), BIK CHAR(15), CITY CHAR(100), INN CHAR(15), OKONH CHAR(100), OKPO CHAR(15), constraint FK_CLIENT_URE_CLIENT foreign key (CLIENT_ID) references CLIENT (ID) on delete cascade on update cascade);
Первичные ключи не даю, они здесь ID & CLIENT_ID,
адреса хранятся в единых полях, но вынести их в отдельную таблицу труда не составляет, просто мне не надо
create table address ( OBJ_ID integer not null, OBJ_TYPE char(6), ID integer not null, addr_code char(1) not null, /* статус адреса - реальный, по паспорту, юридический и тд */ City varchar ... constraint PK_ADDRESS primary key (obj_ID, obj_type, ID),
и пихай в нее адреса, какие захочешь... Obj_ID + obj_type указывают на таблицу, к которой относится адрес, в obj_type можно писать для клиента, например, "CLIENT", и сделать триггер
CREATE TRIGGER CLIENT_AFTER_DELETE FOR CLIENT
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
DELETE FROM ADDRESS WHERE OBJ_ID = OLD.ID and OBJ_TYPE = "CLIENT";
END

← →
RDA (2002-10-10 18:06) [16]

passm © (10.10.02 17:51)
Создаем таблицу сотрудников с домашними адресами и пропиской и что тогда с внешними ключами? Не может деталь иметь два мастера с каскадным удалением - или может?
Ну да все равно - через день введут учет филиалов предприятий и что опять внешний ключ на них добавлять в таблицу CLIENT_ADDRESS ?

← →
sar (2002-10-10 18:10) [17]

<center>
Есть такая связь как IS_A.... возьми ER_Win(4.0).... там все написано...
</center>

← →
RDA (2002-10-10 18:18) [18]

Ссылочная целостность - помогите разобраться на простом примере. Найти похожие ветки