Дерево. Определение принадлежности узлов.

← →
Romkin © (2006-08-09 11:53) [0]

Проблема появилась, сижу голову ломаю. Есть у меня БД вопросов, которые разделяются по древовидной структуре разделов, то есть, есть таблица разделов
CREATE TABLE THEME ( THEME_ID INTEGER NOT NULL, THEME_NAME VARCHAR(80), OWNER_ID INTEGER, ); ALTER TABLE THEME ADD CONSTRAINT PK_THEME PRIMARY KEY (THEME_ID); alter table THEME add constraint FK_THEME_THEME foreign key (OWNER_ID) references THEME(THEME_ID) on delete CASCADE on update CASCADE;
В общем, обычное дерево. Есть таблица вопросов:
CREATE TABLE QUESTION ( QUESTION_ID INTEGER NOT NULL, QUESTION_NAME VARCHAR(80), THEME_ID INTEGER NOT NULL ); ALTER TABLE QUESTION ADD CONSTRAINT FK_QUESTION PRIMARY KEY (THEME_ID, QUESTION_ID);
И есть таблица (на самом деле две), связывающая вопрос и тему через билет (утрировал, не особо принципиально):
CREATE TABLE CARDQUESTION ( CARD_ID INTEGER NOT NULL, SCRIPT_ID INTEGER NOT NULL, QUESTION_ID INTEGER NOT NULL, constraint PK_CARDQUESTION primary key (SCRIPT_ID, CARD_ID, QUESTION_ID) ); еще есть таблица SCRIPT (THEME_ID, SCRIPT_ID, ...) (показан PK)
То есть, у каждой ветви дерева может быть список вопросов, из которых формируются билеты теста (два поля SCRIPT_ID, CARD_ID - как раз идентификатор билета, SCRIPT - тест). Тест такде принадлежит определенному разделу (THEME_ID). Пока все замечательно.
Все дело в том, что в тест, а именно в CARDQUESTION, можно вставлять вопросы из того раздела, которому принадлежит тест и из любого его потомка. То есть, встав в дереве на какой-либо узел, я вижу все вопросы, которые принадлежат этому узлу, плюс вопросы его потомков. Соответственно, выбрать я тоже могу любой.
Вопрос состоит в том, что мне нужна процедура, которая быстро и оптимально может определить "изолированность" узла раздела (таблица THEME). То есть, мне нужна процедура, которая получает на вход THEME_ID и возвращает RET integer >0 в том случае, когда ни один вопрос из данного раздела и его потомков не используется в тестах его предков.
Кто-нибудь может посоветовать? Есть процедура, выводящая QUESTION_ID для данного THEME_ID и его потомков. Есть процедура, выводящая всех предков для данного THEME_ID. Как бы так поуютнее спарить? join процедур делать не хочется. Предков мало, высота дерева 20 от силы, а вопросов в разделе может быть и под тысячу...

← →
zdm © (2006-08-09 12:08) [1]

реальная тема, я долго с деревьями мучался,,,и в конечном итоге оказалась самая реальная структура----это
id|pid|tree|name
пример
1 0 0 все
2 1 1. Юр.лица
3 2 1.1 ООО "ЗЗЗ"
4 1 2. Физ. Лица
ну и т.д. а на событие OnChange TreeView пишешь запрос "select ... from ... where tree Like "+QuotedStr( Sel_Node_Tree+"%")"

← →
Desdechado © (2006-08-09 12:13) [2]

Вот этого не понял
> когда ни один вопрос из данного раздела и его потомков не
> используется в тестах его предков.

И, попутно, что-то уж замудренная структура. Перепутанные сущности и их взаимосвязи:
1. TABLE QUESTION - PK, имхо, должен быть QUESTION_ID, нет FK на THEME_ID
2. так и не понял, что есть "тест" и почему это важное понятие спрятано от глаз.

← →
zdm © (2006-08-09 12:26) [3]

Sel_Node_Tree вычесляется функцией
function FN_Detect_For_Tree(const Sel_Node_Tree: string): string;
var
I: Integer;
begin

I := LastDelimiter(".", Sel_Node_Tree);
if (I > 0) and (Sel_Node_Tree[I] = ".") then
Result := Copy(Sel_Node_Tree, I+1, MaxInt) else
Result := "";
end ;

← →
Romkin © (2006-08-09 12:48) [4]

Desdechado © (09.08.06 12:13) [2] Тест - это таблица SCRIPT, первичный ключ (THEME_ID, SCRIPT_ID). QUESTION - нет, каждый вопрос принадлежит разделу (THEME), соотношение мастер-деталь, поэтому ключ мастера первым стоит. FK я не показывал...
Кажется, я уже нащупал соотношение: взять раздел и всех его потомков, и посмотреть, есть ли в CARD_QUESTION записи, в которых вопросы принадлежат этому множеству, а тесты (SCRIPT) - нет. По идеологии, конечно, в CARDQUESTION должны быть еще два поля, THEME_ID и QUESTION_THEME_ID, но увы, их нет :( Структура старая, меняли...
Почему должны быть? Эта таблица - соединение билета и его вопросов. Билет - деталь теста, тест (SCRIPT) - деталь. У теста ключ (THEME_ID, SCRIPT_ID), у билета (CARD), следовательно, (THEME_ID, SCRIPT_ID, CARD_ID). У вопроса ключ (THEME_ID, QUESTION_ID). Вот и получается, что соединение CARDQUESTION должно состоять из объединения ключей:
(THEME_ID, SCRIPT_ID, CARD_ID, QUESTION_THEME_ID, QUESTION_ID)
Пожалуй, так и надо сделать...

2zdm © Процедура мне нужна на Firebird, а не на Delphi

← →
evvcom © (2006-08-09 12:50) [5]

> [1] zdm © (09.08.06 12:08)
> [3] zdm © (09.08.06 12:26)

Где ты увидел вопрос о коде на паскале?

> [0] Romkin © (09.08.06 11:53)
> [2] Desdechado © (09.08.06 12:13)

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

← →
Sergey13 © (2006-08-09 13:01) [6]

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

← →
Romkin © (2006-08-09 13:01) [7]

Неужели туманно?

Вот есть цепочка THEME -> QUESTION (вопрос принадлежит разделу). Прошу учесть, что THEME - дерево.
и есть вторая цепочка THEME -> SCRIPT -> CARD -> QUESTION (В разделе может быть тест, который состорит из билетов, которые могут включать в себя вопросы данного раздела и любого из его потомков). Связь CARD и QUESTION - многие-многие, поэтому есть CARDQUESTION.
Нужно выяснить, можно ли, допустим, убрать раздел. При удалении раздела удаляются и все его потомки. Раздел нельзя убрать, если вопросы используются в остающихся разделах (то есть, есть вопрос раздела или его потомков, который используется в тестах предка). Вот и сижу. Это касается не только удаления, просто сказать пользователю, можно ли переместить ветку и тд...

← →
evvcom © (2006-08-09 13:04) [8]

> [7] Romkin © (09.08.06 13:01)

После [4] прояснилось. Когда я писал, [4] еще не видел.

← →
Desdechado © (2006-08-09 13:36) [9]

Мне кажется, чтотакая структура более удобна для твоей задачи.
CREATE TABLE THEME ( THEME_ID INTEGER NOT NULL, THEME_NAME VARCHAR(80) NOT NULL, OWNER_ID INTEGER NOT NULL, ); ALTER TABLE THEME ADD CONSTRAINT PK_THEME PRIMARY KEY (THEME_ID); ALTER TABLE THEME ADD CONSTRAINT FK_THEME_THEME FOREIGN KEY (OWNER_ID) REFERENCES THEME(THEME_ID); CREATE TABLE QUESTION ( QUESTION_ID INTEGER NOT NULL, QUESTION_NAME VARCHAR(80) NOT NULL, THEME_ID INTEGER NOT NULL ); ALTER TABLE QUESTION ADD CONSTRAINT PK_QUESTION PRIMARY KEY (QUESTION_ID); ALTER TABLE QUESTION ADD CONSTRAINT FK_QUESTION_THEME FOREIGN KEY( THEME_ID ) REFERENCES THEME( THEME_ID ); CREATE TABLE SCRIPT ( SCRIPT_ID INTEGER NOT NULL, SCRIPT_NAME VARCHAR(80) NOT NULL, THEME_ID INTEGER NOT NULL ); ALTER TABLE SCRIPT ADD CONSTRAINT PK_SCRIPT PRIMARY KEY( SCRIPT_ID ); ALTER TABLE SCRIPT ADD CONSTRAINT FK_SCRIPT_THEME FOREIGN KEY( THEME_ID ) REFERENCES THEME( THEME_ID ); CREATE TABLE CARD ( CARD_ID INTEGER NOT NULL, CARD_NO VARCHAR(30) NOT NULL, SCRIPT_ID INTEGER NOT NULL ); ALTER TABLE CARD ADD CONSTRAINT PK_CARD PRIMARY KEY( CARD_ID ); ALTER TABLE CARD ADD CONSTRAINT FK_CARD_SCRIPT FOREIGN KEY( SCRIPT_ID ) REFERENCES SCRIPT( SCRIPT_ID ); CREATE TABLE CARDQUESTION ( CARD_ID INTEGER NOT NULL, QUESTION_ID INTEGER NOT NULL ); ALTER TABLE CARDQUESTION ADD CONSTRAINT UQ_CARDQUESTION UNIQUE (CARD_ID, QUESTION_ID); ALTER TABLE CARDQUESTION ADD CONSTRAINT FK_CARDQUESTION_CARD FOREIGN KEY( CARD_ID ) REFERENCES CARD( CARD_ID ); ALTER TABLE CARDQUESTION ADD CONSTRAINT FK_CARDQUESTION_QUESTION FOREIGN KEY( QUESTION_ID ) REFERENCES QUESTION( QUESTION_ID );
Но при условии, конечно, что тест толькопо одной теме, а билет может включать вопросы одной темы и присутствовать только в одном тесте.

← →
evvcom © (2006-08-09 14:02) [10]

В Оракле есть такая замечательная функция SYS_CONNECT_BY_PATH. Приведу описание для пущей ясности:
Syntax
SYS_CONNECT_BY_PATH ( column , char )
Purpose
SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of
a column value from root to node, with column values separated by char for each
row returned by CONNECT BY condition.
Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or
NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same
character set as column.
Examples
The following example returns the path of employee names from employee
Kochhar to all employees of Kochhar (and their employees):
SELECT LPAD(’ ’, 2*level-1)||SYS_CONNECT_BY_PATH(last_name, ’/’) "Path"
FROM employees
START WITH last_name = ’Kochhar’
CONNECT BY PRIOR employee_id = manager_id;
Path
---------------------------------------------------------------
/Kochhar
/Kochhar/Greenberg
/Kochhar/Greenberg/Faviet
/Kochhar/Greenberg/Chen
/Kochhar/Greenberg/Sciarra
/Kochhar/Greenberg/Urman
/Kochhar/Greenberg/Popp
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Baer
/Kochhar/Higgins
/Kochhar/Higgins/Gietz

Пробелы в результате удалились :( Таков уж HTML и IE.
Далее идея:
Разворачиваем дерево от потомков к предкам и используем вышеупомянутую функцию. Таким образом получаем для каждой THEME_ID несколько записей с путями, которые легко можно преобразовать к виду флага "есть child" или "нет". А далее агрегатным запросом получить конечное значение нужного флага.
Поскольку функцию или ее некий аналог придется реализовывать в FB самому, то и о флаге соответственно можно позаботиться уже в ней.

← →
Romkin © (2006-08-09 14:22) [11]

Desdechado © (09.08.06 13:36) [9] НУ да, ну да, конечно. Особенно в свете того, что мне сейчас надо делать join CARDQUESTION именно потому, что у нее не полный составной ключ! Был бы полный - я бы сразу взял оттуда пару THEME_ID, первая - которой принадлежит скрипт, вторая - которой принадлежит вопрос. А то, что ты предлагаешь - вообще приводит к непрерывным join!
Простой вопрос: есть QUESTION_ID, надо вывести все SCRIPT_ID, в которых он употребляется. И как в твоей структуре это будет смотреться?! У меня это - простой запрос к CARDQUESTION

← →
MsGuns © (2006-08-09 16:26) [12]

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

← →
Romkin © (2006-08-09 17:24) [13]

MsGuns © (09.08.06 16:26) [12] Наверно, можно сказать и так. Условие - через CARD узел может владеть вопросом только своих потомков.

← →
evvcom © (2006-08-09 17:32) [14]

Ну так вопрос исчерпан или как? Мой [10] помог, или я его тоже туманно изложил? Или остановился на реконструкции таблиц?

← →
Desdechado © (2006-08-09 17:43) [15]

> есть QUESTION_ID, надо вывести все SCRIPT_ID, в которых
> он употребляется. И как в твоей структуре это будет смотреться?
SELECT DISTINCT C.script_id FROM Card C, CardQuestion CQ WHERE CQ.question_id = :Q AND CQ.card_id = C.card_id
Вроде совсем не сложно...

← →
Romkin © (2006-08-09 17:44) [16]

Все, таки привел к нормальному виду:CREATE TABLE CARDQUESTION ( THEME_ID INTEGER NOT NULL, SCRIPT_ID INTEGER NOT NULL, CARD_ID INTEGER NOT NULL, QUESTION_THEME_ID INTEGER NOT NULL, QUESTION_ID INTEGER NOT NULL, ORD_NUM INTEGER ); ALTER TABLE CARDQUESTION ADD CONSTRAINT PK_CARDQUESTION PRIMARY KEY (THEME_ID, SCRIPT_ID, CARD_ID, QUESTION_THEME_ID, QUESTION_ID); ALTER TABLE CARDQUESTION ADD CONSTRAINT FK_CARDQUESTION_CARD FOREIGN KEY (THEME_ID, SCRIPT_ID, CARD_ID) REFERENCES CARD (THEME_ID, SCRIPT_ID, CARD_ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE CARDQUESTION ADD CONSTRAINT FK_CARDQUESTION_QUESTION FOREIGN KEY (QUESTION_THEME_ID, QUESTION_ID) REFERENCES QUESTION (THEME_ID, QUESTION_ID) ON DELETE CASCADE ON UPDATE CASCADE;
И вот тут как раз и видна связь: два поля THEME_ID и QUESTION_THEME_ID. Первое - какой теме принадлежит тест, второе - какой теме принадлежит вопрос. Существует ограничение: QUESTION_THEME_ID - строго потомок (неважно, через сколько детей) THEME_ID или они равны.
Нужна процедура, которая для данного ID (THEME_ID) определяет, есть ли в его предках ссылки через эту таблицу на него или его потомков. То есть, есть ли записи в CARDQUESTION, где THEME_ID - предок ID, а QUESTION_THEME_ID - либо потомок ID, либо он сам. О как :)
Вопрос в том, как лучше повернуть.

← →
Desdechado © (2006-08-09 17:47) [17]

> привел к нормальному виду:
ИМХО, сплошная денормализация. Закольцевал ключи, как только можно...
Иногда это оправдано, но, имхо, не в этом случае.

← →
MsGuns © (2006-08-09 17:58) [18]

>Romkin © (09.08.06 17:44) [16]

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

← →
Romkin © (2006-08-09 18:19) [19]

Desdechado © (09.08.06 17:47) [17] Где там кольцо?! :) Как раз нормальная форма, причем не ниже четвертой ;) И именно в этом случае - полностью оправданно.
MsGuns © (09.08.06 17:58) [18] Э. Надо было сразу делать дерево разделов на вложенных множествах, там проще проверки делать потомок-предок.
Ничего, уже примерно вырисовалось: во-первых, триггер, во-вторых, буду выяснять, а не потомок ли данного узла... Так, похоже, проще. То есть, взять всех предков, и для каждого посмотреть, а нет ли в CARDQUESTION у этого предка потомков данного узла и его самого. Похоже, наименьший лаг получится.

← →
Desdechado © (2006-08-09 18:24) [20]

> Где там кольцо?! :)
В таблице QUESTIONS уже есть взаимоотношения вопросов и тем.
В таблице SCRIPT уже есть взаимоотношение тестов и тем.
В таблице THEME уже есть взаимоотношение тем более высокого и более низкого порядка.
Типичнейшая денормализация.

← →
Romkin © (2006-08-09 18:32) [21]

Desdechado © (09.08.06 18:24) [20] Вот уж неправда. Приведи форму и требование, которому не удовлетворяет ;)
Вот именно, что есть. И поэтому в соединении все эти взаимоотношения учитываются.

← →
Desdechado © (2006-08-09 18:35) [22]

Требований точно не помню.
Но повторения взаимосвязей сущностей, AFAIR, быть не должно. А у тебя все перечисленное в [20] дублируется в "доработанной" таблице.
Зачем? Это ведь можно соединить каскадно JOIN"ами при необходимости.

← →
Romkin © (2006-08-09 18:38) [23]

Понимаешь, простой вопрос на засыпку: а почему ты решил, что QUESTION_ID - альтернативный ключ? :) Вопрос однозначно идентифицируется своим первичным ключем, (THEME_ID, QUESTION_ID). CARD тоже идентифицируется своим ключем, (THEME_ID, SCRIPT_ID, CARD_ID). И на данный момент у меня в БД нет альтернативных ключей для этих таблиц, не объявлены они, понимаешь? ;)
Следовательно, соединение многие-многие должно содержать полную комбинацию первичных ключей.
И даже если есть альтернативные - на каком основании я должен делать соединение по ним?! Такого требования нет.

← →
Romkin © (2006-08-09 18:39) [24]

Desdechado © (09.08.06 18:35) [22] А зачем мне городить постоянно join? Какая выгода от пользования того, как ты предлагаешь? Место? Мне хватает. Блюсти согласованность? Дык foreign key есть. Что еще?

← →
Desdechado © (2006-08-09 18:47) [25]

> QUESTION_ID - альтернативный ключ?
Да, подзабыл. Я ведь уже уточнял по этому поводу...
Но я не вижу смысла генерировать ID вопроса внутри темы. Гораздо удобнее использовать QUESTION_ID как сквозной по всей таблице. Что я и предлагал в своем видении структуры.
А многокомпонентные первичные и внешние ключи мне неприятны, тем более, что в твоей ситуации легко можно обойтись и без них. От них только геморрой лишний и запутывание. Какой же это QUESTION_ID, если он неуникальный?
Размер - он, может, и не важен тут. Но вот прозрачности и легкопонимаемости у твоей структуры нет. Она тяжела для понимания.

Ладно, не буду больше давить. Я написал свое мнение.
А в JOIN нет ничего плохого. Зачем ты его боишься, не знаю...

← →
Romkin © (2006-08-09 18:52) [26]

Desdechado © (09.08.06 18:47) [25] Не надо! Ты свою структуру даже в ErWin не нарисуешь правильно. Он это дело пресекает.
Удобно?! Мне как раз надо получить две THEME_ID из CARDQUESTION. Если там альтернативные, то я должен делать join 5 таблиц?! Фактически, восстанавливая составные ключи?! Это, простите, маразм, а не удобство. Модель должна отражать реальное положение вещей, а не то, что удобно программисту. И как раз моя структура прозрачна, четко видно, с чем связана CARDQUESTION и как.

← →
Desdechado © (2006-08-09 19:07) [27]

> Ты свою структуру даже в ErWin не нарисуешь правильно.
ErWin - догма?
Кстати, ложится она туда хорошо. Что он должен пресечь, интересно?

> Мне как раз надо получить две THEME_ID из CARDQUESTION.
> Если там альтернативные, то я должен делать join 5 таблиц?!
Ничего подобного. У билета есть вопрос, у вопроса есть тема. 3 таблицы.
Никаких ключей восстанавливать не надо.

> Модель должна отражать реальное положение вещей, а не то,
> что удобно программисту.
Двумя руками за! Сущности - сами по себе, их взаимосвязи - сами. У тебя же в одну кучу намешано.

> четко видно, с чем связана CARDQUESTION и как.
О, да! Она связана сразу со всеми таблицами. Но ведь сам же рисовал цепочку:
темы, в которых тесты, в которых билеты, в которых вопросы, которые по темам
Вот они - 5 сущностей, по таблице на сущность, как и полагается. Соединяем требуемые при необходимости, а не храним все в одной таблице, как у тебя.

Сорри, не хочу ругаться, но, кажется, ты не хочешь понять...

← →
Romkin © (2006-08-09 19:20) [28]

Desdechado © (09.08.06 19:07) [27] Не догма, а инструмент с контролем структуры. И не поверю, что ложится. Либо все связи non-identify у тебя получаются, аэто - совсем не то. Идентифицирующую связь накладываешь - так сразу в первичный ключ детали идет первичный ключ мастера.

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

А этот join - это и есть, по сути, восстановление ключа. И смотри, что получается: фактически, у тебя есть требование уникальности, допустим, на question_id. А у меня нет. Оно - дополнительное, и не нужно.

Модель - это и сущности и связи между ними. И все это вместе рассматривается.

>О, да! Она связана сразу со всеми таблицами.
Не сразу, а с двумя. Четко видны идентифицирующие связи, два foreign key. И четко по одной этой таблице я вижу всю иерархию, сразу предполагая, что над CARD есть еще два владельца (не справочника, а именно владельца), а над question - один. Модель читабельна даже по частям. И сразу видно, что эта таблица - соединение. Что еще нужно?!

И что значит "объединяем по необходимости"? Эта необходимость - постоянно есть, это во-первых. Во-вторых, я не вижу преимуществ твоей схемы, а недостатки - есть.

> Идентифицирующую связь накладываешь - так сразу в первичный
> ключ детали идет первичный ключ мастера.
Если у тебя в детали ссылки на 15 справочников-мастеров, у тебя будет первичный ключ детали из 15 полей? Бред. Особенно, если нет необходимости в первичном ключе.
А если даже и пошло 15 полей, но эта таблица является мастером еще для какой-то, в которой тоже пара-тройка мастеров 15-польных, у тебя ключ в ней вырастет до 30-50 полей. Та хай меня покрасят тогда с такими правилами...

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

Так справочников или мастеров? :) Справочники (неидентифицирующая связь) в первичный ключ не идут. И не понимаю я, что значит нет необходимости в первичном ключе? Это основа. И, кстати, если у тебя два мастера - это связь многие-многие. А если больше - то тут нужно подумать, а не повысить ли нормализацию до 4-5 формы?
И необходимость у меня эта есть постоянно. А "нельзя сделать простыми шагами" - да на многих структурах можно делать что-то по шагам. Вопрос в скорости, и в удобстве.

> Так справочников или мастеров?
?

> нет необходимости в первичном ключе? Это основа.
Например, протокол каких-то действий. Они могут быть одинаковые, даже в одно время, разница только в некоем блобе, который не включишь в ключ, да и не надо...
Правда, редкость, но такое бывает...

Все, я устал спорить. В конце концов "каждый идет к истине своей дорогой, а на перекрестке сижу я, Будда"
:)

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

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

Практикую свой подход уже лет 10, ни разу не было проблем ни с масштабированием, ни с развитием, ни с быстродействием. А системы создавались немаленькие, минимум в 100 таблиц. И объемы - не 24*7, конечно, но нехилые.

2 Desdechado © (09.08.06 19:50) [33]
> И объемы - не 24*7, конечно, но нехилые.
Прикольно... Объемы теперь в "аптаймах" измеряются...

2 афтар
Роман, объясните, пожалуйста, зачем в F(Р?)K_QUESTION входит THEME_ID? Я не могу понять.
Слова "связывающая вопрос и тему через билет" ваще не дают мне заснуть...

P.S.
Было бы славно полностью приводить все ключи...

Fay © (10.08.06 07:04) [35] Из всех таблиц? В первом же абзаце сказано: вопрос принадлежит разделу. То есть, первоначально есть запись в THEME (раздел), и в него вводятся вопросы (и учебные материалы, кстати. Но они отношения не имеют). Далее в любом разделе можно сделать тестовое задание (SCRIPT), которое включает в себя некоторое количество билетов. И каждый билет содержит, в свою очередь, несколько вопросов, как из того раздела, которому принадледит тест, так и из нижележащих по дереву (подразделы). Логически это понятно: допустим, курс предмета разбивается на части, по каждой из которых будет экзамен. Плюс экзамен за весь курс, в билетах которого вопросы из всех частей.
Конечно, более естественной выглядит структура CARDQUESTION следующего вида:CREATE TABLE CARDQUESTION ( THEME_ID INTEGER NOT NULL, SCRIPT_ID INTEGER NOT NULL, CARD_ID INTEGER NOT NULL, QUESTION_ID INTEGER NOT NULL, ORD_NUM INTEGER ); ALTER TABLE CARDQUESTION ADD CONSTRAINT PK_CARDQUESTION PRIMARY KEY (THEME_ID, SCRIPT_ID, CARD_ID, QUESTION_ID); ALTER TABLE CARDQUESTION ADD CONSTRAINT FK_CARDQUESTION_CARD FOREIGN KEY (THEME_ID, SCRIPT_ID, CARD_ID) REFERENCES CARD (THEME_ID, SCRIPT_ID, CARD_ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE CARDQUESTION ADD CONSTRAINT FK_CARDQUESTION_QUESTION FOREIGN KEY (THEME_ID, QUESTION_ID) REFERENCES QUESTION (THEME_ID, QUESTION_ID) ON DELETE CASCADE ON UPDATE CASCADE;
То есть, со слиянием ключей (см [16]), но это - явное указание на то, что у билета (CARD) могут быть вопросы только из его раздела, а это не проходит по условию :)

А сейчас, сделав правильное наследование ключей, я могу легко перефразировать задачу: есть таблица THEME
(THEME_ID integer not null primary key,
OWNER_ID integer references THEME(THEME_ID)); - дерево.
есть таблица CARDQUESTION (
THEME_ID INTEGER NOT NULL,
SCRIPT_ID INTEGER NOT NULL,
CARD_ID INTEGER NOT NULL,
QUESTION_THEME_ID INTEGER NOT NULL,
QUESTION_ID INTEGER NOT NULL,
ORD_NUM INTEGER
);
Поля этой таблицы THEME_ID и QUESTION_THEME_ID опосредованно ссылаются на THEME (THEME_ID).
Задача: Для заданного значения THEME_ID выяснить, имеется ли в таблице CARDQUESTION строка, в которой THEME_ID указывает на предка данного значения, а QUESTION_THEME_ID - на него или одного из его потомков. Через процедуру.

Увы, теперь я жалею, что не причесал структуру раньше. Думаю, многих вопросов бы не было :)))
Впрочем, именно структура и дает подсказку, буду идти по потомкам, брать из CARDQUESTION соответствующий THEME_ID этого потомка и проверять принадлежность того, что QUESTION_THEME_ID также входит в то же множество раздела и его потомков.
Получается даже более свободно, никаких предков.
Правда, фактически это вычитание множеств: берем множество потомков, и проверяем, что все вопросы этого множества входят в билеты только этого же множества :(

> Объемы теперь в "аптаймах" измеряются...
Думаю, ты понял, о чем я. О значительном времени, когда юзеры долбят/читают данные. Оттуда и объемы.

2 Romkin © (10.08.06 10:45) [36]
Я понял так : -- Разделы create table T ( ID int not null, PARENT_ID int null, constraint PK_T primary key (ID), constraint FK_T_SELF foreign key (PARENT_ID) references T (ID) ) -- Вопросы create table Q ( ID int not null, T_ID int not null, -- Тема вопроса constraint PK_Q primary key (ID), constraint FK_Q_T foreign key (T_ID) references T(ID) ) -- Билеты create table C ( ID int not null, Q_ID int not null, T_ID int not null, -- Уточнённая тема, должна входить в ветку темы вопроса Q.T_ID constraint PK_Q primary key (ID), constraint FK_C_Q foreign key (Q_ID) references Q (ID), constraint FK_C_T foreign key (T_ID) references T (ID) )

Правильно?

Неа :) Но почти.
Разделы - верно, повторяю, PK переименую
create table T ( T_ID int not null, PARENT_ID int null, constraint PK_T primary key (T_ID), constraint FK_T_SELF foreign key (PARENT_ID) references T (T_ID) ); --Вопросы: тоже верно, немного изменю create table Q ( Q_ID int not null, T_ID int not null, -- Тема вопроса constraint PK_Q primary key (Q_ID), constraint FK_Q_T foreign key (T_ID) references T(T_ID) ); -- Билеты - увы! Нет там вопросов. И у нее есть мастер, таблица тестов S create table S ( T_ID int not null, -- тема билета! S_ID int not null, constraint PK_S primary key (T_ID, S_ID), constraint FK_S_T foreign key (T_ID) references T (T_ID) ) --А теперь билеты create table C ( T_ID int not null, -- тема билета! S_ID int not null, C_ID int not null, constraint PK_C primary key (T_ID, S_ID, C_ID), constraint FK_C_S foreign key (T_ID, S_ID) references S (T_ID, S_ID) ) --А теперь внимание, билеты связаны с вопросами отношением многие-многие: create table CQ ( T_ID int not null, -- тема билета! S_ID int not null, C_ID int not null, Q_T_ID int not null, -- тема вопроса! Q_ID int not null, constraint PK_CQ primary key (T_ID, S_ID, C_ID, Q_T_ID, Q_ID), constraint FK_CQ_C foreign key (T_ID, S_ID, C_ID) references C (T_ID, S_ID, C_ID), constraint FK_CQ_Q foreign key (Q_T_ID, Q_ID) references Q (Q_T_ID, Q_ID) )
Сейчас так. А собственно постановка - в [36]

Зачем T_ID в PK_S ?

Дерево. Определение принадлежности узлов. Найти похожие ветки