Нормализация и исторические данные

Прежде чем я опишу свою проблему, я хотел бы получить пару вещей в сторону:

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

Допустим, мы получаем электронную таблицу Excel один раз в месяц из банка, но не всегда в одном и том же банке. Электронная таблица содержит всего шесть столбцов: название банка, номер счета, остаток на счете, имя клиента (владельца счета), номер SSN клиента и адрес владельца счета. У каждой строки есть свой номер счета, и номер счета не указан более чем в одной строке. Мы хотим импортировать эту электронную таблицу в базу данных и в любой момент в будущем сказать: "Каков был адрес Джона Смита 13 октября 2010 года?"

Для простоты предположим, что у каждого клиента только один адрес и что у каждого клиента может быть ноль или более учетных записей. И на секунду давайте представим, что нам нужно сделать только один лист Excel для импорта КОГДА-ЛИБО, что является глупой предпосылкой, но терпите меня. Если это так, то будет достаточно следующего дизайна:

bank
--------
id
name

account
--------
id
bank_id
customer_id
number
balance

customer
--------
id
name
ssn
address
city
state_id
zip

state
--------
id
name

Остальная часть моего вопроса основана на предпосылке, что вы согласны с тем, что эта схема является "правильной", так что, надеюсь, у вас все в порядке.

Теперь это было бы хорошо, если бы мы только когда-либо делали один импорт, но мы будем делать 12 импортов на банк в год. Вот как я думал об учете этого:

bank
--------
id
name

account
--------
id
import_id
bank_id
customer_id
number
balance

customer
--------
id
name
ssn
address
city
state_id
zip

state
--------
id
name

import
--------
id
date
excel_file (blob)

Теперь каждая учетная запись привязана к импорту, и мы можем с уверенностью сказать, что "Учетная запись 12345 пришла из импорта 572 13.10.10". Это становится потенциально немного более двусмысленным, когда вы смотрите, скажем, на customer Таблица. Так как в customer таблица, чем в account таблица (потому что у некоторых клиентов есть несколько учетных записей), у нас нет такого отношения один к одному между клиентами и импортом, как у нас для счетов и импорта. Я знаю, что нет потери данных и нет потери целостности данных, но это все равно похоже на какую-то жертву.

Мой вопрос (и это может быть слишком открытым): как вы думаете, это хороший способ хранения данных? Вы сделали бы это по-другому?

Изменить: есть важный способ думать об этих сущностях, которые вы должны знать. Не думай о account как одна учетная запись, которая существует с течением времени. Думать о account как снимок учетной записи в определенный момент времени. Таким образом, счет 12345 с балансом $100 НЕ является тем же account как счет 12345 с балансом 150 $. Да, обе записи в реальном мире привязаны к одному и тому же банковскому счету, но то, что я храню, - это снимок учетной записи в определенный момент времени. Схожая (но не идентичная) ситуация с клиентами.

Источник

6 ответов

Извините, я не могу согласовать утверждения "у каждого клиента только один адрес" и "мы хотим сказать," каким был адрес Джона Смита 13 октября 2010 года "". Предполагаете ли вы, что при каждом импорте вы будете создавать новую запись о клиенте для каждого человека, найденного в импорте? Если так, как вы узнаете, что Джон Смит в одном импорте - это тот же Джон Смит из другого импорта, если номера счетов отличаются?

И если вы повторно используете одну и ту же запись о клиенте для одного и того же клиента (что мне кажется правильным), где вы найдете информацию об адресе?

[После комментариев и поправок автора]

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

Хранение адреса в AccountImports немного ненормально, если адрес часто остается неизменным от импорта к импорту. Если это так, вы можете добавить таблицу CustomerAddressHistory. Во время каждого импорта проверяйте последний адрес для SSN в CustomerAddressHistory и, если не совпадает с импортом, добавляйте новый адрес в новую запись в этой таблице.

Источник

Понятия не имею, какую БД вы используете, но здесь идет речь: я бы НЕ сохранил импорт как blobпоскольку это препятствует вашей способности связываться с существующими данными, потому что вы должны обрабатывать blob как тип файла, который вы ожидаете, прежде чем вы сможете присоединить его к любым другим вашим данным. Импортируйте данные непосредственно в свою таблицу импорта вместе с уже имеющимися у вас идентификатором и полем даты. Положить key на id, то unique compound index на дату, банк и счет, чтобы предотвратить однотипные дублирования.

Если вы точно знаете, что у вас будет только 12 импортов в год (месяцы, я полагаю?), Вы можете повысить целостность, создав два вычисляемых поля: одно для date_month (для хранения JUST месяца) и одно для date_year (для хранения ТОЛЬКО год), а затем создайте unique compound index на банковский идентификатор, счет, дата_месяц и дата_год. Это предотвратит случайный повторный импорт данных за тот же месяц в разные даты, например, если импорт за октябрь был выполнен в понедельник, то кто-то сделал это снова в вторник. Это также предотвратит сценарии "К сожалению, я снова нажал кнопку" или "К сожалению, я импортировал данные этого месяца в качестве сценариев прошлого месяца". Чтобы ускорить проверки вычисляемых полей, поместите уникальные индексы в date_month и date_year.

Если вы хотите, чтобы ваша таблица клиентов всегда отображала текущий адрес без суеты, сделайте адрес вычисляемым полем, которое просматривает вашу таблицу импорта по счету клиента (или SSN, и т. Д.) И выбирает TOP 1 адрес отсортирован по дате DESC, Если вы хотите, чтобы запросы в адресном поле или включались в него были быстрее, поместите в него индекс.

Источник
  1. Поскольку каждый импорт привязан к определенному банку, я мог бы рассмотреть вопрос о добавлении bank_id в таблицу импорта и исключении его из таблицы счетов.
  2. Если вы хотите учесть исторические адресные данные и получаете эти данные исключительно из своих импортов, вы можете добавить поля адресов в таблицу счетов и удалить их из таблицы клиентов. Конечно, это может привести к дублированию, если у вас один и тот же адрес для нескольких импортов. Если вас это сильно волнует, вы можете добавить еще одну таблицу, возможно, "адрес", скорее всего, с помощью составного первичного ключа customer_id и address_id. Затем ваша таблица импорта добавляет поле address_id, и ваш код импорта должен проверить, существует ли уже адрес.
Источник

В целом, дизайн выглядит хорошо для меня.

Имеет ли сам import/import_id какое-либо значение помимо сохранения даты? Если нет, я не вижу причин, по которым вам не следует полностью исключать таблицу и помещать дату импорта import_date в таблицу учетных записей.

Кроме того, если вам нужна историческая информация об адресе, вам понадобится также import_id (или import_date:)) в таблице customer.

Обновить

Как отмечено в комментарии, добавление import_id не будет учитывать исторические адресные данные.

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

customer
------
id
first_name
last_name (assuming name wouldn't change--it certainly could)


customer_history
-----------------
id
customer_id
import_id (or date)
(address fields)

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

Источник

Я хотел бы создать новую таблицу под названием CustomerAddress и переместить информацию об адресе из клиента в эту новую таблицу

Затем в таблицу Account и таблицу CustomerAddress добавьте 2 новых столбцаStartDate и EndDate.

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

Источник

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

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

Источник
Другие вопросы по тегам