Author Topic: Помогите определиться со структурой БД  (Read 4510 times)

0 Members and 1 Guest are viewing this topic.

Offline CRonaldo

  • Jr. Member
  • **
  • Posts: 50
  • Karma: +0/-0
Здравствуйте!
Имею таблицу Людей:
Code: [Select]
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);
В ней раньше были логические поля которые определяли социальный статус. Меня попросили добавить ещё некоторые категории соц. статуса, потом ещё, тут я решил, что бы не менять каждый раз структуру БД, лучше заведу отдельную табличку соц. статусов:
Code: [Select]
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 ко многим с людьми:
Code: [Select]
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? Может вообще структура корявая?

Offline Diver

  • Hero Member
  • *****
  • Posts: 790
  • Karma: +2/-0
Помогите определиться со структурой БД
« Reply #1 on: February 10, 2009, 14:40:04 »
Не слишком шарю в проектировании БД. Но наверное вы идете по правильному пути. Надо завести табличку категорий соц. статусов: т.е. в ней будет ид_категории и название категории. А в табличку соц. статусов добавить поле под ид_категорий.
MCP

Offline -ud-

  • Full Member
  • ***
  • Posts: 164
  • Karma: +4/-0
    • Undeground Developing
Помогите определиться со структурой БД
« Reply #2 on: February 11, 2009, 15:46:55 »
Quote from: CRonaldo
После этого встала проблема, если есть к примеру социальный статус "Гражданский брак" и "Официальный брак", то как мне сделать так, чтобы можно было только одно из этих занести в таблицу P_SOC_GR? Сейчас вот нашел вариант сделать в справочнике соц. статусов ещё одно поле, где будут по категориям соц. статусы разбиты, но что-то не пойму как мне всё это цепануть грамотно к P_SOC_GR? Может вообще структура корявая?
Вы правильно поняли что нужна разбивка по категориям. Единственное, полем тут не обойтись, нужно сделать еще одну таблицу:

Code: [Select]
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:
Code: [Select]
-- Сначала создадим исключение
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
.

Offline CRonaldo

  • Jr. Member
  • **
  • Posts: 50
  • Karma: +0/-0
Помогите определиться со структурой БД
« Reply #3 on: February 12, 2009, 13:27:32 »
Табличка вроде толковая получилась, я как-то не догадался, только вот триггер не могу сообразить т.к. этот работать не будет.
Quote
-- Проверяем есть ли у человека какая-нибудь социальная группа
  if (exists (select * from P_SOC_GR where PEOPLE_ID = new.PEOPLE_ID)) then
Эта часть по-моему лишняя, только сервак будет нагружать лишний раз, проще каждый раз проверять таблицу категорий, где не так уж и много записей, нежели каждый раз проверять таблицу с соц. статусами человека.
Quote from: -ud-
-- Проверяем принадлежит ли новая социальная группа категории имеющейся
   if (exists (select ID_SOC_GR from SOC_GROUPS_CATEGORY where ID_SOC_GR = new.SOC_GR_ID)) then
      exception EX_HAS_CATEGORY;
А здесь же она будет постоянно находить запись, т.к. здесь всем соц. статусам категория приписана. В итоге не даст ничего занести.

Offline -ud-

  • Full Member
  • ***
  • Posts: 164
  • Karma: +4/-0
    • Undeground Developing
Помогите определиться со структурой БД
« Reply #4 on: February 14, 2009, 13:03:27 »
У меня всё работает  Может вы (или я) не совсем правильно поняли что заносить в таблицы? Приведу как у меня:
Code: [Select]
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
.

Offline CRonaldo

  • Jr. Member
  • **
  • Posts: 50
  • Karma: +0/-0
Помогите определиться со структурой БД
« Reply #5 on: February 16, 2009, 00:23:53 »
Quote from: -ud-
У меня всё работает  Может вы (или я) не совсем правильно поняли что заносить в таблицы?
Да вроде всё правильно понял. Естественно в P_SOC_GR у Вас нельзя занести ещё одну запись например где PEOPLE_ID = 1 и SOC_GR_ID = 2? Если так, то всё правильно и Вы и я поняли.
В понедельник на работе ещё попробую, отпишусь получилось или нет.
« Last Edit: February 20, 2009, 10:33:24 by CRonaldo »

Offline CRonaldo

  • Jr. Member
  • **
  • Posts: 50
  • Karma: +0/-0
Помогите определиться со структурой БД
« Reply #6 on: February 20, 2009, 10:32:40 »
Значит так, всё перепроверил ещё раз, ничего не меняется, а следовательно не работает.
Решил сам додумать, попыхтел помучал вымучал следующее:
Структура таблиц остаётся прежней, отдельное спасибо за это -ud-! А вот триггера я поменял:
Code: [Select]
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
Собственно всё. Вроде работает. Спасибо за помощь! Если найдёте косяки или варианты как оптимизировать, то буду рад рассмотреть варианты.
« Last Edit: February 20, 2009, 10:33:44 by CRonaldo »