Взаимосвязь двух записей из одной таблицы

[nicolas]

Здравствуйте!

Есть таблица:

Код: Выделить всё

TABLE(ID, NAME)

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

Код: Выделить всё

LINK(ID1, ID2, DESCRIPT)

где ID1 и ID2 - внешние ключи к TABLE

Далее, по условиям, если связь записей А и Б из TABLE описывается с помощью DESCRIPT, то и связь Б и А обладает тем же свойством.

А теперь собственно вопрос:
При внесении данных в таблицу LINK надо ли вносить сразу две записи, меняя местами ID1 и ID2, или можно ограничится только одной?

Если запись одна, то надо делать проверку на предмет вхождения ID1,ID2 и ID2,ID1 в таблицу LINK
Если писать две записи - возникает дублирование DESCRIPT, насколько это верно с точки зрения нормализации?

[EvilsInterrupt]

Это действительно правильная схема БД ? Мне кажется что это ошибочный проект БД, может и ошибаюсь

[WildSery]

Если писать две записи - возникает дублирование DESCRIPT, насколько это верно с точки зрения нормализации?

У тебя база по нормализации сертифицируется?
Видел базы и так, и сяк. В каждом случае выбор был сделан исходя из анализа удобств и грабель.
С дублированием - удобный поиск вхождения (одна проверка, а не две), что в некоторых случаях может покрыть избыточность.
Подумай над тем, как ты используешь эту таблицу и как и какие запросы будешь с ней делать.

[CyberMax]

Если взаимосвязь одна, что мешает ее поле поместить прямо в TABLE? Чем больше взаимосвязей, тем меньше избыточность будет в таком случае.
В принципе, лучше опиши проблему, может, оно и не надо вовсе так делать...

[nicolas]

Если взаимосвязь одна, что мешает ее поле поместить прямо в TABLE? Чем больше взаимосвязей, тем меньше избыточность будет в таком случае.
В принципе, лучше опиши проблему, может, оно и не надо вовсе так делать...

Попробую.
Есть таблица хим.веществ. TABLE(ID, NAME)
Вещества могут взаимодействовать друг с другом, в соотношении многие-ко-многим
При этом естественно, как А взаимодействует с Б, так и Б взаимодействует с А, с теми же эффектами (и спецэффектами )
Все это взаимодействие и уложено в таблицу LINK(ID1, ID2, DESCRIPT).
Поскольку взаимодействие многие-ко-многим, то уложить в TABLE нельзя.

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

[kdv]

Смущает только дублирование описания

в таблице LINK столбцы ID1 и ID2 должны составлять первичный ключ, чтобы не допускать дубликатов. Потому что комбинация ID1 + ID2 является уникальной, так же как и ID2 + ID1. На то что это "первый и второй" столбцы таблицы, смотреть не надо. Их можно было бы создать и в таком порядке.

если все сделать прозрачно на триггерах.

целостность триггерами контролировать можно только при монопольной (однопользовательской) работе с данными.

[nicolas]

Смущает только дублирование описания

в таблице LINK столбцы ID1 и ID2 должны составлять первичный ключ, чтобы не допускать дубликатов. Потому что комбинация ID1 + ID2 является уникальной, так же как и ID2 + ID1. На то что это "первый и второй" столбцы таблицы, смотреть не надо. Их можно было бы создать и в таком порядке.

Так и есть, это первичный ключ.

если все сделать прозрачно на триггерах.

целостность триггерами контролировать можно только при монопольной (однопользовательской) работе с данными.

А про триггеры я имею ввиду такую ситуацию:
если пользователь вносит в таблицу LINK такую запись:
(1,2,ххх), то триггер на INSERT должен добавить в таблицу запись (2,1,xxx)
Если пользователь меняет описание в записи (1,2,xxx) на (1,2,ууу), то триггер на UPDATE должен изменить (2,1,xxx) на (2,1,yyy)
Ну и при удалении записи с ПК(1,2) должна быть удалена запись с ПК(2,1)

Безопасно ли так делать в немонопольном режиме? (Если я правильно понял замечание)

[WildSery]

Их можно было бы создать и в таком порядке.

В каком? Кто из них первый?

Безопасно ли так делать в немонопольном режиме?

Конечно. Целостность контролируется первичным ключем.
Но тут есть и грабли - в триггерах нужно проверять связку на ID1=ID2, и в этом случае не создавать дубликат (нарушится PK), а в случае если связку изменили с (1,2,xxx) на (1,1,xxx), то нужно удалить (2,1,xxx)

ЗЫ. Хотя, если честно, так с ходу в голову не приходит механизм триггерного дублирования.

[kdv]

В каком? Кто из них первый?

болею я

[nicolas]

Но тут есть и грабли - в триггерах нужно проверять связку на ID1=ID2, и в этом случае не создавать дубликат (нарушится PK), а в случае если связку изменили с (1,2,xxx) на (1,1,xxx), то нужно удалить (2,1,xxx)

ЗЫ. Хотя, если честно, так с ходу в голову не приходит механизм триггерного дублирования.

Ну записей с ID1=ID2 просто не должно быть, это будет отсекаться на уровне бизнес-логики приложения (ну и в триггере можно проверку забабахать).
А что касается UPDATE (1,2)=>(1,3), к примеру, то тут тоже все рулит бизнес-логика+ПК: ведь этот UPDATE не что иное, как удаление взаимосвязи (1,2) и добавление (1,3).

Спасибо за совет и поддержку

[WildSery]

Можно всё и триггерами. В том числе и "равные" связи.
Во, у меня примерно так получилось:

Код: Выделить всё

CREATE TABLE TT (
    ID1         INTEGER NOT NULL,
    ID2         INTEGER NOT NULL,
    TXT         VARCHAR(10),
    DUPLICATED  SMALLINT DEFAULT 0 /* Специальное поле, в работе не использовать! */
);
ALTER TABLE TT ADD CONSTRAINT PK_TT PRIMARY KEY (ID1, ID2);


/* На изменение */
CREATE TRIGGER TT_BI FOR TT ACTIVE BEFORE INSERT POSITION 0
as begin
  /* Если это был дубликат, то просто позволяем его вставить */
  if (new.duplicated = 1) then
    new.duplicated = 0;
  else
  /* Иначе создаём дубликат */
  if (new.id1 != new.id2) then
    insert into tt (id1, id2, txt, duplicated) values (new.id2, new.id1, new.txt, 1);
end


/* На удаление */
CREATE TRIGGER TT_BD FOR TT ACTIVE BEFORE DELETE POSITION 0
as begin
  /* Если id1=id2, то удалится само */
  /* Иначе смотрим, не была ли запись уже помечена (duplicated = 2) */
  if ((old.id1 != old.id2) and (old.duplicated != 2)) then begin
    /* Помечаем дубликат двойкой */
    update tt set duplicated = 2 where id1 = old.id2 and id2 = old.id1;
    /* и удаляем его */
    delete from tt where id1 = old.id2 and id2 = old.id1;
  end
  /* Иначе даём просто удалить */
end


/* И наконец, самый сложный, на изменение */
CREATE TRIGGER TT_BU FOR TT ACTIVE BEFORE UPDATE POSITION 0
as begin
  /* Если это дубликат, просто даём самому измениться */
  if (new.duplicated = 1) then
    new.duplicated = 0;
  else
  /* Если это "нормальное" изменение (не пометка двойкой перед удалением) */
  if (new.duplicated = 0) then begin
    /* Если из двух сделали одинаковый id */
    if ((new.id1 = new.id2) and (old.id1 != old.id2)) then begin
      /* Помечаем дубликат двойкой */
      update tt set duplicated = 2 where id1 = old.id2 and id2 = old.id1;
      /* и удаляем его */
      delete from tt where id1 = old.id2 and id2 = old.id1;
    end
    else
    /* Иначе, если из одинаковых id сделали разные */
    if ((new.id1 != new.id2) and (old.id1 = old.id2)) then
      /* Создаём дубликат */
      insert into tt (id1, id2, txt, duplicated) values (new.id2, new.id1, new.txt, 1);
    else
      /* Иначе, если просто апдейт без дополнительных грабель, то апдейтим и дубликат */
      update tt
        set id1 = new.id2, id2 = new.id1, txt = new.txt, duplicated = 1
        where id1 = old.id2 and id2 = old.id1;
  end
  /* Иначе просто даём установиться пометке перед удалением */
end

[nicolas]

WildSery, респект

Буду учиться у гуру.