Хранение периодов дат в базе данных

Question

Я хотел бы обсудить "лучший" способ хранения периодов дат в базе данных. Давайте поговорим о SQL/MySQL, но этот вопрос может быть для любой базы данных. У меня ощущение, что я делаю что-то не так годами...

На английском у меня есть информация:

-In year 2014, value is 1000
-In year 2015, value is 2000
-In year 2016, there is no value
-In year 2017 (and go on), value is 3000

Кто-то может хранить как:

BeginDate   EndDate     Value
2014-01-01  2014-12-31  1000
2015-01-01  2015-12-31  2000
2017-01-01  NULL        3000

Другие могут хранить как:

Date        Value
2014-01-01  1000
2015-01-01  2000
2016-01-01  NULL
2017-01-01  3000

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

Что предпочитают мои коллеги? Любое другое предложение?

РЕДАКТИРОВАТЬ: я использовал полный год только, например, мои данные обычно меняются с детализацией дня.

РЕДАКТИРОВАТЬ 2: Я думал об использовании сохраненной "Дата" в качестве "BeginDate", упорядочить строки по дате, а затем выбрать "EndDate" в следующей (или предыдущей) строке. Сохранение "BeginDate" и "Interval" может привести к проблеме дыр / перекрытий в качестве первого метода, которого мне необходимо придерживаться сложного правила проверки.

Answer 1

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

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

Например, в вашей текущей схеме:

select *
from other_table ot
inner join year_table yt on ot.transaction_date between yt.year_start and yt.year_end

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

Во втором предложении схемы это не так просто:

select *
from other_table ot
inner join year_table yt 
 on ot.transaction_date between yt.year_start 
 and yt.year_start + INTERVAL 1 YEAR

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

Вы также можете сохранить год как целое число (как рекомендуют некоторые комментаторы).

select *
from other_table ot
inner join year_table yt on year(ot.transaction_date) = yt.year

Опять же - это может оказать влияние на производительность, так как каждое сравнение требует выполнения функции.

Пурист во мне не любит хранить это как целое число - так что вы также можете использовать тип данных MySQL YEAR.

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