Проектирование классов с нестандартной реализацией

Необходимо иметь класс "Физлицо", описывающийся аттрибутами:

    Persons:
  • id int
  • Family varchar(50)
  • Name varchar(50)
  • Surname varchar(50)

Но в силу того что таблица будет огромной, то целесообразно реализовать его "внутри" как

    Persons
  • id int
  • Family_id int
  • Name_id int
  • Surname_id int
    Families
  • id int
  • Family varchar(50)
    Names
  • id int
  • Name varchar(50)
    Surnames
  • id int
  • Surname varchar(50)

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

Возможно ли в рамках вашей концепции описание такого класса с данной реализацией?

Forums: 

или все-таки -

или все-таки - это должен быть набор из 4х классов!?

Изображение пользователя st.

Справочники

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

В аналитической БД - другое дело, справочники становятся измерениями, а значения в таблице фактов кодируются.

Тут я с вами не

Тут я с вами не могу согласиться - база данных на 43 млн физлиц весит более 65Гиг, в том виде который сделал я - 15Гиг. На маленьких объемах хранение ФИО в самой таблице конечно не сказывается ни на размере ни на производительности, но если речь идет о громадных объемах - тут надо оптимизировать

Изображение пользователя st.

Это немного

65 Гб - это не так много, как может показаться. Включив компрессию данных вы получите те же 15-25 Гб без транскодирования, при этом пользователи не будут просить администраторов вводить новые имена собственные. Пропорция между длиной таблицы людей и их имен недостаточна для ощутимого выигрыша, т.к. во всех запросах к ней будет всегда присутствовать минимум 3 соединения.

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

В

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

Изображение пользователя st.

Как понять?

1. Тут чистая "звезда".

2. Запрос
SELECT COUNT(1) FROM peoples WHERE first_name IN ('John', 'Jack', 'Joe')
будет выполняться быстрее чем
SELECT COUNT(1) FROM peoples WHERE id_first_name IN (123, 345, 456)
??

на таких

на таких объемах (65Гиг) не все так очевидно - или лопатить индекс по агромадной таблице или найти значение в маленькой таблице и выполнить join

Изображение пользователя st.

Ну да

Ну да, ну да, JOIN - это вам не "лопатить индекс", JOIN сам делается по волшебству без индексов с hash join clustered scan :)

потестирую -

потестирую - выложу результаты

провел

провел измерения:
- при запросах на точное совпадение - разницы практически нет
- при запросах типа like вариант со справочниками летает, а в случае когда ФИО хранятся в основной таблице - просто беда при холодном старте при перезапуске сервера (без нагрузки) разница = 1мин 08сек против 6сек

да и по объему
- база с ФИО инлайн: данные-14гиг индексы-13гиг
- база со справочниками: данные-8Гиг, индексы - 2Гига

Изображение пользователя st.

Плохо дело

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

Я думаю что

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

Изображение пользователя st.

Во-первых

Во-первых, транскодирование не имеет прямого отношения к нормализации, в обоих случаях (со справочниками и без них) отношение находится минимум в 3 НФ. Во-вторых, был намёк, что тесты нужно делать корректно, но, видимо, намёк был слишком завуалирован :)

Что вас смущает

Что вас смущает в процессе тестирования?
На работе мне приходится работать с решениями которые не подводят меня и в данном случае, когда данные не нормализованы - работать с ними не возможно в в бою - тормоза неприемлемы в системе - я привел пример самого щадящего режима тестирования для не нормализованных данных, когда сервер без нагрузки и имеет наглость забрать всю оперативную память для сканирования всей таблицы! в реальных условиях, когда в памяти активно используются куча индексов и множество запросов - такой роскоши ему никто не даст! Да, с этой стороны тестирование некорректно - но если б я выполнил тестирование в бою - результаты были еще хуже для 1го случая в пользу 2го

Изображение пользователя st.

В процессе испытаний

В процессе испытаний меня смущает сам процесс. Я выше давал ссылку, по которой приведен пример минимума необходимого:

  • конфигурация железа и софта
  • скрипты для создания структур, заполнения данными, выполнения запросов (в режиме очищенного кеша и в нормальном), сбора статистики
  • планы выполнения запросов в графическом или текстовом виде
  • статистика загрузки ЦП, памяти, ввода/вывода

При отсутствии перечисленных компонентов нет темы для обсуждения.

тест был для MS

тест был для MS SQL на 2008R2 с 8гиг ОЗУ
но PostgreSQL показал почти равные результаты, но у нормализованного варианта чуть хуже..

если есть

если есть желание протестировать - могу отправить архив данных и скрипты

Объясняю

1. Persons это не фактовая таблица, а справочник. При соединении с фактами звезда превращается в снежинку.

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

SELECT Family, Name, Surname FROM Persons WHERE Name IN ('John', 'Jack', 'Joe')

Для звезды вот так

SELECT Family, Name, Surname FROM Persons p
JOIN Families f ON p.Family_id=f.id
JOIN Names n ON p.Name_id=n.id
JOIN Surnames s ON p.Surname_id=s.id
WHERE Name IN ('John', 'Jack', 'Joe')

Изображение пользователя st.

Теперь понятно

Теперь понятно, в твоих задачах физлица - это измерения хоз.операций. Я сталкивался с классом задач (страхование, реестр населения) где физ.лица - таблица фактов со множеством измерений, группировок, иерархий + изменения одного и того же физ.лица во времени.

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

Выделять код можно форматированием, тег SELECT FROM...

справочники

справочники пополняются самими прозрачно для пользователя в триггерах

Изображение пользователя st.

Чем дальше в лес...

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

В этой задаче

В этой задаче про нечеткий поиск лучше забыть сразу.
Наталия и Наталья - это 2 разных имени.
Про фамилии лучше даже не думать ... :)

Изображение пользователя st.

Наоборот

Наоборот, без нечеткого поиска база физ.лиц, адресов и т.д. будет кишеть дубликатами. Сравнивать следует по совокупности атрибутов с подобранными весовыми коэффициентами, об этом в статье написано.

Справочник имен собственных, как и любая таксономия, ведется централизованно, служит для подсказки, а не для заполнения пользователями, и покрывает 99% случаев. В 1% пользователь вводит значение в поле таблицы, а не в справочник, далее в системе могут быть предусмотрены регулярные процедуры по выявлению таких случаев с дальнейшей обработкой типа "исправить" или "добавить в таксономию".

Чтобы убедиться в неэффективности заполняемой пользователем таксономии не обязательно экспериментировать на промышленных системах, можно просто взглянуть на облако тегов нашего сайта слева в блоке "Теги" :)

опыт

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