Правильная организация хранения исторических данных для существующей БД

Приветствую всех!
Передо мной стоит задача организовать сохранение исторических данных для существующей БД. Организация занимается арендой недвижимости.
Есть обычная БД(3НФ) которая содержит список текущих предожений.
Вся информация об объектах аренды содержится в основной таблице [Предложения], в ней есть поле Дом(FK), информация о его номере, местоположении разнесена на таблицы [Дома], [Улицы], [Районы]. Таким образом информация об адресе предложения получается запросом с JOINами из этих таблиц.

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

Комбинация может быть любая, и запрос должен иметь приемлемый таймаут.
Наверняка есть какие-то паттерны реализации подобного функционала.
Как я понял из теории, получается что сейчас есть только БД, а нужно смотреть в сторону понятий OLAP Data Warehouse или Data Mart. Или это это можно реализовать в рамках классической реляционной БД? Какие есть варианты? Спасибо!

0

Сообщение от ValeOFY Как я понял из теории, получается что сейчас есть только БД, а нужно смотреть в сторону понятий OLAP Data Warehouse или Data Mart. Или это это можно реализовать в рамках классической реляционной БД? Какие есть варианты? Спасибо!

Чот загнули вы. Всё это решается одним, максимум 2мя запросами к БД. А там уже как вы отображать будете полученную информацию - другой вопрос. Обычно для этого используют графики. Готовых расширений на JS по этой теме много. Проблема тривиальная. Что вам нужно конкретно - мне не ясно.

0

Дело в том, что в своем нынешнем виде таблица [Предложения] содержит только объекты, которые сдаются в данный момент. Если объект снимается - информация удаляется из таблицы и нигде не сохраняется. Соответственно нет исторических данных для вывода на графики.

0

ValeOFY, здесь уже проблема в том, что БД была спроектирована неверно. Незачем делать костыли, лучше сделать сразу как надо. Если данные нужны для статистики, то добавьте поле trash с типом enum('0', '1') и не удаляйте запись, а имитируйте это установив значение trash равное '1'.

0

Можно просто завести 2 колонки: дата начала и дата конца.

0

Сообщение от Grossmeister Можно просто завести 2 колонки: дата начала и дата конца.

Оно тут вообще причём?

0

Сообщение от BuPy7 Оно тут вообще причём?

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

0

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

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

0

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

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

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

1

Сообщение от Grossmeister А любой анализ без привязки ко времени бессмысленен.

Да он им может быть и не нужен. Им важен факт за всё время работы. Автор ведь не упомянул ничего о датах.

И да, можно триггерами. Никто не спорит.

0

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

Интересное решение, буду рассматривать. Но боюсь настанет момент, когда количество записей в таблице станет невероятно большим и таймаут запроса к ней (+JOINы других таблиц с адресом) станет неприемлемым. Т.е. я же не могу бесконечно в одну таблицу данные закидывать, надо их как-то обрабатывать, обобщать и делать какую-то выборку по времени что-ли.

Сообщение от BuPy7 Да он им может быть и не нужен. Им важен факт за всё время работы. Автор ведь не упомянул ничего о датах.

Мы друг друга не поняли. Я имел ввиду что на графики надо выводить динамику изменения количества предложений/рыночную стоимость объектов с течением времени. + можно конкретизировать параметры интересующих объектов(например по району или количеству комнат).

0

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

Обязательно надо будет сделать индекс по датам (хотя бы по дате начала).
А далее возможны варианты.
Некоторые СУБД (например, Oracle) позволяют производить секционирование (partitioning) таблиц. Когда одну таблицу можно разбить, например, по временным интервалам и сама СУБД будет смотреть только те секции, которые относятся в данному запросу.
Если такое применяемая СУБД не позволяет, то можно самим создавать таблицы с данными, допустим, за год. Только тогда надо при написании запроса самому учитывать, к каким таблицам обращаться.
Либо, если, допустим, мы часто обращаемся к данным за последний год, а к остальным редко, то редко запрашиваемые данные можно переносить в архивную таблицу.

PS
В учетных системах вообще не принято удалять данные (м.б. за исключением неверно введенных). И отметка актуальности делается как раз за счет колонок с датами.

1

Grossmeister, благодарю за развернуты ответ!

Добавлено через 4 часа 44 минуты
Grossmeister, Хочу уточнить пару моментов.

Объемы информации большие.
В базе в данный момент времени находится 300К объявлений.
Ежедневно она обновляется, возьмем 5% = 15К объявлений добавляется, столько же удаляется.
Таким образом за год будет ~ 5.5КК записей в таблице [Предложения].
Таблица [Дома] содержит 140К записей.

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

Неужели можно так просто все реализовать? Просто, ничего не удаляя.. Было бы замечательно!
Такой подход давал бы большую гибкость при анализе данных, ведь вся иформация останется в базе. Т.е. можно получить график изменения стоимости квартир, скажем, на 3-м этаже в панельных 5-тиэтажных домах площадью до 40 м.кв. на улице Ленина и.т.д.(конечно, это излишне специфический запрос, для примера).

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

И второе. Знакомы ли вы с технологией SQL Azure Federations?
Если да, то подойдет ли эта технология для разделения этой огромной неочищаемой таблицы [Предложения], например по годам на разные федерации(как аналог partitioning)?

0

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

Всего 1 запрос: условие по дате + по квартире, группировка по году + месяцу

Добавлено через 18 минут

Сообщение от Grossmeister Можно просто завести 2 колонки: дата начала и дата конца.

Правильное предложение. Дата начала - дата предложения, Дата конца - дата снятия предложения. Я бы еще добавил поле "Причина снятия предложения". Логично было бы видеть почему снято предложение, например: арендована, предложение отменено арендодателем, изменение цены арендодателем, ... ну и еще какие-нибудь

1