Tomsk Sysadmins Forum
Windows => Программирование => Topic started by: CRonaldo on February 09, 2009, 14:23:40
-
Здравствуйте!
Имею таблицу Людей:
CREATE TABLE PEOPLES (
ID_PEOPLE PRIMARY_KEY NOT NULL /* PRIMARY_KEY = INTEGER NOT NULL */,
FIO FIO NOT NULL /* FIO = VARCHAR(80) */,
BIRTH_DAY DATE_YESNULL /* DATE_YESNULL = DATE CHECK (value <= current_date or value is null) */,
ADRES ADRES /* ADRES = VARCHAR(50) */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE PEOPLES ADD CONSTRAINT PK_PEOPLES PRIMARY KEY (ID_PEOPLE);В ней раньше были логические поля которые определяли социальный статус. Меня попросили добавить ещё некоторые категории соц. статуса, потом ещё, тут я решил, что бы не менять каждый раз структуру БД, лучше заведу отдельную табличку соц. статусов:
CREATE TABLE SOC_GROUPS (
ID_SOC_GR PRIMARY_KEY NOT NULL /* PRIMARY_KEY = INTEGER NOT NULL */,
SOC_GR VARCHAR(50) NOT NULL
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE SOC_GROUPS ADD CONSTRAINT PK_SOC_GROUPS PRIMARY KEY (ID_SOC_GR);и ещё одну табличку для связи 1 ко многим с людьми:
CREATE TABLE P_SOC_GR (
PEOPLE_ID FOREIGN_KEY_NOTNULL NOT NULL /* FOREIGN_KEY_NOTNULL = INTEGER NOT NULL */,
SOC_GR_ID FOREIGN_KEY_NOTNULL NOT NULL /* FOREIGN_KEY_NOTNULL = INTEGER NOT NULL */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE P_SOC_GR ADD CONSTRAINT PK_P_SOC_GR PRIMARY KEY (PEOPLE_ID, SOC_GR_ID);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE P_SOC_GR ADD CONSTRAINT FK_P_SOC_GR_PEOPLES FOREIGN KEY (PEOPLE_ID) REFERENCES PEOPLES (ID_PEOPLE) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE P_SOC_GR ADD CONSTRAINT FK_P_SOC_GR_SOC_GROUPS FOREIGN KEY (SOC_GR_ID) REFERENCES SOC_GROUPS (ID_SOC_GR) ON UPDATE CASCADE;После этого встала проблема, если есть к примеру социальный статус "Гражданский брак" и "Официальный брак", то как мне сделать так, чтобы можно было только одно из этих занести в таблицу P_SOC_GR? Сейчас вот нашел вариант сделать в справочнике соц. статусов ещё одно поле, где будут по категориям соц. статусы разбиты, но что-то не пойму как мне всё это цепануть грамотно к P_SOC_GR? Может вообще структура корявая?
-
Не слишком шарю в проектировании БД. Но наверное вы идете по правильному пути. Надо завести табличку категорий соц. статусов: т.е. в ней будет ид_категории и название категории. А в табличку соц. статусов добавить поле под ид_категорий.
-
После этого встала проблема, если есть к примеру социальный статус "Гражданский брак" и "Официальный брак", то как мне сделать так, чтобы можно было только одно из этих занести в таблицу P_SOC_GR? Сейчас вот нашел вариант сделать в справочнике соц. статусов ещё одно поле, где будут по категориям соц. статусы разбиты, но что-то не пойму как мне всё это цепануть грамотно к P_SOC_GR? Может вообще структура корявая?
Вы правильно поняли что нужна разбивка по категориям. Единственное, полем тут не обойтись, нужно сделать еще одну таблицу:
CREATE TABLE SOC_GROUPS_CATEGORY (
ID_CATEGORY INTEGER NOT NULL,
ID_SOC_GR INTEGER NOT NULL
);
ALTER TABLE SOC_GROUPS_CATEGORY ADD CONSTRAINT PK_SOC_GROUPS_CATEGORY PRIMARY KEY (ID_CATEGORY, ID_SOC_GR);
ALTER TABLE SOC_GROUPS_CATEGORY ADD CONSTRAINT FK_SOC_GROUPS_CATEGORY_1 FOREIGN KEY (ID_SOC_GR) REFERENCES SOC_GROUPS (ID_SOC_GR);
И сделать триггер на событие before insert и before update таблицы P_SOC_GR:
-- Сначала создадим исключение
CREATE EXCEPTION EX_HAS_CATEGORY 'Человек уже приандлежит социальной группе с такой категорией';
-- И собственно триггер
CREATE TRIGGER p_soc_gr_bi0 FOR p_soc_gr
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
-- Проверяем есть ли у человека какая-нибудь социальная группа
if (exists (select * from P_SOC_GR where PEOPLE_ID = new.PEOPLE_ID)) then
begin
-- Проверяем принадлежит ли новая социальная группа категории имеющейся
if (exists (select ID_SOC_GR from SOC_GROUPS_CATEGORY where ID_SOC_GR = new.SOC_GR_ID)) then
exception EX_HAS_CATEGORY;
end
end
-
Табличка вроде толковая получилась, я как-то не догадался, только вот триггер не могу сообразить т.к. этот работать не будет.
-- Проверяем есть ли у человека какая-нибудь социальная группа
if (exists (select * from P_SOC_GR where PEOPLE_ID = new.PEOPLE_ID)) then
Эта часть по-моему лишняя, только сервак будет нагружать лишний раз, проще каждый раз проверять таблицу категорий, где не так уж и много записей, нежели каждый раз проверять таблицу с соц. статусами человека.
-- Проверяем принадлежит ли новая социальная группа категории имеющейся
if (exists (select ID_SOC_GR from SOC_GROUPS_CATEGORY where ID_SOC_GR = new.SOC_GR_ID)) then
exception EX_HAS_CATEGORY;
А здесь же она будет постоянно находить запись, т.к. здесь всем соц. статусам категория приписана. В итоге не даст ничего занести.
-
У меня всё работает Может вы (или я) не совсем правильно поняли что заносить в таблицы? Приведу как у меня:
PEOPLES
ID_REOPLE | FIO
---------
1 | Иванов
2 | Петров
3 | Сидоров
SOC_GROUPS
ID_SOC_GR | SOC_GR
---------------------------
1 | Женат
2 | Разведен
3 | Бомж
4 | Хмырь
SOC_GROUPS_CATEGORY
-------------------------------
ID_CATEGORY | ID_SOC_GR
1 | 1
1 | 2
P_SOC_GR
-------------
PEOPLE_ID | SOC_GR_ID
1 | 1
1 | 3
2 | 1
2 | 3
2 | 4
-
У меня всё работает Может вы (или я) не совсем правильно поняли что заносить в таблицы?
Да вроде всё правильно понял. Естественно в P_SOC_GR у Вас нельзя занести ещё одну запись например где PEOPLE_ID = 1 и SOC_GR_ID = 2? Если так, то всё правильно и Вы и я поняли.
В понедельник на работе ещё попробую, отпишусь получилось или нет.
-
Значит так, всё перепроверил ещё раз, ничего не меняется, а следовательно не работает.
Решил сам додумать, попыхтел помучал вымучал следующее:
Структура таблиц остаётся прежней, отдельное спасибо за это -ud-! А вот триггера я поменял:
CREATE TRIGGER P_SOC_GR_BI FOR P_SOC_GR
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (exists(
select *
from p_soc_gr psg
join soc_gr_categ sgc on (psg.soc_gr_id = sgc.soc_gr_id)
where psg.people_id = new.people_id
and sgc.soc_gr_categ_id in(
select sgc.soc_gr_categ_id
from soc_gr_categ sgc
where sgc.soc_gr_id = new.soc_gr_id
)
)
) then exception e_p_soc_gr; /*'Человек уже принадлежит социальной группе с такой категорией!'*/
end
CREATE TRIGGER P_SOC_GR_BU FOR P_SOC_GR
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (exists(
select *
from p_soc_gr psg
join soc_gr_categ sgc on (psg.soc_gr_id = sgc.soc_gr_id)
where psg.people_id = new.people_id and psg.soc_gr_id != old.soc_gr_id
and sgc.soc_gr_categ_id in(
select sgc.soc_gr_categ_id
from soc_gr_categ sgc
where sgc.soc_gr_id = new.soc_gr_id
)
)
) then exception e_p_soc_gr; /*'Человек уже принадлежит социальной группе с такой категорией!'*/
endСобственно всё. Вроде работает. Спасибо за помощь! Если найдёте косяки или варианты как оптимизировать, то буду рад рассмотреть варианты.