Работа с родительской и дочерними таблицами

[nikedeforest]

Вот такой вопрос:
Есть 4 таблицы, одна из них справочник. Мне нужно сделать так, чтобы при внесении значения в справочник, который состоит из четырех полей, проверялось есть ли это значение в поле этого справочника, если нет, то добавить его и внести в дочерние таблицы соответствующий порядковый номер для этого значения, если значение уже в справочнике содержиться, то просто в дочерние таблицы внести порядковый номер этого значения. Проблема заключается в том, что в что в справочнике несколько полей, значение вноситься тоько в одно поле, используя триггер ему нельзя передать параметр, следовательно он не будет знать для какого именно поля мне нужно просматривть есть-ли там это значение, либо его нет. А ведь триггер самый оптимальный вариант, и чего-то другой способ на ум не приходит, может что подскажете.
Блин, че-то кажется плохо объяснил .

[nikedeforest]

Решил, что спасение в хранимой процедуре, но вот незадача, не пойму, выдается ошибка Column unknown
NAME_POLYA, все просмотрел, не вижу где. Если не сложно ткните пальцем
--------------

Код:

set term!!
create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
begin
temp=0;
select pn from sprav
where :name_polya=:znach
into :temp;
        if (temp=0) then
        begin
               select min(pn) from sprav
               where :name_polya=0
               into :temp;
                     if (temp<>0)then
                          begin
                             insert into sprav (name_polya)
                             values (:znach);
                                insert into name_table (name_polya)
                                values (:temp);
                          end
                     else
                       begin
                            select max(pn) from sprav
                            into :temp;
                                  insert into sprav (pn, name_polya)
                                  values (:temp+1, :znach);
                                      insert into name_table(name_polya)
                                      values (:temp+1);
                       end
        end
        else
          begin
          insert into name_table (name_polya)
          values (:temp);
          end
end!!
set term;

[Dimka]

спасение, действительно, в ХП

не знаю, что за диалект, наверно IB какой-нибудь, однако

where :name_polya=0

Это есть сравнение значения переменной с 0, но не поля таблицы, хранящегося в :name_polya с 0. Для реализации подстановки поля нужно построить динамический запрос (в виде строчки) и выполнить его. Наверно, выполнять его будет функция exec. Но не ручаюсь.

конкретно ошибки здесь

insert into sprav (name_polya)

и

insert into name_table (name_polya)

и

insert into sprav (pn, name_polya)

и

insert into name_table(name_polya)

и

insert into name_table (name_polya)

двоеточие пропущено. Но всё равно работать не будет, нужен динамический запрос.

[nikedeforest]

Я пробовал вставить двоеточие в запросы insert into ... , но происходит ругательство на двоеточие. Поэтому, вроде как там я прав.
 С where  я кажется и вправду тупанул, щас проверю. Функция ехес - это, я про такую в SQL не слышал, такую я видел в Си и Дельфи.

[nikedeforest]

убрал двоеточие в where не помогло . Я не пойму, эта процедура, написанная мною, вообще к жизни не пригодна ?. Где я не прав? Я уже врроде с этими двоеточиями все перепробовал, может дело не в них.
Насчет диалекта, то это interbase поставляемый  с DELPHI 7. Я сначала на нем пишу, потом перевожу на MySQL.

"Для реализации подстановки поля нужно построить динамический запрос (в виде строчки) и выполнить его."-не совсем понял про динамический запрос, может я о нем слышал под другим названием, что это.

[Dimka]

потому что в insert и в select должны быть названия полей, а не переменные. Повторяю, нужен динамический запрос. Я не ручаюсь за правильность, так как под рукой нету IB, чтобы проверить, но что-то в духе нижеследующего:

Код:

declare variable sqlstr varchar(2000);
sqlstr = 'select pn from sprav where ' + :name_polya + '=' + :znach;
exec :sqlstr into :temp

Т.е. запрос надо записать как текст, вставив в него через сложение строк переменные, содержащие поля. И выполнить эту строчку интерпретатором.

[nikedeforest]

Щас попробую, я вообще-то такое первый раз вижу, (обидно, столько книжек прочел). Этот код, он должен находиться в теле процедуры???

[nikedeforest]

Код:

set term!!
create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
declare variable sqlstr varchar(70);
begin
temp=0;
sqlstr='select pn from sprav where'+ name_polya+'='+:znach;
exec :sqlstr into :temp;
        if (temp=0) then
        begin
............
end!!
set term ;

Вот так сделал, происходит ругательство такого типа

Dynamic SQL Error
SQL error code = -104
Token unknown - line 8, char 4
:

Удаляю двоеточие перед sqlstr  происходит ругательство типа, что за sqlstr.
Я вообще удивляюсь, что на ехес ругательства нет, редактор не выделил жирным это слово, т.е. он слово ехес не посчитал за ключевое. А execute procedure это не тоже, что ехес?

[HandKot]

Может конечно не совсем допонял задачу, но мне кажется все можно решить с помощью связей и индексов

[nikedeforest]

HandKot, теперь я не совсем понял тебя. ИМХО процедура то, что мне надо, вот только проблема стала в том, чтобы написать ее правильно, т.е. чтобы она работала, но видать моих знаний тут недостаточно. Вот и прошу о помощи, чтобы сделать процедуру эту рабочей.

[Dimka]

sqlstr='select pn from sprav where'+ name_polya+'='+:znach;

пусть name_polya = 'test', а znach = '3'
твой запрос образует строчку

Код:

'select pn from sprav wheretest=3'

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

[HandKot]

nikedeforest
тута порылся и вроде в IB нет команды EXEC

может тогда попробовать так

Код:

create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
declare variable sprPN integer;
BEGIN
sprPN = 0
if (name_polya = 'Поле1') then
begin
  SELECT pn FROM sprav WHERE Поле1 = :znach INTO :temp;
  if (temp = 0) then
     'нет такой записи в справочнике - вставляем
     'формируем новый ключ
     SELECT MAX(pn)+1 FROM sprav INTO :temp 
     INSERT INTO sprav(pn, Поле1) VALUES (:temp,:znach)    
     sprNP = temp
  else
     SELECT pn FROM sprav WHERE 'Поле1' = :znach INTO :sprPN
  end
end
else
begin
 ---другое поле
end

'всавляем ключ в подчиненные таблицы
if (name_table = 'Таблица1') then
begin
  insert into Таблица1(pn) VALUES (:sprPN)
end
else
begin
 ---другая таблица
end
END!!!

синтаксис неправильный т.к. я не знаком с IB
я просто хочу передать примерное решение
и еще, если сделать не ХП, а функцией возвращающей значение ключа из справочника
и тогда в подчиненные таблицы заносить значение ключа уже не внуьри проверки, а снаружи так

Код:

insert into [Имя таблицы](pn) values (control(ИмяПоля, Значение)

[Dimka]

Одна эта ошибка

Dynamic SQL Error

Говорит, что динамические запросы в IB есть Если не пойдёт, можно попробовать писать в скобках. В SQL Server, с которым я работаю, динамические запросы выполняются как exec('select ........'), а вызов процедур без скобок.

exec - это и есть сокращение от execute. Ещё со времён dBase (а может и раньше, не знаю) повелось, что команды можно писать сокращённо . В FoxPro (в старых версиях точно) команды различались лишь по первым 4 символам, там и вместо select можно писать sele. В других СУБД это не всегда так, но exec - штука довольно распространённая.

[nikedeforest]

HandKot, насколько я понял, ты предлагаешь вместо переменных name_polya и name_table использовать конкретно имя столбца(поля) и имя таблицы в запросах . Но понимаешь, таблица-то не одна, их на данный момент уже 3, в дальнейшем их может прибавиться, хотелось бы чтобы ХП не была такой чувствительной к изменением в БД. А еще если представить, что полей тоже не мало, то вообще так подумать, это сколько же мне надо писать оператор if и запросов для каждого поля и таблицы, и опять же никакой расширяемости для БД. Не очень-то подходит твой способ, ты уж не обижайся, но в этом случае мне вообще нет смысла в этой хранимой процедуре, она должна бать все-таки по универсальнее.
dimka, понял, но уже проверю только завтра. Мне надо к экзамену завтрашнему хоть немного подготовиться, поэтому отвечу после экзамена.
 А вот чуть не забыл, у препода сегодня спросил про динамический запрос, оказывается мы такие вовсе не затрагивали (да и в книжках я не встречал), поэтому не мудрено, что я про них не слышал. Но он сказал, что динамический запрос нельзя вставлять в хранимую процедуру. А теперь HandKot говорит, что exec нет в IB. dimka ты уверен, что это то что надо?

[nikedeforest]

Так запросы динамические есть. А вот ехес есть в IB? Может там что-то другое?

[Dimka]

Не знаю, как в IB... И почему твой преподаватель запрещает вставлять динамические запросы в ХП... Сам язык SQL таких ограничений не накладывает - это лишь причуды IB могут быть.

Попробуй выполнить простую процедуру вида

Код:

create procedure test 
as
begin
exec('select * from sprav');
end

Поиграйся с exec: со скобками и без, заменить на execute и т.п. Если она заработает  - ты получишь безошибочное исполнение, то твой преподаватель ошибается.

[nikedeforest]

dimka, т.е. если так как ты говоришь, то мне достаточно туда пробел добавить и все должно заработать. Сам смысл того что я делал правильный?

[nikedeforest]

Блин, когда писал последний пост, не весь прочел предидущий  пост (т.е. твой dimka). Надо в принципе так и сделать как ты сказал, заняться экспериментом с IB.

[Dimka]

Вот читаю документацию по IB и вижу одну простую инструкцию, исполняющую динамические запросы: EXECUTE IMMEDIATE. Однако, инструкция эта в консоли IB не работает и в ХП соответственно, предназначена для других целей (см. доки  Embedded SQL Guide). Для каких именно - не понял, куда-то это встраивается в код на C/C++. Разбираться тщательно нет времени. Так что, не выйдет. Признаться, я об IB был более высокого мнения.

Тогда остаётся лишь один выход: вынести код на клиентское приложение - там можно динамически запросы собирать и исполнять.

[nikedeforest]

dimka, ты гонец плохих новостей. Слушай, а в MySql это все реально сотворить? Мне в принципе все это надо для MySql, просто я к нему еще не привык и по началу все делаю на IB. Но в этом случае может придеться сразу в MySql.

[Oldy]

Если позволите, я бы попытался сделать так:

create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
begin
  temp=0;
  if :name_polya = 'FIeld1' then
  begin
     select pn from sprav where Field1=:znach into :temp;
        if (temp=0) then
          begin
             insert into sprav (Field1)   values (:znach);
            // и далее то, что нужно если значение уникально
          end
        else
          begin
            // здесьделаем что нужно, если значение поля не уникально
          end
  end

  if :name_polya = 'FIeld2' then //все тоже самое, что и для первого поля
  begin
    .
    .
    .
  end
  и т.д.
end

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

[Dimka]

Описанный способ с условиями выше предлагался, но неудобно...

А насчёт справочников даже согласен. Почему в одной таблице 4 поля, а заполняется лишь одно из них? Это недостаточная нормализация. Это ещё оправдать нужно какими-то соображениями.

[Oldy]

Согласен, что неудобно, но это единственно возможный  способ для InterBase.
Если-бы это был Yaffil или FireBird 1.5 то тогда можно-бы было применить "Execute varchar" или "Execute statement"
см. https://forum.shelek.ru/index.php/topic,3877.0.html

[nikedeforest]

В справочнике 4 поля (одно поле это порядковый номер оно не считается, получается 3 поля). Объясняю почему заполняется одно поле. Кстати заполняться могут и все поля, но заполнение справочника происходит, когда в дочернюю таблицу нужно внести значение которое в справочнике еще не содержиться. Дочерних табдиц несколько, между собой они никак несвязана, но у них есть одинаковые поля. Вот нам нужно внести значение в одну из дочерних таблиц (заполнить надо все поля). Рассмотри 2 варианта:
1) Мы заносим данные во все поля и получается что во все эти поля вносяться значения которых нет в справочнике, тогда в справочнике заполняються все поля.
2) Мы заносим данные во все поля и получается что для одного поля дочерний таблицы такое значение есть в соответствующем ей поле в справочнике, а вот для других полей нет и соответствующие им поля справочника будут заполнены этими недостающими значениями.
Вроде как понятно объяснил ИМХО.
Возможно вас смутит, что в справочнике в некоторых полях будут храниться значения равные NULL, но сразу скажу, что их будет не так много и в принципе неудобств это по-идеи никаких не несет.В придачу NULL места много не сожрет .
Но ответьте на вопрос поставленный мною выше. В MySql можно реализовать то, что мне нужно способом указанным Димкой (ничего что логин по-русски нкаписал?)  через динамический запрос или так, как пытался я (этот способ указан во втором посте этой темы), что было бы еще лучше. 
P.s. Oldy, ссылку еще не смотрел, после того как отправлю пост посмотрю.
Кабздец видать моему экзамену, совсем не могу сосредоточиться

[nikedeforest]

Oldy, ты той ссылкой очердной раз доказал, что нельзя в IB и в FB 1.0.3(с которой я не знаком и пока нет желания знакомиться)  в запросы select, insert и в фиг еще знает какие передать вместо имени поля переменную. Но скажи, можно ли это сделать в MySql и можно ли использовать динамический запрос в ХП в MySql. Если не ответите, то умру ведь неучем . 

[Dimka]

nikedeforest, ничего не понял. Давай задачу не на табличках (одна, другая), а по тем реальным данным, что там хранятся - предметную область. У меня сильнейшее предчувствие, что ты слишком уж замудрил со структурой.

[nikedeforest]

Вот предметная область
В первой таблице имя которой str_pr(строящееся жилье) содержиться 10 полей, я перечислю первые 3, остальные значения не имеют для данной задачи
Поле №1 rayon(район) Поле№2 tip_doma(тип дома) Поле №3 sanuzel(санузел)

Вторая таблица liv_pr(жилой комплекс), в ней 11 полей, но первые 3 такие же как в предидущей таблице, я их перечислять не буду.

Третья таблица kmz_pr(комплекс малоэтажной застройки), в ней 16 полей, но требуеться только первое, которое называется rayon(район).

Таблица справочник имя которой sprav,  состоит из трех полей:
Поле №1 rayon(район) Поле№2 tip_doma(тип дома) Поле №3 sanuzel(санузел)

К каждой таблице приплюсуйте поле pn(порядковый номер), которе по сути является первым, но его думаю можно не учитывать, это так чтоб имели ввиду.
Вроде все. Надеюсь теперь понятно.
dimka,  я тебя умоляю ответь про вопрос о MySql, я думаю это щас важнее. Если можно выполнить эту хранимую процедуру (неважно с динамическим запросом или без), то в принципе все в порядке.

[Dimka]

знал бы - ответил бы Попробуй.

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

[Oldy]

nikedeforest , боюсь что возможности MySql еще беднее чем у InterBase. Вот все, что я нашел  http://www.mysql.ru/docs/man/Variables.html по использованию переменных в запросе. Может плохо искал? 

[HandKot]

nikedeforest
есть такая идея:
в справочнике всего 3 поля key - ключ, name_polya - имя поля, znach - значние (твою таблицу справочник положили боком, если можно так выразится)
пример:
key Name_polya Znach
1    Поле1       ффф
1    Поле2       ыыы
2    Поле3       ччч
3    Поле1       ккк
может так решится твоя проблема и с запросом легче будет 

а использование динамических запросов - это результат неправильной структуры данных и плохой признак программирования ИМХО

ЗЫЖ я использую динамические запросы только на клиентской стороне (программа на VB)

[nikedeforest]

. А вот назначение справочника я не понял. Какой интерес хранить всевозможные сочетания районов, типов домов и санузлов?

В целях избежания избыточности данных. (по крайней мере меня так учили и другого предназначения я для него не вижу ). Кстати в каких еще случаях применяеться справочник?
А оправдать свой справочник я могу таким аргументом (только не смейтесь): эти 3 поля в справочнике являються емкими по ресурсам (ну типа того, что жрут они много, конечно относительно). В придачу число значений в этих полях ограниченно, т.е. районов же не бесконечно много, типов санузлов тоже. А вноситься же эти значения будут всегда, поэтому я решил сэкономить место, храня значения типа varchar в справочнике,
а в дочерних таблицах будут значения типа integer(место много не жрет) и будут эти значения равными значения порядкового номера в таблце-справочнике. Надеюсь не запутал вас .
Oldy, сейчас почитаю, заранее спасибо.
HandKot, ты кажется перепутал или я тебя неправильно понял, но в справочнике поля такие

pn     rayon         tip_doma        sanuzel
1    Ленинский       БК              любой
2

а использование динамических запросов - это результат неправильной структуры данных и плохой признак программирования ИМХО

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

[Dimka]

а использование динамических запросов - это результат неправильной структуры данных и плохой признак программирования ИМХО

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

nikedeforest, ты, пытаясь улучшить одно, ухудшил другое Твой справочник (если убрать искусственный первичный ключ) даже в 2НФ не находится, либо ключом полагать все 3 поля. Если на район приходится несколько типов домов - район будет повторяться, а тип дома не будет полностью функционально зависеть от района. Если же за первичный ключ брать район и тип дома вместе, то опять не будет полной функциональной зависимости санузла. Это 3 различных справочника, 3 различные таблицы, 3 вида данных различной природы, совершенно не связанных между собой сами по себе (не имеющие функциональной зависимости), а лишь опосредовано, через таблицы, которые их используют. И не надо никаких ХП с извращениями, следовательно. Экономия оправдана чем? Получается (если полагать, что справочник в 3НФ находится), что у тебя в районе может быть лишь 1 уникальный (для всех районов) тип дома, и 1 уникальный (для всех типов домов) санузел. На основании здравого смысла (из обычной жизни) могу сказать, что такого не бывает .

[nikedeforest]

dimka, я не понял. Ты предлагаешь вместо одного справочника завести 3. Вообще у меня сложилось чувство, что мы друг  друга не поняли. Понимаешь, мне ненужна взаимосвязь между этими полям в справочнике, мне нужно всего лишь порядковый номер определенного нахождения записи в справочнике, т.е. если Ленинский район у меня в справочнике находиться во второй записи, то я передаю в дочернюю таблицу в поле район номер два, при этом нужная запись типа дома (tip_doma) может находиться под цифрой 4 в справочнике и следовательно в дочернюю таблицу в поле тип дома я внесу значение 4. Ты меня правильно понял???
 Слушай что я нашел, оказывается поддержка хранимых процедур в MySql планировалась ввестись в версии 5.0., у меня кажеться по старее. Я просто пытался внести ХП и ничего не выходило, поискал, порылся и вот, блин узнал. Вопрос возникает, а что так поздно эти уроды опомнились?Щас буду смотреть какой версии мой MySql.

[Dimka]

nikedeforest, но ты понимаешь, что совпадение номеров района и типа дома и санузла - это случайное совпадние? По сути, у тебя будут записи, где заполнено лишь 1 поле, а остальные свободные. И в дочернюю таблицу ты передаёшь ссылку на строку 2 для района, ссылку на строку 4 для типа дома и т.п. Эти ссылки могут вести в 3 разные справочника, и нет никаких проблем с выбором полей и всем прочим. Обилие null совершенно не экономит место. Более того, в рамках такой структуры ты совершенно не можешь отделить по ключу районы от типов и санузлов. Получается полная каша, по которой даже нормальный select не построить без заумных условий выборки.

Если же ты выносишь сочетание (именно сочетание) район-тип-санузел в отдельную таблицу, где район и тип и санузел могут повторяться, то это не справочник совершенно, для этой таблицы тебе нужно будет завести 3 справочника, чтобы не вляпаться в аномалии удаления и обновления и банальные опечатки пользователей. В этом случае твой справочник не нормализован.

В общем, что в первом, что во втором случаях, структура не оправдана. В обоих случаях (если исправить) ХП не нужно или нужно без динамического кода.

[nikedeforest]

Установил себе MySql версии 5.0.1 кажется. ХП поддерживаются, но синтаксис языка непонятный, я как не старался единственной что смог сделать

Код:

create procedure test
begin
select * from sotr;
end

Это заработало, а вот что не вышло

Код:

create procedure test
return (s integer)
as
begin
s=2;
end 

пробовал s:=2, также пробовал returns и прочее.
Короче вывод.
Прошу, покажите мне где можно взять инфу по синтаксису MySql (интересует все, но в первую очередь ХП и триггеры) версии выше 5, желательно на русском, но за неимением такой согласен посмотреть код в какой-нибудь нерусской книженке.

[nikedeforest]

nikedeforest, но ты понимаешь, что совпадение номеров района и типа дома и санузла - это случайное совпадние?

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

[nikedeforest]

Документация по MySql версии выше 5 меня все еще интересует

[MOPO3]

Документация по MySql версии выше 5 меня все еще интересует

Хммм, это где ты такой нашол мускул то ? На официальном сайте есть только MySQL 5.0, и то  Development release с пометкой : use this for previewing and testing new features )
Ничего не путаеш ? Стабильный релиз пока только 4,1 версии

[Dimka]

nikedeforest, везде, давно и постоянно. В таблице состоящей из двух полей ID и Name нет ничего преступного .

[nikedeforest]

Мороз, подожди щас проверю. А есть какой нибудь запрос, чтобы его набрать в консоли и получить версию?

[MOPO3]

nikedeforest, в какой консоли ? у тебя фриБСД или Линукс какой-нибудь ?

[MOPO3]

Под фришкой : pkg_info | grep mysql
Под редхатом : rpm -q | grep mysql

[nikedeforest]

М-да, папка  которую парниша знакомый прислал называется

mysql-5.0.1-alpha-snapshot-win

Да и хранимую процедуру, пусть и простенькую, но ведь заглотил. А поддержка ХП была обещана только в версии 5.0.
Вот еще на WinMySQLAdmin
Нашел такую надпись

Server info 5.0.1-alpha-nt

поэтому вроде как не перепутал. Так видать не судьба мне с хранимой процедурой поработать (документации по видимому нет), а жаль. Хотя может оно и к лучшему, будь проще и люди к тебе потянуться .

[nikedeforest]

Я имел ввиду в консоли где набираються SQL запросы.

[HandKot]

nikedeforest
я имел ввиду, что в справочнике будет всего три поля
КЛЮЧ (его ты хранишь в других таблицах)
ИМЯ ТАБЛИЦЫ (ПАРАМЕТРА в твоем случае это либо, "rayon", либо "tip_doma", либо "sanuzel"
)
ЗНАЧЕНИЕ (собствеено значения)

данная структура более менее гибкая и позволяет добавлять новые таблицы без изменения самого справочника

пример

pn          name               znach
1           'rayon'             'Ленинский'
1           'tip_doma'          'БК'
1           'sanuzel'           'Любой'
2           'rayon'             'Сталинский'
2           'sanuzel'            'Раздельный'
...
и т.д.

хотя лучше повторения поля PN лучше не делать

[nikedeforest]

dimka, кажется ты меня убедил. Надо будет с наукой посогласоваться (привести к нормальным формам). Напишите же мне как еще используються справочники.

[nikedeforest]

HandKot, не очень понял, щас еще поразмышляю (видать торможу).

[nikedeforest]

HandKot,ИМХо неудачная идея, записсей будетмного в справочнике. Зачем, если это можно избежать.

[Dimka]

HandKot, неудачная идея. Справочник не обязательно одно текстовое поле Name хранит. Твоя структура совершенно не расширяема. Вдруг мне для санузла кроме названия ещё что-то добавить захочется? В твоей структуре потребуется добавлять новое поле, которое отнесётся также и к районам и к типам домов, что неверно и черевато ошибками и непонятностями. Структура должна быть такой, чтобы любой специалист, прочитав, смог более-менее понять предметную область. Отклонение от этого правила оправдана лишь жёсткими требованиями к производительности и ограниченностью ресурсов. На современных персональных машинах или серверах не массового пользования (например, маленького или среднего предприятия) можно считать, ограничений нет (для данной задачи, если, конечно, не по всей стране данные собираются).

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

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

Особый случай со справочниками возникает тогда, когда элементы справочника меняются, но история должна сохраниться в базе. В таком случае справочником выступает пара таблиц: одна содержит некие неизменные понятия (часто абстрактные), а вторая историю изменения значений. Остальные таблицы привязываются к записям истории. Например, есть таблица данных, связанная со справочником налогов, где хранятся название налога и ставка. Что-то там в таблицу вносится до 1 января, считается, всё хорошо. С 1 января ставка налога изменилась. Старые данные требуют для вычисления старую ставку, а новые уже новую. Выходит 2 ставки на 1 налог. Тогда ставка выносится в таблицу истории значений, в историю добавляется 2 даты: начало и окончание действия значения, а само название налога остаётся в главной таблице справочника. В такой структуре уже не страшно менять ставки налога - базу переделывать не придётся. Пример не совсем удачный, так как в подавляющем большинстве случаев все расчёты делаются при внесении записей в БД и результаты расчётов также сохраняются, поэтому в реальной жизни сложный справочник налогов мало где применим.

[nikedeforest]

dimka, спасибо, просвятил.

[nikedeforest]

dimka, я тутчего подумал. Если 3 справочника делать, то получается в каждом справочнике по полю pn (порядковый номер), получается на 2 поля больше. 

[Dimka]

и чего?

[nikedeforest]

и чего?

Так ведь, справочник создавался для избежания избыточности, но при этом он сам начинает создавать эту её самую.
Блин, тяжело найти золотую середину.

[Sla]

Я, например, не вижу избыточности.
справочник это статические таблицы (один раз создал и забыл)
а то что ты предлагал в первом посте (а может в последующем), вот там и присутствует избыточность.
И так ли себя оправдает динамический запрос, как ты его  представляешь?
а идея Hadkotа вполне приемлема.
И почему ты считаешь, что в справочнике будет много записей? Много - это сколько?

[Dimka]

Справочники создаются по крайней мере по двум причинам:

1) нормализация (когда значения, хранящиеся в справочнике, включены в несколько отношений);

2) аномалии (данные текстовых или двоичных типов не должны повторяться, если используются в качестве ключей поиска или имеют более узкие домены, нежели типы этих данных). Там, где пользователь может сделать опечатки и получить расхождение по форме данных, имеющих одну суть. Там, где в пользовательском интерфейсе имеются combo-box или list для выбора одного или нескольких значений из ограниченного набора (но иногда не обязательно).

[nikedeforest]

Ответ на пост SLA.

справочник это статические таблицы (один раз создал и забыл)

Разве статические??? Вроде как, за время жизни БД справочник изменяеться (по крайней мере записей в нем прибавляються), я не исключаю статические справочники, но насколько я знаю, справочники бывают динамическими. Если я не прав, поправьте.

а то что ты предлагал в первом посте (а может в последующем), вот там и присутствует избыточность.

я не спорю, но я чего-то не разглядел. Как dimka не старался мне объяснить и нф припомнил, я с ним в принципе согласен, но почему-то мне понравилась моя идея и теперь пытаюсь избавиться от неё. Честно сказать, я не обращался к НФ когда решил делать, тот 4ех столбцовый справочник, а руководствовался чисто интуитвными доводами.

И так ли себя оправдает динамический запрос, как ты его  представляешь?

НЕ ЗНАЮ. Я же писал, что при динамические запросы узнал недавно и поработать мне с ними не пришлось еще. Я не работал с ними, не знаю в каких случаях их следует применять, а в каких нет.
Вот вопрос кстати: динамические запросы используються вне хранимых процедур??? (Пример, если можно и синтаксический и смысловой).

а идея Hadkotа вполне приемлема.

Я ее не сразу догнал, меня смутили повторения в поле pn.Пока Я эту идею не выбрасываю из рассмотрения.

Много - это сколько?

здесь теория относительности . Много-больше чем ранее .

[nikedeforest]

dimka, понял, точнее перевариваю.

[Sla]

Когда я говорил про статические, я подразумевал - редко меняющиеся (ты часто меняешь словари?)
Пример динамического запроса можно конечно привести, но он на plsql, тебе оно надо?

Много - это когда сложно сказать сколько
В словаре Эллочки -35 слов (это много?)
В словаре Пушкина - 3500 слов (это много?)
В русском словаре - ну пусть будет 35000 слов (это много?)

а в твоем случае - немного, по крайней мере, их можно перечислить

[Dimka]

Пример аномалии.

Пусть есть отношение, где неключевым атрибутом является улица (как текст). Пусть "Московская" повторяется много раз. Пользователь вводит название вручную. Один из юзеров сделал опечатку "Масковская", система внесла в таблицу. Получаем 2 разных по форме улицы, но логически относящиеся к одному объекту реального мира. Ищем данные по улице "Московская" - будут пропущены все с опечатками. Практика показывает, что улицы с названиями длиной около 8 букв в таблицах с несколькими тысячами записей могут иметь до 25 разночтений, что приводит БД в неработоспосбное состояние (толку от такой БД нет, работать с ней невозможно).

Как проблема решается. Создаётся справочник улиц, где написание улицы "Московская" однозначно. Ключами являются числа. Пользователю предоставляется combo-box со списком улиц вместо text-box, где он ранее набивал название, и в меню выносится ссылка на отдельный справочник улиц: открывается список улиц со стандартными функциями добавления новой, редактирования имеющейся, удаления старой.

В случае переименования улицы достаточно изменить 1 запись справочника, вместо того чтобы прочёсывать всю базу данных в поисках названия этой улицы.

[HandKot]

2 nikedeforest, поторения поля pn необязательны и я сделал так токо из-за твоего желания иметь некоторый набор данных с одним ключем (вариант когда в одной записи забито данных больше чем в одном столбце), хоть это и действительно непонятно зачем

2 dimka идея действительно неудачная и сразу было понятно, что надо делать кучу справочников. Я просто предложил примерное решение под поставленную задачу не меняя кардинально исходных данных (замену одной таблиц  4 ) и для облегчения написания запросов в ХП с чего в принципе все и началось...

[nikedeforest]

Теперь про ХП можно забыть.
Во-первых MySQL не поддерживает, во-вторых с тремя таблицами-справочниками в ХП нет смысла ИМХО.