Pull to refresh

Comments 27

Мне проектирование баз данных никогда не казалось сложным. Да, есть тонкости РСУБД и моменты связанные с производительностью, но в 95% случаях все кристально ясно. Может кто-то описать какие-либо сложные случаи? (чтобы понять, может мне всегда простые проекты попадаются).
нашим архитекторам тоже так не казалось. Куча таблиц (100, 200 — хз. очень много), которые благодаря джоинам покрываются локами и вешают базу, малоселективные индексы на битовые поля, вьюхи из вьюх и логика в хранимках — этот ад не справился с нагрузкой после релиза. А проект на 60 человек и большая фин система.
Проективровать базы нужно не на бумаге
Вторая система которую поддерживаю — партнёрка для рекламы. Те же грабли. На локальной машине выборки 500 записей по 20 секунд.
А проектирование как таковое вообще было?
Судя по личным наблюдениям, большинство относится к БД как к черному ящику и вообще не хочет вникать в вопросы реализации.

Насчет сложных мест — советую посмотреть www.slideshare.net/billkarwin/sql-antipatterns-strike-back
Да уж. Представления в базе данных это очевидное зло по двум причинам:
1. Вы не понимаете как работают представление.
2. Другие разработчики не понимают как работают представления.
спроектируйте структуру данных для хранения номеров телефонов, как мобильных так и домашних, имеющих возможность хранить внутри себя любой телефонный номер мира (любая страна и прочее), а также подумайте о возможных изменениях кода стран, городов, мобильных операторов, и сделайте вашу структуру таким образом, что бы при таком изменении, сам идентификатор записи телефонного номера не изменился, и что бы можно было посмотреть историю этого телефона во времени…

когда справитесь могу подкинуть вам задачку про адрес…
Вначале поясните немного схему: какие сущности хранят в себе таблицы locality и locality_code — это что город/название мобильного оператора? Поясните пожалуйста.

Далее объясните пожалуйста с какой целью появилась таблица region? Насколько я понял она не хранит в себе ничего кроме имени и ссылки на страну. Или вы попытались объединить задачу с телефоном и адресом? Поясните пожалуйста.

Также, ваша реализация хранения истории — перечёркивает всю красоту точечных изменений телефонных кодов: к примеру у страны поменялся телефонный код в таблице phone — всё хорошо, никаких апдейтов делать не нужно данные будут корректны, но для каждой записи из этой таблицы, которая ссылается на страну, с изменившимся телефонным кодом необходимо будет создавать новую запись в таблице phone_history — что не есть хорошо. Даже если код страны изменится 1 раз — но страна будет к примеру Россия или США — то прикиньте сколько новых записей вам придётся создать для отслеживания этой истории?

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

region введён для понимания о каком населённом пункте идет речь. К примеру, населённый пункт Александровка присутствует на карте России 166 раз: ru.wikipedia.org/wiki/%D0%90%D0%BB%D0%B5%D0%BA%D1%81%D0%B0%D0%BD%D0%B4%D1%80%D0%BE%D0%B2%D0%BA%D0%B0

Остальные сущности, надеюсь, понятны.

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

Задача подсистемы телефонных номеров, на уровне БД, заключается в двух вещах:
1) быстро выдавать готовый телефонный номер по idPhone (полностью или частично, например без кода страны, или без кода города);
2) минимизировать количество записей при изменении (не потеряв важных данных)

Country — безусловно нужная таблица — потому что телефонный код страны — обязательная составная часть любого полного номера телефона.

locality — нормальное название, особенно если под ним мы можем понимать также и мобильного оператора к примеру Билайн или МТС — и совершенно верно что у одного оператора(в одном городе) может быть несколько телефонных кодов, однако надо помнить что один оператор может быть представлен в нескольких странах — но эти вопросы мы сознательно упускаем, и понимаем что в БД оператор Билайн — встретится и в Украине и в России и в других странах (будет хранится в нескольких записях табилицы locality) — если нам потом потребуется отдельно это анализировать и использовать — то ни что не помешает создать таблицу «Оператор связи» — и выстроить связь между этой таблицей и таблицей locality.

Насчёт того что поле code в locality_code имееют длинну 4 символа — это вы явно поспешили, во многих мелких населённых пунктах Украины (и наверное России) до сих пор есть 5ти и 4х значные телефонные номера — то есть коды городов у них будут 5-ти и 6-ти значные.

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

В остальном всё верно. Получается из вашей схемы необходимо выкинуть таблицу region и phone_history и изменить длинну locality_code — если вам интересно можем дальше обсудить возможность и целесообразность реализации хранения истории изменений.
Я не совсем с Вами согласен по поводу region.

Данная база имеет информативность стремящуюся к нулю. Из неё мы можем узнать только информацию о смене кодов для телефона. По хорошему из неё было бы не плохо получить информацию о владельце номера. Я не говорю сейчас про адрес. В этом случае без таблицы region есть шансы получить номера для 166-ти жителей Александровки (при наличии в каждом населённом пункте однофамильцев). И это только для России.

Таблица locality не должна содержать информацию о мобильных операторах. Только населённые пункты. Для мобильных операторов необходимо создать дополнительную таблицу. И связать её с locality_code. На данный момент все коды мобильных операторов, как и городские коды, хранятся в locality_code.

По поводу длинны кода для населённого кода согласен. Самый длинный телефонный код (как минимум для России) принадлежит селу Средние Пахачи (Камчатка). Состоит из 8-ми символов: 41544513.

Немного доделал схему: content.screencast.com/users/nigasam/folders/Jing/media/a0148b59-5da8-43be-9a09-2614bcd10341/00000004.png

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

Давайте продолжим. Эта тема интересна мне.
Вы включаете в схему таблицы адреса потому что не хотите для адреса отдельно разрабатывать схему? Вы хотите соединить две подсистемы в одной?
Я с радостью сделаю схему и для адресов. Отдельно. Я хочу сделать подсистему отдельно, но правильно. С возможностью дальнейшего роста.
а зачем тогда в эту подсистему вы включаете элементы адреса, избыточные? вдень когда я хочу позвонить кому-то мне в принципе всё-равно в какую Александровку я звоню, главное понимать какая это страна (сколько мне это будет стоить денег) и какой у Александровки телефонный код и номер абонента.

Другое дело что внутрисетевой роуминг внутри страны, и разница в тарификации при звонке в другой регион — действительно имеет место в России, если вы для этого вводили таблицу region — тогда это ещё можно понять. Но с другой стороны — тогда эту задачу вы не решите для мобильных телефонов в той схеме (вариант 2) который вы привели.

Вы со мной в принципе не согласны, что разумнее вначале выстроить отдельно таблицы для «чистой» подсистемы телефонных номеров, а затем для адреса и только потом выстроить связи позволяющие решать в том числе и задачи выявления однофамильцев в городах (здесь потребуется ещё одна подсистема — Люди)?
Важно понимать что это ещё не база — это только кусок схемы относящейся к телефонным номерам, и адрес также должен быть и таблицы для него также должны быть, но если вам кажется что в вашей схеме уже есть и адрес и телефоны — то можем подискутировать на эту тему (для адреса этих таблиц недостаточно), я же хочу разбить схему и задачу на две части а потом выстроить связи между адресом и телефонами
Мне не кажется, что в схеме есть адрес. В схеме есть привязка телефона к населённому пункту. Населённый пункт помимо привязки к стране имеет привязку к региону/краю/району/etc, которые можно объединить в одну таблицу.

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

как видим он состоит из трёх частей код страны, код оператора/города/… и собственно номера.

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

Если решать задачу сразу в лоб — то первым решением будет создать три таблицы и так их и назвать «код страны», «код оператора или города» «телефонный номер». Решение не такое плохое как может показаться на первый взгляд. Но попробуем сделать ещё несколько предположений.

Предположение первое — у каждой страны в один момент времени есть только один телефонный код страны: — если верить этой статье ru.wikipedia.org/wiki/%D0%A1%D0%BF%D0%B8%D1%81%D0%BE%D0%BA_%D1%82%D0%B5%D0%BB%D0%B5%D1%84%D0%BE%D0%BD%D0%BD%D1%8B%D1%85_%D0%BA%D0%BE%D0%B4%D0%BE%D0%B2_%D1%81%D1%82%D1%80%D0%B0%D0%BD то это действительно так.

Что нам может дать это утверждение? Оно может дать нам ровно следующее — нам не требуется создавать отдельную сущность «код страны» — и в будущем связывать её с сущностью «страна» — потому что связь между страной и кодом страны 1 к 1 — значит мы можем просто создать таблицу Страна и добавить в неё одно дополнительное поле — код страны — таблица country — полностью этому соответствует.

С первой таблицей определились. Теперь забежим вперёд и сразу подумаем насчёт истории изменений этой таблицы. Если мы хотим иметь возможность отслеживать изменения, и при необходимости вытаскивать телефонный код страны на момент какой-то даты определённым специфическим запросом — то где-то нам надо хранить эти изменения. У вас это country_code_history. Мне кажется что это не самое лучшее название, раз уж основная таблица называется country — то и историю лучше хранить в таблице country_history — потому что так нам одной таблицы хватить на хранение любых изменений одной сущности (к примеру смену названий государства также можно будет отследить по этой таблице). Какие поля должны быть в таблице истории кроме, естественно country_id? Если мы предполагаем отслеживать все изменения, то можно хранить все поля из той таблицы в нашем случае добавить также название страны. Что ещё требуется? Ещё потребуется всего два параметра дата старта и дата финиша (то есть время «с» «по» когда этот экземпляр истории был активной текущей записью страны с определённым айдишником). Хранить old_code и new_code — мне кажется не самым лучшим решением, потому что мне даже не понятно какой код был у страны на определённую дату взглянув на вашу схему? Возможно вы закладывались на что-то иное — и я просто неправильно вас понял в таком случае поясните.

Также сразу оговорим какие изменения наша схема не отследит стандартным способом а именно это распады и соединения стран. То есть варианты с СССР(была одна страна стало много) ФРГ и ГДР (две страны слились в одну) и их телефонными кодами стран, красиво в эту схему не лягут (придётся докручивать напильником и прочее), но мы согласны на такое несовершенство, и готовы в будущем доработать требуемые структуры и механизмы при наличии таких требований.

Также важно сразу обговорить, каким образом будут происходить записи в таблицу изменений country_history — (хоть написание методов CRUD и не входит в задачу разработки схемы, но на кое-какие моменты необходимо обратить внимание) а именно — при любом апдейте записи в таблице country — старое значение должно попадать в таблицу country_history (естественно я тут не рассматриваю пустые или ошибочные изменения — отслеживание этого — отдельный вопрос, ещё мы понимаем что в нашей системе только у очень ограниченного круга лиц будут права изменить название или код страны — это не частая операция).

Если вам мои рассуждения по поводу первых двух таблиц понятны, и возражений нет и вам интересно продолжить я продолжу рассуждать далее, если я где-то ошибся или неправильно вас понял или вы со мной не согласны, напишите пожалуйста.
По поводу истории страны согласен. Изначально считал, что там будет храниться только код. Без названия страны. old_code и new_code — коды до и после изменения. Согласен, что это не очень правильно. Ваш вариант правильней.

Каким образом можно отследить распады и соединения стран? У меня только одна идея: путём создания/удаления и изменения записей.

Давайте продолжим. Мне достаточно интересно.
Хорошо, продолжаем, итак у нас есть две первые таблицы country и country_history — в таблице country будет содержаться первая часть телефонного номера: код страны.

Теперь добавим ещё одну таблицу и назовём её secondary_telephone_code — эта таблица будет содержать вторую часть нашего телефонного номера, а сам значимый текстовый код может быть как кодом населённого пункта так и кодом мобильного оператора. В этой таблице связка полей country_id и code будет уникальной (я сейчас не говорю о том как прописывать первичные ключи — а просто указываю на логику таблицы). То есть по сути ваша таблица locality_code превращается в secondary_telephone_code только она теперь напрямую связана с country. Также в таблицу secondary_telephone_code помимо полей country_id и code — можно добавить поле type или typeId — которое будет указывать на то чем именно является этот код — кодом города? мобильного оператора? или ещё чем-нибудь? а также необязательное поле text — в котором можно хранить любое примечание к коду (название города мобильного оператора или что-то ещё).

Обращаю ваше внимание что type и text не являются обязательным полями (как минимум для начала функционирования системы телефонных номеров).

Также, на данном этапе, пока у нас ещё нет адреса — нам не надо выстраивать связи между таблицей secondary_telephone_code и например таблицами region или другими. То есть выстроить связи в будущем мы сможем — но наши основные связи не поменяются и для логики хранения и изменения телефонных номеров такая сущность как регион не играет никакой роли (не путаем с логикой выбора или вноса информации пользователем/оператором). Название таблицы secondary_telephone_code — может показаться не самым благозвучным — но оно действительно удачное — потому что эта таблица содержит в себе именно то что означает её название вторую часть телефонного кода (можно добавить в название part и превратить таблицу в secondary_part_telephone_code — а можно этого и не делать). То что эта вторая часть может быть кодом города и выбираться из списка после выбора страны и региона, либо кодом оператора — не имеет никакого значения на этапе проектирования структуры телефонного номера.

По аналогии с country_history — мы создаём таблицу secondary_telephone_code_history

Итак у нас есть 4 таблицы — две основные и две для хранения истории изменений основных таблиц.

Добавляем третью основную таблицу phone — как у вас на схеме — и связываем её с таблицей secondary_telephone_code — всё структура данных системы телефонных номеров готова. В ней можно хранить реальные номера телефонов и отслеживать изменения, например если данные поступают из внешних источников. Конечно же, для того, что бы привязать эту структуру к интерфейсу вноса и редактирования информации — её необходимо доработать, и связать с другими подсистемами (адресом, операторами связи и прочими) — но с нашей задачей, хранить в себе любые телефонные номера мира и отслеживать изменения кодов стран городов и мобильных операторов — данная структура справляется. и не содержит ничего лишнего.

Теперь предлагаю вам поставить следующую задачу, исходя из того что у нас уже есть, если вы согласны с тем что мы получили и для чего мы это получили, и того чего вы хотите от системы на следующем шаге. Чем меньше будет шаг тем лучше.
Также можете выложить текущую схему таблиц что бы мы понимали что находимся в одной точке.
было бы неплохо, если бы вы добавили еще как проектировать циркулярные зависимости. То есть когда нужна ссылка в двух таблицах друг на друга. Вроде как что я слышал — это плохо. Но вот почему — так и не понял (ну кроме время от времени возникающих deadlock'ов в базе данных)
Автором являюсь не я. У автора статей, к сожалению, нет ничего на указанную тему. Могу лишь предложить переведенные статьи с зарубежных ресурсов, что-то свое писать, снова к сожалению, не имею времени. На зарубежных ресурсах много качественной и полезной информации имеется. В некоторых случаях больше, чем на отечественных.
Не понимаю ценности данной работы, есть множество учебников, где всё подробно разобрано.
Чесно говоря, я, не читав ничего по РСУБД, взялся проэктировать сложные БД и пришел к тому, что описано в данной статье. Не потому, что такой умный, а потому что это логично. Хотел подчерпнуть со статьи что-то новое, но ничего нового не подчерпнул, лишь получил уверенность в том, что делаю. Спасибо за перевод!
Но обычно наличие двух таблиц в связи один-к-одному считается дурной практикой.

Есть таблица с дополнительными полями профиля пользователя. Эти поля используются только в личном кабинете, да и заполнены хорошо если у 3%. Смысл их хранить в основной таблице пользователя, если большая часть это null-данные?
Для сферической базы данных нет разницы, заполнены все поля, насколько их много, время поиска не важно. А разбиение на две таблицы — это элемент оптимизации под конкретную РСУБД.
Sign up to leave a comment.

Articles