Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
Gluk123
Member

Откуда:
Сообщений: 6
Хочется увидеть именно алгоритм выбора! Т.е. как определить, в каком случае использовать суррогатный, а каком естественный ключи. По возможности отвечать структурировано и развернуто.

Поэтому просьба ко всем:
  • НЕ УСТРАИВАТЬ РЕЛИГИОЗНЫЕ ВОЙНЫ НА ТЕМУ: «ЧЕМ СУРРОГАТНЫЕ КЛЮЧИ ЛУЧШЕ / ХУЖЕ ЕСТЕСТВЕННЫХ КЛЮЧЕЙ»
  • НЕ ПОСТИТЬ МНОГОЗНАЧИТЕЛЬНЫХ БЕССМЫСЛЕННЫХ ФРАЗ (типа: «ну, это зависит…»), А ПИСАТЬ ТАК, ЧТОБЫ БЫЛА ПОНЯТНА МЫСЛЬ АВТОРА
  • НЕ КИДАТЬСЯ ТОМАМИ КЛАСИКОВ ДРУГ В ДРУГА И ПРОСТО ПРИВЕСТИ АЛГОРИТМ, КОТОРЫЙ ВЫ ИСПОЛЬЗУЕТЕ
  • 18 янв 12, 10:09    [11922811]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    aleks2
    Guest
    Предлагаю дружно продинамить тредстартера.
    18 янв 12, 10:10    [11922821]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    kDnZP
    Member [заблокирован]

    Откуда: ★[msg=16399436]★[msg=20850760]
    Сообщений: 11289
    aleks2, да ладно. Просто ТС надобно указать направление...
    Сюда: Сравнение СУБД, сюда: Проектирование БД, а еще лучше сюда: Форум друзей сходите. Где-то точно помогут.
    18 янв 12, 10:19    [11922876]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    baracs
    Member

    Откуда: Москва
    Сообщений: 7204
    Gluk123, а чего вы орете-то?


    Мне, вот, хочется в Черногории на горных лыжах покататься...
    18 янв 12, 10:24    [11922916]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    Gluk123
    Member

    Откуда:
    Сообщений: 6
    Для затравочки помещаю алгоритм с одного из буржуйских сайтов [url=]http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/[/url]:

    При выборе PK для таблицы разработчик должен придерживаться нескольких требований к PK:
    1. PK не может включать нулевые значения (т.е. NOT NULL), и должен однозначно определять каждую запись отношения;
    2. PK должен быть компактным и включать в себя как можно меньшее количество атрибутов (идеально PK должен состоять из одного атрибута). При использовании составных ключей удостоверьтесь, что композитный ключ состоит из суррогатных ключей и использует тип данных семейства целочисленных (integer);
    3. PK должен быть устойчивым (поля первичного ключа должны оставаться неизменными).

    САМ АЛГОРИТМ ВЫБОРА:
    1. Если мы можем подобрать подходящий естественный ключ отвечающий всем трем критериям, то мы должны использовать его. Справочные таблицы и таблицы конфигурации обычно отвечают этим требованиям;
    2. Тип данных для PK: чем меньше, тем лучше, выбирайте integer или short character тип данных. Это также обеспечит более быстрые операции объединения (join). Это становится еще более важным, если Вы собираетесь использовать PK с кластеризованным индексом, так как некластеризованные индексы созданы от кластеризованных. СУБД обрабатывает значения целочисленных данных быстрее чем символьные значения данных, потому что он преобразовывает символы в ASCII эквивалентные значения перед обработкой, что является дополнительным шагом обработки.
    18 янв 12, 10:37    [11923011]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    kDnZP
    Member [заблокирован]

    Откуда: ★[msg=16399436]★[msg=20850760]
    Сообщений: 11289
    Gluk123, а еще вас можно послать в поиск. Знаете где это?
    18 янв 12, 10:44    [11923063]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    Gluk123
    Member

    Откуда:
    Сообщений: 6
    Еще один вариант (см. здесь [url=]http://beyondrelational.com/quiz/sqlserver/general/2010/questions/sqlserver-quiz-general-2010-madhu-k-nair-surrogate-key-vs-natural-key.aspx[/url]):

    Если поставить вопрос: "Когда использовать суррогатный ключ и когда использовать естественный ключ в качестве первичного ключа?" Я предпочитаю вариант ответа:
    "Когда в сомнении используют Суррогатный ключ!"

    Большинство ЕК не выполняет следующие требования:

    int/bigint
    NOT NULL
    уникальный

    Первые два требования важны для производительности (первичный ключ используется в качестве ссылки через внешние ключи и во всех некластеризованных индексах; в зависимости от размера ЕК дополнительно затрачиваемое количество памяти в физических таблицах и дополнительный индекс для int/bigint - колонки будут очень незначительны в сравнении со свободным местом, которое Вы сэкономите с каждым дальнейшим индексом и каждым внешним ключом).
    Уникальность требуется технически для первичных ключей и не выполняется в DWH-таблицах которые состоят из медленно изменяющихся дименсий.
    Даже когда все требования выполнены, мой опыт говорит мне, что никогда нет гарантии, что пользователь не изменит ЕК в будущем. Если это произойдет, у Вас появятся проблемы, если Вы использовали ЕК в качестве первичного ключа (или, по крайней мере, много работы по изменению PK в таблице, изменению внешних ключей во всех других таблицах и возможно даже исправлению всех запросов).
    Но даже при наличии СК, рекомендуется создать уникальный индекс на ЕК - столбцах в таблицы.

    Если поставить вопрос: "Когда использовать Суррогатный ключ и использовать Естественный Ключ для того, чтобы выполнения объединений (join) или where/having запросах?", то ответ следующий:
    Всегда используйте первичный ключ (СК или ЕК) для выполнения объединений в пределах одной и той же базы данных и всегда используйте ЕК для where/having запросов и для объединений между разными базами данных.
    18 янв 12, 11:23    [11923371]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    Gluk123
    Если поставить вопрос: "Когда использовать Суррогатный ключ и использовать Естественный Ключ для того, чтобы выполнения объединений (join) или where/having запросах?", то ответ следующий:
    Всегда используйте первичный ключ (СК или ЕК) для выполнения объединений в пределах одной и той же базы данных и всегда используйте ЕК для where/having запросов и для объединений между разными базами данных.

    Звучит как "Мойте руки перед едой" или "Ешьте здоровую пищу"
    Что лучше использовать для join/where/having покажет план выполнения. А не указание "сверху"
    18 янв 12, 11:29    [11923431]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    Gluk123
    Member

    Откуда:
    Сообщений: 6
    Glory
    Звучит как "Мойте руки перед едой" или "Ешьте здоровую пищу"
    Что лучше использовать для join/where/having покажет план выполнения. А не указание "сверху"


    Согласен, но попробуйте, исходя из своего опыта, дать свой алгоритм.
    Не делаете же вы все методом "научного тыка" :)
    18 янв 12, 11:48    [11923622]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    ZOOKABAKODER
    Member

    Откуда:
    Сообщений: 178
    Ну если воевать нельзя, то просто скажу что нелюблю Дэйта.

    Естественный ключ
    1. Не стабилен: никогда не знаешь как всё может изменится в ПО, и как это повлияет на формат/размер естественного ключа. И причём предсказать момент этого изменения обычно невозможно, т.к. это не наша зона ответственности это спец по ПО может предсказать, да и он то не всегда в состоянии. Из опыта: из bigint ключь стал char(20), патамушта не только цыферки стали писать, таблица содержала около 100 миллиардов записей + ещё несколько связанных таблиц.
    2. Не оптимизирован: например возьмём код в строку из 10 символов. Если они все значащие (т.е. не точки/запятые/пробелы и т.д.), то в один символ влезет около k = 33 буквы строчные + 33 буквы заглавные + 10 цыфр = 33*2 + 10 = 76 и это для великого русского алфавита, много-языковых кодов я пока не встречал. Таким образом для 10 получаем: k + k^2+...k^10=1'579'440'828'553'962 (надеюсь правильно посчитал); и это без учёта не значащих символов (точек, тирэ и т.п.); без учёта запрещённых кодов или формата: обычно ж код состоит из сегментов каждый из которых со своими буквами/цифрами допустимыми и своим размером. Итого в 10 байт, поднатужившись, можно сунуть 1'579'440'828'553'962 значений ключа, к примеру в 8 байт BIGINT влезает в 5840 больше.
    3. Генерация и проверка значений сложная или невозможная: ну тут вроде всё понятно. Алгоритм генерации естественного ключа обычно посложнее простого инкремента.

    Выбор суррогатного ключа
    Тут важны три вопроса:
    IF. Частота вставок.
    RC. Количество записей
    DE. Наличие доверенного словаря значений.

    Соответственно DE имеет смысл только при отсутствии или редкости IF. Доверенный словарь не могу объяснить что значит, тут как-то обычно интуитивно понятно. Ну если порассуждать, то словарь должен использоваться уже в нескольких местах, должна быть мала вероятность того что в словаре будут вставки и изменения формата. Например: двух/трёх буквенные коды стран, трёхбуквенные коды валют, коды часовых поясов.

    Дальше так:
    * IF=часто, тогда ключ целое число с инкрементом (авто или руками). Выбор конкретного типа зависит и от RC, чем меньше записей ожидается, тем меньше размер ключа буду выбирать. Конкретной формулы нет, по ситуации.
    * IF=редко, тогда если RC <= 20 и DE = нет, то CHAR(1...4) LATIN1_BIN и буквы подбираю так чтоб ассоциировались со значениям. Очень очень удобно буквенные коды использовать, запомнить их легче чем цифры, скорость от них не страдает (если не VAR и размер маленький конечно), плюс ко всему они интуитивно понятны если правильно подобраны. Например: намного быстрее запомнить что Document.State = 'SNG' это подписанный документ, чем запомнить что это Document.State = 3.
    * IF=редко, DE = есть. Смотрю на размер/формат значений словаря. Если в районе CHAR(1...4) получается, то буду использовать словарь; если CHAR(5...8), то по ситуации, например если ключ будет внешним в здоровом реестре, то лучше всётаки целое число.

    ЗЫ: ПО = предметная область
    18 янв 12, 13:29    [11924776]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    Gluk123
    Member

    Откуда:
    Сообщений: 6
    По поводу выбора ключа для сущности (Joe Celko):

    IDENTITY - это не ключ. Ключ, по определению, это подмножество атрибутов, которое однозначно определяет объект. Табличное свойство IDENTITY (это даже не столбец) это количество ФИЗИЧЕСКИХ попыток вставки (попыток, даже не успешных вставок) для ФИЗИЧЕСКОГО сохранения, используемое для таблицы в отдельной инсталляции ПО на отдельно взятом компьютере отдельно взятого выпуска T-SQL.
    Когда Вы ищете ключ, Вы начинаете со стандартов Вашей отрасли. Я считаю, что стандарты покрывают ~80% правильным образом смоделированных систем - VIN, ISBN, UPC, EAN и т.д., которые могут быть проверены через достоверные внешние источники. В былые времена это занимало длительное время, а сегодня Вы можете использовать Google. Стандарты имеют правила и процедуры проверки и верификации.
    Кроме стандартов есть естественные ключи данных, такие как (долгота, широта), идентификатор хранилища, номер кассового аппарата, номер билета и т.д., которые могут удостоверить в действительности (правильности) данных. Это еще ~18 % случаев. Если у Вас нет естественного ключа, то Ваша схема является, вероятно, неправильной.
    Затем, если это не сработало, и мы смотрим на оставшиеся <2 % от всех ситуаций, мы изобретаем ключ с контрольными цифрами, правилами проверки допустимости и ведением контрольных записей в пределах нашего предприятия. Это ужасная головная боль, ЕСЛИ Вы делаете это правильно. Это - то, почему промышленные стандарты существуют - люди устала, от этой головной боли. Хотели бы Вы производить розничную продажу БЕЗ стандартных штрих-кодов UPC на продуктах? Мы делали это в течение многих столетий перед появлением штрих-кодов.
    Так что, Новички предпочитают делать это неправильно. Используют автоинкрементные значения или изобретают другие собственные решения, которые не могут быть проверены или подтверждены, ПОСКОЛЬКУ это легче чем реальный проект RDBMS. Они хотят иметь волшебный, универсальный ответ "едино размерный подходящий для всех случаев", который не требует, чтобы Вы ФАКТИЧЕСКИ поняли исследуемую область.
    Ключ без проверки и подтверждения допустимости приведет только к замусориванию данных. И после всех издержек “Волшебного Универсального Эликсира UID” они все еще должны гарантировать, что реляционный ключ уникален.
    6 фев 12, 23:26    [12044139]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    пролетевший
    Member

    Откуда: от Михал Иваныча
    Сообщений: 1314
    Первичный ключ должен быть:
    - уникальным
    - существовать для всех объектов
    Это по определению. Классный пример VIN - существует и уникален для всех современных автомобилей. Но что делать с чудаками на аниткварных или самодельных ? телега - транспортное средство. Если потребуется зарегистрировать или застраховать, где искать VIN на телеге ? В 30-х годах в германии все детские коляски должны были иметь номер. Уверены что завтра кто нибудь не захочет ввести такую практику ?
    - Должен быть неизменным. Не требование определения, но как только запись попадает в базу, она имеет тенденцию расползаться по репликам, архивам, ETL; поменять через некоторое время почти невозможно. Личный пример с тем же VIN - му**к гаишник при регистрации нашел то ли номер двигателя, то ли шасси и вбил в базу. Сколько мне это гемороя принесло...
    Отсюда - ничего, что заносится человеком, на роль первичного ключа не подходит. Неизбежно некоторое число записей будут с ошибками, которые надо исправлять. Это относится и ко всяким штрих кодам, на сотню хоть один раз да отсканируют не то.
    Реально, чтобы найти естественный ключ надо знать предметную область вдоль и поперек, что в большинстве случаев нереально. Лучшее решение это 2 ключа. Генерируемый для целостности базы и отношений, и бизнес ключ видимый оператору, или используемый для связи с внешними системами.
    7 фев 12, 00:53    [12044376]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    baracs
    Member

    Откуда: Москва
    Сообщений: 7204
    Gluk123,

    А идите вы, милейший, в поиск!

    А еще, поступало тут, лет 10 назад, хорошее предоложение
    7 фев 12, 10:53    [12045291]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    _Shakill
    Guest
    пролетевший
    Лучшее решение это 2 ключа. Генерируемый для целостности базы и отношений, и бизнес ключ видимый оператору, или используемый для связи с внешними системами.

    +1. человеку удобнее работать с ЕК, внутри базы проще иметь дело с СК
    7 фев 12, 11:04    [12045356]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    Gluk123
    Member

    Откуда:
    Сообщений: 6
    baracs
    Gluk123,

    А идите вы, милейший, в поиск!


    Да, я то собственно из поиска :)

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

    Я интересуюсь мнением людей которые используют два или три вида ключей, и то как они производят свой _выбор_! Т.е. именно АЛГОРИТМ выбора между суррогатным, искусственным и естественным. Сторонникам одностороннего подхода огромная просьба: ПРОПУСТИТЕ ЭТУ ВЕТКУ!

    Я НЬЮБ в РБД, хоть и теоретически подкованный и пытаюсь сейчас подобрать для себя _алгоритм_ разработки РБД. Все 3 уровня: концептуальный, логический и физический. Введение суррогатов целесообразно начиная с физического уровня проектирования.
    Разрабатываю OLTP в целях "набить руку", а факторов для выбора ключа очень много, вот и хочу не изобретать велосипед, а узнать кто и как выбирает тип ключа.

    Ну и хотелось бы облегчить жизнь тем, кто как и я будут "корячиться" пытаясь совместить теорию с практикой ;)
    В русскоязычном пространстве я так и не нашел нормального обсуждения этой тематики, именно с практической точки зрения, а не с теоретической.
    7 фев 12, 13:45    [12046694]     Ответить | Цитировать Сообщить модератору
     Re: АЛГОРИТМ ВЫБОРА между естественным и суррогатным ключом  [new]
    baracs
    Member

    Откуда: Москва
    Сообщений: 7204
    Gluk123
    baracs
    Gluk123,

    А идите вы, милейший, в поиск!


    Да, я то собственно из поиска :)
    Зачем, тогда, в надцатый раз выкладывать многократно обсужденный текст Селко?
    Gluk123
    Я интересуюсь мнением людей которые используют два или три вида ключей, и то как они производят свой _выбор_! Т.е. именно АЛГОРИТМ выбора между суррогатным, искусственным и естественным.

    Ох, ну что такое "естественный ключ"?
    В большинстве случаев, это тот же суррогатный ключ, но сгенерённый в какой-то другой, внешней, неподконтрольной нам, системе.

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

    Во-вторых, эта система может быть не единственной.

    В-третьих, любая созданная человеком система, не вечна.

    И т.д. И т.п.

    Вобщем, если досконально изучив предметную область, вы нашли стопудово надежный, практически вечный, естественный ключ. И вам удобно его использовать, то ради бога!
    7 фев 12, 18:11    [12049425]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить