Покритикуйте плиз архитектуру БД

schipuchka1

сабж, система сбора онлайн отчётности. Собираются с разной периодичностью формы, которые состоят из некоторого количества таблиц, в которых есть ячейки различного типа - дробного, целого и строковый (нельзя менять) (возможно ещё формулы по таблице будут)
Таблицы Form, Table, Cell - описывают собственно структуру форм
Таблицы DataForm, DataTable, DataCell - относятся к конкретной сдаваемой форме
Таблицы FormRight, TableRight, CellRight - права: тока чтение; чтение и запись; чтение, запись и блокировка от изменений (ака замочек

) - не путать с блокировками в БД

Dasar

для анализа необходимы по каждой таблице:
1. кол-во хранимых данных
2. скорость поступления новых данных
также необходим список запросов с оценкой:
1. какие таблицы будет затрагивать
2. как часто будет дергаться
3. какой объем данных будет выбирать

schipuchka1

1. есть 40 форм, в каждой в среднем по 25 таблиц, в таблице в среднем 20 * 10 ячеек.
т.о. предполагаемая начальная мощность Cell - 200 000 элементов.
учреждений, которые сдают эти формы (годовые) 120, но большинство сдаёт тока несколько форм. Предполагаемая нагрузка от этих форм - 120 * 200 000 / 3 = 8 000 000
самый сложный запрос будет выглядеть что-то наподобии:

SELECT dc.value, c.x, c.y, dc.block FROM DataCell dc, DataTable dt, DataForm df, Cell c WHERE dc.ID_cell = c.ID AND dc.ID_dataTable = dt.ID AND dt.ID_dataForm = df.ID AND df.ID_form = 0 AND dt.ID_table = 0 AND df.`date` = "29-12-2012" AND df.ID_org = 0;

schipuchka1

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

Dasar

форма - это что-то единое, атомарное?
или возможны частые запросы, которые выбирают данные из разных форм, выбирают часть формы?

schipuchka1

форма (Form) - это нечто утверждаемое раз и на долго. Если один раз данные по форме заполнили, то она уже не меняется, а при необходимости создаётся новая.
Данные из разных форм (DataForm) выбираться могут, но выбираются в основном по ячейкам.

mbolik1

1. Ты бы проверил как оно у тебя будет работать: залей свои 8 млн. записей и запусти запросы, что-то мне кажется помрёт оно.
2. Если у тебя планируется держать данные разного типа заведи себе две колонки value_str и value_num, большинство СУБД не тратятся на хранение пустых значений, а потери от того что ты будешь хранить число как стоку огромные.
3. Планируется ли у тебя хранение старых форм (скажем за последние 5 лет) и появление форм с периодичностью сдачи чаще чем раз в год (скажем раз в месяц т.к. это резко увеличивает объёмы хранимых тобой данных.
4. Не всякая СУБД догадается сначала выбрать все записи из DataCell и из Cell а потом их соединить, скорее даже сначала выберет все данные из Cell и лишь потом будет искать нужные данные из DataCell (а это безумно дорого даже с подходящими индексами). Так что может задуматься о том чтобы заранее задублировать данные из Cell в DataCell, пусть денормализация, зато лишнее соединение убираем.
5. Кстати что за СУБД?

Maurog

SELECT dc.value, c.x, c.y, dc.block FROM DataCell dc, DataTable dt, DataForm df, Cell c

выборка по 4 таблицам может тормозить.
MSSQL2005, к примеру, готов максимум на 3 джойна (дальше дохнуть будет с экспоненциальной скоростью)
лучше сразу прототип наложить на твою схему, если будет тормозить, то можно денормализовать, ато у тебя в схеме очень много внешних ключей, как следствие, будет много джойнов. возможно, самый сложный запрос ты тоже не угадал, и базу будут дрючить по самые не балуй :grin:

Dasar

форма (Form) - это нечто утверждаемое раз и на долго. Если один раз данные по форме заполнили, то она уже не меняется, а при необходимости создаётся новая.

тогда может быть лучше хранить форму как единое целое, в виде того же xml-я.
в mssql и oracle - xml можно делать типизированным (со схемой в этом случае, еще и запросы поверх xml-я будут достаточно быстрыми

schipuchka1

1;5 - ещё нет СУБД, только архитектура
2 - нет, значения только числовые
3 - да, да, старые формы будут реплицироваться в другую таблицу
4 - ячеек в Cell будет значительно меньше, чем в DateCell

schipuchka1

Тогда мб имеет смысл разбить запрос?

SELECT dt.ID FROM DataTable dt, DataForm df WHERE dt.ID_dataForm = df.ID AND df.ID_form = 0 AND dt.ID_table = 0 AND df.`date` = "29-12-2012" AND  df.ID_org = 0;

SELECT dc.value, c.x, c.y, dc.block FROM DataCell dc, Cell c WHERE dc.ID_cell = c.ID AND dc.ID_dataTable = x;

mbolik1

1;5 - ещё нет СУБД, только архитектура

На держи:

SELECT /*+USE_NL(dc c)*/ DC.VALUE, C.X, C.Y, DC.BLOCK FROM TEST_DATACELL DC, TEST_CELL C WHERE DC.ID_CELL = C.ID AND DC.ID_DATATABLE = (SELECT CEIL(DBMS_RANDOM.VALUE*100) FROM DUAL);

--0.422

--0.734

SELECT /*+USE_NL(dc c)*/ DC.VALUE, C.X, C.Y, DC.BLOCK FROM TEST_DATACELL DC, TEST_CELL C WHERE DC.ID_CELL = C.ID AND DC.ID_DATATABLE = 45;

--0.609

--1.156

SELECT DC.VALUE, C.X, C.Y, DC.BLOCK FROM TEST_DATACELL DC, TEST_CELL C WHERE DC.ID_CELL = C.ID AND DC.ID_DATATABLE = (SELECT CEIL(DBMS_RANDOM.VALUE*100) FROM DUAL);

--0.234

--0.266

SELECT DC.VALUE, C.X, C.Y, DC.BLOCK FROM TEST_DATACELL DC, TEST_CELL C WHERE DC.ID_CELL = C.ID AND DC.ID_DATATABLE = 45;

--0.234

--0.235

Всё гонялось на хорошем серваке с кучей памяти.
Первая цифра время работы на 8 млн., вторая на 16. млн. (количество форм выросло в двое)
Первые два запроса эмулируют СУБД у которой единственный механизм соединения таблиц — Nested Loop.
Итого: у тебя 25 таблиц на одну форму значит минимальное время доступа: 25*0.23=6 секунд, что хорошо, правда если СУБД не очень, то это уже 25*0.6=15 секунд, что уже хуже. С учётом обработки на стороне сервера приложений (сформировать запрос, отправить запрос в БД, получить данные и опять по кругу) я бы рассчитывал на время формирования формы — 1 мин.
Если у тебя и правда нет строковых данных то вся БД получается объёмом меньше 1Гб и почти любой дизайн подойдёт, на твоём месте я бы начинал не с дизайна БД, а с дизайна приложения, а БД подгонял бы под то как удобней приложению, т.к. объёмы данных смешные.

schipuchka1

большое спасибо. Дизаин приложения тоже составляется, но в нём проблем нет (собственно дизаин базы под него уже заточен)

Оставить комментарий