Использование естественных ключей допустимо?

← →
by © (2004-10-12 11:39) [0]

Есть некий набор таблиц, который описывает реальные таблицы БД. Его можно реализовать в двух вариантах:
1)
таблица META_TABLE
ID TABLE_NAME DESCRIPTION
1 GOODS Товары

таблица META_FIELD
ID META_TABLE_ID FIELD_NAME DESCRIPTION
1 1 ID Ключ
2 1 NAME Наименование
т.е. все сслылки идут через числовое ID

2)
таблица META_TABLE
TABLE_NAME DESCRIPTION
GOODS Товары

таблица META_FIELD
TABLE_NAME FIELD_NAME DESCRIPTION
GOODS ID Ключ
GOODS NAME Наименование
т.е. все сслылки идут через реальное наименование.

Какой вариант предпочтительней? Какие могут быть подводные камни?
Сущности которые используются как естественные ключи заведомо уникальны (имя таблицы БД, связка имени таблицы и имени поля)

← →
Думкин © (2004-10-12 11:41) [1]

http://www.podgoretsky.com/cgi-bin/dlcounter/npscnt?file=http://podgoretsky.com/ftp/Docs/Delphi/Tenser/10/index.html&fil e_id=Tenser10

← →
DiamondShark © (2004-10-12 11:53) [2]

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

Вот кусочек системы метаданных из ASA

SYSTABLE
---------------
table_id
file_id Foreign key references SYS.SYSFILE
count
first_page
last_page
primary_root
creator Foreign key references SYS.SYSUSERPERM. user_id
table_name
table_type
view_def
remarks
replicate
existing_obj
remote_location
remote_objtype
srvid

SYSCOLUMN
---------------
table_id Primary key, foreign key references SYS.SYSTABLE.table_id
column_id Primary key, foreign key references SYS.SYSIXCOL
pkey
domain_id foreign key references SYS.SYSDOMAIN.domain_id
nulls
width
scale
estimate
column_name
remarks
default
check
user_type Foreign key references SYS.SYSUSERTYPE. type_id
format_str
column_type
remote_name
remote_type

← →
Mystic © (2004-10-12 12:10) [3]

1) GOODS так-то нехорошо. Настравивает на то, что поле хранит информацию о нескольких товарах.

2) Предполагается ли в будущем, что наименование товара может измениться? Если да, то по ID однозначно.

3) Если товаров не много (около десяти), и в запросах часто используется фильтрация по товару (обычно это в случае происходит в случае типов товаров), я считаю можно связываться на по ID, а по текстовому коду. Скажем, если предположить, что типов товаров около десяти, часто для каждого товара происходит особый расчет (CASE GOOD_TYPE WHEN ... ), то вполне даже симпатично смотрится структура:

CREATE TABLE GOOD_TYPE ( ID INT NOT NULL, CODE CHAR(10) NOT NULL, NAME VARCHAR(100), PRIMARY KEY(ID) ) CREATE TABLE GOOD ( ID INT NOT NULL, TYPE CHAR(10), PRIMARY KEY(ID), FOREIGN KEY FK_DOOF_TYPE (TYPE) REFERENCES GOOD_TYPE )

← →
ЮЮ © (2004-10-12 12:19) [4]

А разве такое возможно
FOREIGN KEY FK_DOOF_TYPE (TYPE/*CHAR(10)*/) REFERENCES GOOD_TYPE /* где PRIMARY KEY по полю INT */ ???

← →
DiamondShark © (2004-10-12 12:31) [5]

> ЮЮ © (12.10.04 12:19) [4]
> А разве такое возможно

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

← →
by © (2004-10-12 12:39) [6]

Mystic © (12.10.04 12:10)
GOODS это название таблицы в БД, а таблицы META_TABLE И META_FIELD, которые содержат естественные ключи - используются для описания структуры талицы GOODS для использования в клиенте. Все естественные ключи - это уникалmные на уровне БД значения.
А в самой таблице GOODS будет сурогатный ключ
ID NAME
1 кирпич
2 цемент

Я всегда использовал сурогатные ключи, а вопрос о естественных возник из такой проблемы. Есть набор таблиц для описания метаданных системы, эти описания могут правится разными разработчиками. Возникает проблема слияния изменения. Если использовать ID как integer через генератор (у меня БД FireBird), то будут совпадения. В одной из веток была идея об использовании GUID, но это менее удобно, совсем не информативно и тоже текст а не быстрый integer.
В этой ситуации естественные ключи обеспечивают уникальность, но как-то они мне не привычны.

← →
by © (2004-10-12 12:41) [7]

Во всеж же таблицах с данными используются только сурогатные ключи. Естественные ключи - только в систамных таблицах для описания метаданных.

← →
Mystic © (2004-10-12 12:57) [8]

Ну... тогда лучше ставить вопрос о репликации данных (обновление базы данных - приемника на основе данных базы данных - источника). Желательно вначале изучить литературу по этой теме, опыт у меня только практический. Надо сказать, что задача не тривиальна и многое зависит от структуры тех данных, которые надо реплицировать. То, что ты приводил в качестве примера, это реальный физический пример или просто для иллюстрации?

Я бы оставил везде структуру с суррогатными ключами и двигался в сторону написанияутилит по репликации данных. Тут же вопрос: насколько часто это должно происходить?

← →
by © (2004-10-12 13:23) [9]

Mystic © (12.10.04 12:57)
Это реальный пример.
Наверно таки действительно нужно смотреть в сторону репликации. Причем репликация не Клиент БД - Центральная БД - Центральная БД - Клиент БД, т.е. все через центральную, а хуже. Есть часть данных которая должна быть у всех одинакова и часть данных у каждого своя. И все это в одной структуре таблиц. Можно конечно дублировать структуру таблиц на системные ядра и системные для разработчиков клиентов, но это не красиво.

← →
Mystic © (2004-10-12 13:31) [10]

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

В целом на начальном этапе можно обойтись набором эвристик (построение таблиц соответствия, ...)

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

← →
by © (2004-10-12 13:46) [11]

Дело в том что в таблице есть часть данных, которая общая для всех клиентских БД и не меняется ими и часть данных которые в клиентских БД меняются. Т.е. для системных данных репликация состоит в полной замене данных клиентской БД эталонными.
Сейчас пришла в голову идея. Все системные данные имею признак True в поле IS_SYSTEM_DATA, а пользовательские False. Используется сурогатный ключ для ID. Но значение ID генерируется двумя генераторами, в зависимости от IS_SYSTEM_DATA. Чтобы диапазоны генераторов не пересеклись, значение генератора для системных данных умножается на -1, т.е. системные ID отрицательные.
Если не прийдет в голову ничего лучше, попробую реализовать.

← →
Johnmen © (2004-10-12 14:02) [12]

Суррогатные ключи против натуральных
http://www.ibase.ru/devinfo/NaturalKeysVersusAtrificialKeysByTentser.html

← →
by © (2004-10-12 14:05) [13]

Johnmen © (12.10.04 14:02)
Читал я это. И использую всегда суррогатные ключи. Но возник вопрос таблиц которые правятся в разных БД, но должны быть сихронизированы. И как один из способов решения и возникла идея о естественных ключах.

← →
Mystic © (2004-10-12 14:57) [14]

Имхо, проще задвать диапазоны ID. Например, ID < 1000000000 это системные, а все остальные начинаются с 1000000000...

Вообще, для синхронизации, имхо, проще строить временные таблицы соответствия ID

← →
by © (2004-10-12 15:55) [15]

Будем пробовать, а когда наткнемся на нерешаемое, то достанем книжку по Рефакторингу )))

← →
kaif © (2004-10-12 17:38) [16]

Если Вы уверены, что этими двумя таблицами все метаданные ограничатся навсегда - можете смело обходиться без суррогатных ключей. Если хот сколько-нибудь сомневаетесь, что дело ограничится этими двумя таблицами метаданных - рекомендую на всякий пожарный заготовить суррогатные ключи. Тем более, что вреда от них точно нет.
Любопытно, что в системных таблицах IB практически не встретишь полей ID. Мне это больше доставляет неудобств, чем приносит пользы. Например, мне не пришлось бы хранить ни имена таблиц, ни имена полей в моих таблицах метаданных, если бы в системных таблицах были ID этих объектов. Согласитесь, что
это было бы удобнее, если нужно, например, переименовать поле таблицы - можно было бы просто переименовать его в самой базе, а метаданные подсистемы править уже было бы ненужно. А так мне приходится в этой ситуации править в двух местах: выполнять alter table ... alter column и плюс к этому update <моя системная таблица> set <соотв. поле> = <новое имя>. Учитывая то, что сервера не любят в одной транзакции манипуляции с данными и метаданными, приходится вместо простого отката в результате неудавшихся изменений прибегать к извращениям. У меня программа много работает с метаданными (она сама есть платформа для других задач) и отсутствие прозрачных суррогатных ключей в системных таблицах IB меня сильно стесняет.

С другой стороны, если бы системные таблицы IB были построены на суррогатных ключах, то они стали бы совершенно нечитабельными. Так что здесь палка о двух концах. Гораздо приятнее сделать select Из системной таблицы RDB$FIELDS и увидеть имена полей, чем какие-то ID, которые один на другом сидит и третьим погоняет...
Так что когда речь идет о метаданных еще важно, какая цель преследуется. Нужно ли, чтобы потом эти таблицы "легко читались" разработчиком визуально или это вообще неважно - речь идет о какой-то прикладной системе, в которой можно все "скрыть за кадром"

А зачем им легко читаться?

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

kaif © (12.10.04 17:38)
а Вы в своей программе метаданные метаданных жестко вписали в код программы или берете из БД?

> Но у естесвенных ключей большим плосом является "читаемость",
> так как работать с этими данными прийдется разработчику.

Сделайте читабельный view.
Да и работать разработчик будет, всё-таки, не столько с таблицами, сколько с инструментом. Так ведь?

Очень правильная идея DiamondShark © (12.10.04 18:21) [20]

2 by ©
Не совсем понял Ваш вопрос. Если хотите взглянуть на то, как устроены мои метаданные, например, сведения о таблицах-справочниках:

http://www.gaapinvest.com/allegro/doc/book1/doc026.html

Как видите, я широко использую суррогатные ключи.
При этом читабельность получается достаточно хорошей, так как все же метаданные устроены обычно гораздо проще, чем сами данные.
У меня программа знает, какие метаданные что означают. То есть она не является совсем уж универсальной системой. Программа в курсе того, что в таблице CLASS лежат метаданные справочников, в CLASS_FIELDS метаданные полей справочников, в DOC_TYPE список типов документов, а в DOC_TABLES - список таблиц которые используются для хранения документов.
С апдейтами у меня, как ни странно, проблем вообще нет.
С каждой новой версией ядра системы развивается библиотека DbUpdate.dll. Когда пользователь подключается к базе данных в первую очередь считывается версия ее "ядра" из определенной таблицы. Если, например, версия ядра 5, а EXE-файл программы рассчитан на работу уже с версией 9, то происходит:
-запрос об "апгрейте версии ядра базы" пользователю
-если он отказался - соединение с базой закрывается и работать с ней невозможно (и соответственно, "испортить")
-если согласился, то динамически грузится файл DbUpdate.dll, который последовательно апдейтит ядро от версии 5 до версии 9
5->6
6->7
7->8
8->9
-таким образом мне всегда достаточно подготовить и тщательно отладить лишь один (последний) апдейт. Предыдущие я просто не трогаю. Программа вызыввает из DbUpdate.dll всего одну функцию, передавая в нее в качестве параметров версию, с которой нужно начать и версию, которой нужно закончить, а также указатель на callback-функцию (обратного вызова) для отображения сообщений о процессе апдейта в окне основной программы.
---------------------
Любопытно, что я очень боялся, что приду в какой-то момент к противоречиям, но этого пока не произошло.
Фактически сейчас будет опубликована уже 9-ая версия ядра, которая в некоторых местах очень сильно отличается от первоначальной версии 1. Но все апдейты происходят бес сбоев, конфигурации, созданные в системе продолжают функционировать правильно, хотя некоторые метаданные уже хранятся иначе, чем это было раньше.

Если версия ядра выше, чем версия EXE-файла программы Allegro, то соединение с такой базой просто невозможно - программа сразу говорит, что соединение невозможно - скачайте апдейт самой программы (то есть инсталлятор, который поставит последнюю версию Allegro.exe и DbUpdate.dll)
--------------
Если Вас волнуют апдейты, лучше найти хорошую стратегию апдейтов, а не колебаться между суррогатными и реальными ключами, так как это для апдейтов как раз совершенно неважно.

Если таблиц больше 10 и их число может дальше расти - я бы без колебаний отдал предпочтение суррогатным ключам.

Использование естественных ключей допустимо? Найти похожие ветки