У меня вопрос по проектированию модели данных для складов. Общие требования к разрабатываемой системе такие:
1. Есть несколько складов. На складе располагаются товары.
2. Есть операции приемки товара на склад, перемещения на другой склад и списания со склада.
3. Необходима возможность просмотреть прошлое состояние склада (ограничение, например, за год)
4. Необходимо хранить данные по прошедшим операциям.
5. Необходима возможность отмены и изменения прошедшей операции приемки/перемещения/списания.
6. Прошедшие операции следует хранить не более 1 года.
Какой подход избрать для проектирования модели данных?
У меня в голове сейчас 3 варианта.
1 вариант. Не хранить, сколько товара было на складе в каждый конкретный момент. Хранить только операции, например:
Товар Склад Кол-во Дата Тип операции
----------------------------------------------------
Телевизор С1 +8 март Приход
Телевизор С1 -1 апрель Расход
Телевизор С2 +1 апрель Приход
Тогда мы можем агрегировать эти операции по складу и товару и получить количество товара на заданную дату. Легко отменить операцию — просто не учитывать ее при агрегировании. Однако старые операции должны храниться только 1 год, а затем удаляться, значит мы потеряем данные. Кроме того, чтобы получить состояние складов на сегодня, придется выгружать всю таблицу операций в память, чтобы провести агрегирование — теряем в производительности.
2 вариант. Хранить и операции, и состояние товара на складе. Причем состояние товара — это версионные данные. Например:
Товар Склад Кол-во Дата
-----------------------------------
Телевизор С1 8 март
Телевизор С1 7 апрель
Телевизор С2 1 апрель
Теперь мы можем удалять старые операции и даже старые записи из таблицы состояние складов. Однако размер БД увеличивается и появляются сложности с отменой прошлых операции. Чтобы отменить операцию, нужно провести цепочку изменений в таблице состояния склада — ведь изменятся все последующие записи, связанные с данным товаром.
Вероятно, имеет право на жизнь 3 вариант, который является комбинацией 1 и 2 вариантов. В нем мы заводим табличку "базового" состояния складов, которое было до совершения первой неудаленной операции. Тогда, при удалении устаревших операций, мы применяем их к "базовому" состоянию складов. А состояние складов за определенную дату получается путем объединения "базового" состояния и операций до этой даты.
А какое Ваше мнение, господа?
Заранее всем спасибо.
Re: Задачка: модель данных для склада с версионностью
Здравствуйте, ronaldo9, Вы писали:
R>6. Прошедшие операции следует хранить не более 1 года.
Странное требование на самом деле. Может стоит не показывать операции более одного года?
R>1 вариант. Не хранить, сколько товара было на складе в каждый конкретный момент. Хранить только операции
Это называется двойной бухгалтерской записью, должно применяться для любого учета движения денег или товаров.
R>...Кроме того, чтобы получить состояние складов на сегодня, придется выгружать всю таблицу операций в память, чтобы провести агрегирование — теряем в производительности.
materialized\indexed views
R>А какое Ваше мнение, господа?
В любом случае надо иметь записи об остатках на начало периода (поля в той же таблице проводок, возможно помеченные флагом). Регулярно агрегировать данные, которые выходят за пределы периода и записывать их в остатки на начало.
Алгоритм получения остатков на любую дату будет одинаковым.
Есть еще один неприятный момент отказа от хранения всей истории — невозможность получить бухгалтерскую отчетность и проводить анализ, например выявлять сезонность товаров.
Re[2]: Задачка: модель данных для склада с версионностью
Здравствуйте, gandjustas, Вы писали:
G>Здравствуйте, ronaldo9, Вы писали:
R>>6. Прошедшие операции следует хранить не более 1 года. G>Странное требование на самом деле. Может стоит не показывать операции более одного года?
В данном случае число лет может быть 3, и 5, и 10. Суть в том, что размер БД должен быть как-то ограничен.
Спасибо за ответ. Можно ли где-нибудь еще почитать по данной теме?
Re[3]: Задачка: модель данных для склада с версионностью
Здравствуйте, ronaldo9, Вы писали:
R>Здравствуйте, gandjustas, Вы писали:
G>>Здравствуйте, ronaldo9, Вы писали:
R>>>6. Прошедшие операции следует хранить не более 1 года. G>>Странное требование на самом деле. Может стоит не показывать операции более одного года?
R>В данном случае число лет может быть 3, и 5, и 10. Суть в том, что размер БД должен быть как-то ограничен.
Зачем?
Здравствуйте, ronaldo9, Вы писали:
R>Здравствуйте.
R>У меня вопрос по проектированию модели данных для складов. Общие требования к разрабатываемой системе такие: R>1. Есть несколько складов. На складе располагаются товары. R>2. Есть операции приемки товара на склад, перемещения на другой склад и списания со склада. R>3. Необходима возможность просмотреть прошлое состояние склада (ограничение, например, за год) R>4. Необходимо хранить данные по прошедшим операциям. R>5. Необходима возможность отмены и изменения прошедшей операции приемки/перемещения/списания.
...
1. В любой автоматизированной учетной системе Вы встретите:
— Операции прихода (Дата, Склад, Товар, Кол-во, Некая аналитика — откуда поступило)
— Операции перемещения (Дата, Склад получатель, Склад отправитель, Товар, Кол-во)
— Операции расхода (Дата, Некая аналитика — куда ушло, Склад, Товар, Кол-во)
2. Первоначальные остатки на складе формируются через Операции поступления предыдущим периодом
3. Состояние склада рассчитывается на любую дату
4. В случае большого объема данных, когда сервер СУБД действительно НЕ СПРАВЛЯЕТСЯ, вводится понятие Срез.
В отдельном срезе формируются остатки по складу на определенную дату (Дата, Склад, Товар, Остаток).
Состояние склада рассчитывается на порядок быстрее с учетом среза и операций прошедших только после него.
Однако, механизм Срезов на порядок усложняет систему:
— Необходимо на уровне СУБД запретить исправление операций, проводимых датой раньше любого существующего среза.
— Или пересчитывать все срезы идущие датой после операции
— Функция расчета состояния склада с учетом срезов в реальных задачах имеет массу подводных камней
R>6. Прошедшие операции следует хранить не более 1 года.
Странное условие, если оно не обоснованно техническими ограничениями
Re[2]: Задачка: модель данных для склада с версионностью
Здравствуйте, sereginseregin, Вы писали:
S>Здравствуйте, ronaldo9, Вы писали:
R>>Здравствуйте.
R>>У меня вопрос по проектированию модели данных для складов. Общие требования к разрабатываемой системе такие: R>>1. Есть несколько складов. На складе располагаются товары. R>>2. Есть операции приемки товара на склад, перемещения на другой склад и списания со склада. R>>3. Необходима возможность просмотреть прошлое состояние склада (ограничение, например, за год) R>>4. Необходимо хранить данные по прошедшим операциям. R>>5. Необходима возможность отмены и изменения прошедшей операции приемки/перемещения/списания.
S>...
S>1. В любой автоматизированной учетной системе Вы встретите: S>- Операции прихода (Дата, Склад, Товар, Кол-во, Некая аналитика — откуда поступило) S>- Операции перемещения (Дата, Склад получатель, Склад отправитель, Товар, Кол-во) S>- Операции расхода (Дата, Некая аналитика — куда ушло, Склад, Товар, Кол-во)
Поправка: в хреновой системе встретите, в нормальной системе будет двойная бухгалтерская запись, которая одинаково выражает все операции выше.
S>4. В случае большого объема данных, когда сервер СУБД действительно НЕ СПРАВЛЯЕТСЯ, вводится понятие Срез.
Никаких понятий не надо, сами проводки довольно редко надо получать, надо иметь остаток на дату и обороты за период.
Создается материализованная вьюха в базе, которая суммирует все проводки группируя по датам и аналитикам. Это получатся обороты за день.
Движок материализации в современных субд довольно умен чтобы при измененнии не пересчитывать все.
Остатки — сумма оборотов с начала. Оборот за период — сумма в периоде.
Для большей оптимизации можно ввести дополнительные группировки, например по месяцам, кварталам и годам. Тогда для расчета оборотов в большом периоде и остатков не нужно бегать по всей вьюхе по дням. Это реализуется inline функциями в субд.
И не нужно хранить "срезы" отдельной сущностью.
Причем всю аналитику можно возложить на умный OLAP, который сам все посчитает, только возможность увидеть остатки сразу после операции оприходования пропадет.
Re: Задачка: модель данных для склада с версионностью
> Какой подход избрать для проектирования модели данных?
Проведи анализ. Нужно взять Бухгалтера по товарным запасам (или как они
там классифицируются) и пусть он Вас научит складскому учету. Как только
научитесь, сможете спроектировать систему.
Там будет много чего...
Остатки, накладные, акты. Какие-нибудь инвентаризации, списания в
результате порчи, пересортица, недостачи и возмещения.
Вообще там будет несколько срезов. Один по деньгам (он же используется в
баллансовых отчетах). Другой срез по кол-ву товаров. Там еще выплывут
всякие партии поставок, сроки годности...
В общем — нужно чтобы во время проектирования системы рядом сидел бухгалтер.
И еще стоит рассмотреть вопрос лицензирования какого-нибудь решения.
Думаю найдется такое, которое Вам подойдет.
Posted via RSDN NNTP Server 2.1 beta
Re[4]: Задачка: модель данных для склада с версионностью
Здравствуйте, gandjustas, Вы писали:
G>Здравствуйте, ronaldo9, Вы писали:
R>>Здравствуйте, gandjustas, Вы писали:
G>>>Здравствуйте, ronaldo9, Вы писали:
R>>>>6. Прошедшие операции следует хранить не более 1 года. G>>>Странное требование на самом деле. Может стоит не показывать операции более одного года?
R>>В данном случае число лет может быть 3, и 5, и 10. Суть в том, что размер БД должен быть как-то ограничен. G>Зачем?
Техническое ограничение. Используется бесплатная версия БД с ограничением на объем 4 Гб.
Re[2]: Задачка: модель данных для склада с версионностью
Здравствуйте, Other Sam, Вы писали:
>> Какой подход избрать для проектирования модели данных?
OS>Проведи анализ. Нужно взять Бухгалтера по товарным запасам (или как они OS>там классифицируются) и пусть он Вас научит складскому учету. Как только OS>научитесь, сможете спроектировать систему.
Далеко не все бухгалтеры так сильны в математической модели учета, как хотелось бы.
OS>Там будет много чего... OS>Остатки, накладные, акты. Какие-нибудь инвентаризации, списания в OS>результате порчи, пересортица, недостачи и возмещения. OS>Вообще там будет несколько срезов. Один по деньгам (он же используется в OS>баллансовых отчетах). Другой срез по кол-ву товаров. Там еще выплывут OS>всякие партии поставок, сроки годности...
Смешались в кучу кони, люди...
OS>В общем — нужно чтобы во время проектирования системы рядом сидел бухгалтер.
См выше.
OS>И еще стоит рассмотреть вопрос лицензирования какого-нибудь решения. OS>Думаю найдется такое, которое Вам подойдет.
1С торговля и склад.
Re[5]: Задачка: модель данных для склада с версионностью
Здравствуйте, ronaldo9, Вы писали:
R>Здравствуйте, gandjustas, Вы писали:
G>>Здравствуйте, ronaldo9, Вы писали:
R>>>Здравствуйте, gandjustas, Вы писали:
G>>>>Здравствуйте, ronaldo9, Вы писали:
R>>>>>6. Прошедшие операции следует хранить не более 1 года. G>>>>Странное требование на самом деле. Может стоит не показывать операции более одного года?
R>>>В данном случае число лет может быть 3, и 5, и 10. Суть в том, что размер БД должен быть как-то ограничен. G>>Зачем? R>Техническое ограничение. Используется бесплатная версия БД с ограничением на объем 4 Гб.
Используйте SQL Server 2008 R2, там 10 ГБ.
А вообще задумайтесь о покупке Standart версии, там Olap есть.
Re[6]: Задачка: модель данных для склада с версионностью
Здравствуйте, gandjustas, Вы писали:
R>>>>В данном случае число лет может быть 3, и 5, и 10. Суть в том, что размер БД должен быть как-то ограничен. G>>>Зачем? R>>Техническое ограничение. Используется бесплатная версия БД с ограничением на объем 4 Гб.
G>Используйте SQL Server 2008 R2, там 10 ГБ.
Гигибайты баз — не самое большое ограничение, гораздо хреновее ограничение в используемой памяти в 1 гб, на больших объемах сильно бъет по производительности.
Re[3]: Задачка: модель данных для склада с версионностью
S>>1. В любой автоматизированной учетной системе Вы встретите... G>Поправка: в хреновой системе встретите, в нормальной системе будет двойная бухгалтерская запись, которая одинаково выражает все операции выше.
Не знаю пока такой охренненой учетной системы, в которой только одна таблица проводок. Необходимы еще отдельные таблицы для всяких документов (приход, перемещение, расход, ...). Невозможно всю аналитику, необходимую для управленческих нужд, засунуть в проводку. Слишком большое разнообразие данных и логики. XML поля тоже не помогут.
Но, возможно, к данной задаче это не относится...
G>Создается материализованная вьюха в базе, которая суммирует все проводки группируя по датам и аналитикам. Это получатся обороты за день. G> ... G>Для большей оптимизации можно ввести дополнительные группировки, например по месяцам, кварталам и годам. Тогда для расчета оборотов в большом периоде и остатков не нужно бегать по всей вьюхе по дням. Это реализуется inline функциями в субд. G>И не нужно хранить "срезы" отдельной сущностью. G>Причем всю аналитику можно возложить на умный OLAP, который сам все посчитает, только возможность увидеть остатки сразу после операции оприходования пропадет.
Я предпочитаю запускать расчет среза отдельной сущностью (а не "материализованная вьюха") в базе в конце рабочего дня, когда в системе минимальная нагрузка и мой расчет не тормозит систему у пользователей. А функция расчета остатков сама определяет, какой срез ближайший, оставшиеся обороты собирает из проводок. Собирать обороты по дням, месяцам, годам не было необходимости.
Re[6]: Задачка: модель данных для склада с версионностью
Здравствуйте, sereginseregin, Вы писали:
S>Здравствуйте, gandjustas, Вы писали:
S>>>1. В любой автоматизированной учетной системе Вы встретите... G>>Поправка: в хреновой системе встретите, в нормальной системе будет двойная бухгалтерская запись, которая одинаково выражает все операции выше. S>Не знаю пока такой охренненой учетной системы, в которой только одна таблица проводок. Необходимы еще отдельные таблицы для всяких документов (приход, перемещение, расход, ...). Невозможно всю аналитику, необходимую для управленческих нужд, засунуть в проводку. Слишком большое разнообразие данных и логики. XML поля тоже не помогут.
Надо было весь кусок цитировать. Были указаны три, вроде как различные, операции, хотя с головой хватает двойной бухгалтерской записи. Я не говорил о документах, таблицах и тому подобном.
G>>Создается материализованная вьюха в базе, которая суммирует все проводки группируя по датам и аналитикам. Это получатся обороты за день. G>> ... G>>Для большей оптимизации можно ввести дополнительные группировки, например по месяцам, кварталам и годам. Тогда для расчета оборотов в большом периоде и остатков не нужно бегать по всей вьюхе по дням. Это реализуется inline функциями в субд. G>>И не нужно хранить "срезы" отдельной сущностью. G>>Причем всю аналитику можно возложить на умный OLAP, который сам все посчитает, только возможность увидеть остатки сразу после операции оприходования пропадет.
S>Я предпочитаю запускать расчет среза отдельной сущностью (а не "материализованная вьюха") в базе в конце рабочего дня, когда в системе минимальная нагрузка и мой расчет не тормозит систему у пользователей.
А думаешь материализованные вьюхи будут что-то тормозить? Не так часто добавляются проводки чтобы пересчет серьезно влиял на скорость работы.
S>А функция расчета остатков сама определяет, какой срез ближайший, оставшиеся обороты собирает из проводок. Собирать обороты по дням, месяцам, годам не было необходимости.
Ты сам себе противоречишь. Обороты по дням, месяцам, годам — это выполняют ту же функцию что твои "срезы", только считаются они автоматически в момент запроса и при этом имеют ту же самую структуру что и все остальные проводки, поэтому банально union писать легче в функции расчета остатков.
Re[5]: Задачка: модель данных для склада с версионностью
Здравствуйте, gandjustas, Вы писали:
G>Были указаны три, вроде как различные, операции, хотя с головой хватает двойной бухгалтерской записи. Я не говорил о документах, таблицах и тому подобном.
Для бухгалтерии хватает, для управленческого учета не всегда. Часто приходится собирать в реальном времени остатки и обороты по документам.
G>А думаешь материализованные вьюхи будут что-то тормозить? Не так часто добавляются проводки чтобы пересчет серьезно влиял на скорость работы.
У нас в год 3000000 проводок (8500 в день). Расшифровки остатков и оборотов бухгалтера собирают вдоль и поперек тоже ежесекундно, их не остановтЬ
G>Ты сам себе противоречишь. Обороты по дням, месяцам, годам — это выполняют ту же функцию что твои "срезы", только считаются они автоматически в момент запроса и при этом имеют ту же самую структуру что и все остальные проводки, поэтому банально union писать легче в функции расчета остатков.
Может не так выразился, собирать СРЕЗЫ оборотов не было необходимости, postgres пока справляется.
Re[6]: Задачка: модель данных для склада с версионностью
Здравствуйте, sereginseregin, Вы писали:
S>Здравствуйте, gandjustas, Вы писали:
G>>Были указаны три, вроде как различные, операции, хотя с головой хватает двойной бухгалтерской записи. Я не говорил о документах, таблицах и тому подобном. S>Для бухгалтерии хватает, для управленческого учета не всегда. Часто приходится собирать в реальном времени остатки и обороты по документам.
Двойная бухгалтерская запись этому никак не мешает, а только помогает. Из-за того что все операции хранятся едино кода расчетов получается проще.
G>>А думаешь материализованные вьюхи будут что-то тормозить? Не так часто добавляются проводки чтобы пересчет серьезно влиял на скорость работы. S>У нас в год 3000000 проводок (8500 в день).
В среднем одна проводка в 2 сек. Не страшно для пересчета.
Re[3]: Задачка: модель данных для склада с версионностью
>> > Какой подход избрать для проектирования модели данных? > > OS>Проведи анализ. Нужно взять Бухгалтера по товарным запасам (или как они > OS>там классифицируются) и пусть он Вас научит складскому учету. Как только > OS>научитесь, сможете спроектировать систему. > Далеко не все бухгалтеры так сильны в математической модели учета, как > хотелось бы.
Не верно. У квалифицированного бухгалтера должно хватать на это знаний.
Иначе он не бухгалтер.
> > OS>Там будет много чего... > OS>Остатки, накладные, акты. Какие-нибудь инвентаризации, списания в > OS>результате порчи, пересортица, недостачи и возмещения. > OS>Вообще там будет несколько срезов. Один по деньгам (он же используется в > OS>баллансовых отчетах). Другой срез по кол-ву товаров. Там еще выплывут > OS>всякие партии поставок, сроки годности... > Смешались в кучу кони, люди...
О чем это вы?
> OS>В общем — нужно чтобы во время проектирования системы рядом сидел > бухгалтер. > См выше. > > OS>И еще стоит рассмотреть вопрос лицензирования какого-нибудь решения. > OS>Думаю найдется такое, которое Вам подойдет. > 1С торговля и склад.
Возможно.
---
gangjustas, зачем был ваш пост?
Posted via RSDN NNTP Server 2.1 beta
Re[4]: Задачка: модель данных для склада с версионностью
Здравствуйте, Other Sam, Вы писали:
>>> > Какой подход избрать для проектирования модели данных? >> >> OS>Проведи анализ. Нужно взять Бухгалтера по товарным запасам (или как они >> OS>там классифицируются) и пусть он Вас научит складскому учету. Как только >> OS>научитесь, сможете спроектировать систему. >> Далеко не все бухгалтеры так сильны в математической модели учета, как >> хотелось бы.
OS>Не верно. У квалифицированного бухгалтера должно хватать на это знаний. OS>Иначе он не бухгалтер.
Сама предметная область и её мат. модель — сильно разные вещи бывают.
При развитии компьютерных систем все чаще бухгалтеры оперируют не проводками, остатками и оборотами, а "документами", "товаром на складе", "доходами", "налогами" и другими объектами, специфичными для бизнеса.
>> >> OS>Там будет много чего... >> OS>Остатки, накладные, акты. Какие-нибудь инвентаризации, списания в >> OS>результате порчи, пересортица, недостачи и возмещения. >> OS>Вообще там будет несколько срезов. Один по деньгам (он же используется в >> OS>баллансовых отчетах). Другой срез по кол-ву товаров. Там еще выплывут >> OS>всякие партии поставок, сроки годности... >> Смешались в кучу кони, люди...
OS>О чем это вы?
О том что не надо все мешать в одну кучу.
OS>gangjustas, зачем был ваш пост?
Примерно о том же что и ваш: о том как писать учет и как его не писать.
Re: Задачка: модель данных для склада с версионностью
> Сама предметная область и её мат. модель — сильно разные вещи бывают. > При развитии компьютерных систем все чаще бухгалтеры оперируют не > проводками, остатками и оборотами, а "документами", "товаром на складе", > "доходами", "налогами" и другими объектами, специфичными для бизнеса. >
И правильно делают! Система должна подстраиваться под людей, а не люди
под систему! Пофиг какая там мат модель, бухгалтер должен там увидеть
то, что он ожидает. Ожидает "документ" — значит должен быть документ.
Как это будет устроенно внутри — это для бухгалтера не имеет никакого
значения.
Что хочу сказать? Пользователь прав! В данном случае — бухгалтер прав!
Читайте "Психбольница в руках пациентов" Алана... забыл фамилию.
>> > >> > OS>Там будет много чего... >> > OS>Остатки, накладные, акты. Какие-нибудь инвентаризации, списания в >> > OS>результате порчи, пересортица, недостачи и возмещения. >> > OS>Вообще там будет несколько срезов. Один по деньгам (он же > используется в >> > OS>баллансовых отчетах). Другой срез по кол-ву товаров. Там еще выплывут >> > OS>всякие партии поставок, сроки годности... >> > Смешались в кучу кони, люди... > > OS>О чем это вы? > О том что не надо все мешать в одну кучу.
Прелагайте как разделить эти вещи по разным кучам. Я выслушаю.
