Как хранить историю наличия и приходов/расходов в mysql?
Нужно организовать создание и хранение данных на сайте php+mysql:
- актуальное наличие, которое можно менять вручную (модель, размер, цвет, количество)
- приходы (модель, размер, цвет, количество)
- расходы (модель, размер, цвет, количество, цена)
- возможность сформировать отчетную таблицу в заданном интервале времени, например, месяц.
1. Как правильно организовать хранение данных с учетом того, что приходов, расходов и изменений наличия вручную может быть по несколько в день и каждая операция может содержать десятки строк?
Моя идея хранить актуальное наличие, каждый раз перезаписывая его после операции. Изменения наличия вручную хранить как отдельные операции по приходу/расходу. Ежемесячно создавать лог наличия моделей, а в случае необходимости построить отчет, брать ближайший лог наличия и от него высчитывать операции в заданном интервале.
2. Как хранить историю наличия, если цена товара может быть разной в зависимости от времени прихода на склад?
Например, может быть ситуация, когда на складе есть 100 моделей по 10 размеров по 10 цветов по 30 штук, итого 300 000 позиций. Хранить в строках с разделителями? В файлах?
sangan, оно не так сложно, как кажется.
По сути примерно то же что вами и Дмитрий написал.
Только в более обобщенном (универсальном) виде, потому и кажется сложным.
stratosmi, Я работал долго с 1С, сейчас перешел на вэб и обнаружил что с таблицами остатков в вэбе часто не заморачиваются. Просто считают приходы и расходы от начала веков. И на современных серверах это работает почти незаметно даже на миллионах записей.
Я работал долго с 1С, сейчас перешел на вэб и обнаружил что с таблицами остатков в вэбе часто не заморачиваются. Просто считают приходы и расходы от начала веков.
Проблема в низкой, в среднем, квалификации программистов.
1С это взяла на себя - платформа работает с такими вещами, а прикладному программисту предоставляются уже готовые инструменты, которые все это умеют.
В вебе пилят самостоятельно, квалификации не хватает, что приводит к интересным побочным эффектам - когда сайт интернет магазина каждую страничку с товаром потенциальному покупателю по паре секунд готовит.
И на современных серверах это работает почти незаметно даже на миллионах записей.
Вы путаете ВЫБОРКУ по условию, когда есть правильные индексы. Правильные индексы позволяют НА САМОМ ДЕЛЕ вовсе не работать с миллионами записей, а брать отдельные ТОЛЬКО НУЖНЫЕ из них.
И АГРЕГАЦИЮ (подсчет итогов). Агрегация завсегда значительно дольше. Так как без вспомогательной таблицы итогов (заранее подсчитанных) приходится по всем записям пробегаться их суммируя. Индексы помогают только фильтр по товару (и т.п.) установить, чтобы хоть как-то облегчить работу сервера.
и обнаружил что с таблицами остатков в вэбе часто не заморачиваются. Просто считают приходы и расходы от начала веков.
Как правило приходы и расходы вообще не хранят (по крайней мере для подсчета общего итога не используют).
А хранят только текущий остаток и пишут в него напрямую. Что довольно рисково и означает необходимость более аккуратно выполнять операции, изменяющие остатки.
Вопрос топикстартера в этом смысле меня удивил. Довольно грамотно он подошел, обстоятельно.
stratosmi, Да, в плане построения БД конечно все делается очень неграмотно. О том, чтобы хранить историю цен, программисты даже не задумываются, просто дают менеджерам править руками запись, а потом менеджеры начинают ставить задачу - как бы нам историю цен логгировать.
Что такое значительно дольше? 5 секунд вместо полсекунды? Как часто нужны агрегатные отчеты за год? Ну может раз в неделю. Это же не бухгатерия перед сдачей баланса. Естественно никого не парит подождать лишние 5 секунд.
Что такое значительно дольше? 5 секунд вместо полсекунды?
Зависит от конкретных данных.
Но вы упомянули про миллион строк. Суммировать миллион строк без заранее подготовленных таблицы с рассчитанными уже остатками - это вам не хухры мухры.
Как часто нужны агрегатные отчеты за год? Ну может раз в неделю. Это же не бухгатерия перед сдачей баланса. Естественно никого не парит подождать лишние 5 секунд.
Отнюдь не раз в неделю. Подсчет количества остатков нужно осуществлять при КАЖДОЙ операции:
1) При отображении товара на сайте, чтобы показать или "временно нет в наличие" или кнопку "купить, положить в корзину". А еще и по целому списку товаров рассчитывать остатки, когда отображается весь товар определенной группы товаров и т.п. Надо ли упоминать, что просмотр товаров и пользователями и ботами (эти вообще по всем товарам пробегают) делается и даже чаще чем 1000 раз по сравнению с количество операций по реальной покупке товара? Но нам все равно каждый из этих раз нужно отображать товар с действительными актуальными остатками.
2) При каждой операции завершения оформления заказа.
Спасибо за отклик! По структуре понятно более или менее. Грубо, наличие может содержать 300 000 строк без группировки единиц товара по ценам (amount = 1 для каждой позиции) и, скажем, 10к - 30к строк, если сгруппировать позиции с одинаковыми ценами. Лог раз в месяц - это Х12. Операций, если хранить построчно каждую единицу или даже объединяя по одинаковой цене, будет тоже очень много. Внутри движения тоже может отказаться разная цена, поэтому я предполагал хранить каждую позицию отдельной строкой. Но объем данных в любом случае довольно большой. Это допустимо?
и, скажем, 10к - 30к строк, если сгруппировать позиции с одинаковыми ценами.
Группировать не имеет смысла. Данные должны быть проиндексированы по моделям, чтобы к ним был моментальный доступ:
а) При контроле остатков, чтобы случайно не продали больше чем есть
б) При составлении отчетов по остаткам
Но объем данных в любом случае довольно большой. Это допустимо?
Слово "большой" не имеет смысла само по себе. Большой в стравнении с чем? Я думаю он у вас средний. Несколько миллионов записей в год вполне допустимо для современных компьютеров, я бы даже сказал это ничто. Раз в год вы можете архивировать таблицу движений, перенося ее в отдельную базу или файл и оставляя только остатки на конец года.
То есть допустимо убрать поле количество и хранить каждую единицу товара, даже одного размера и цвета в отдельной строке, так как цена может быть разной? В случае прихода 3-х единиц, например, вставлять 3 строки, а в случае расхода удалять?
А зачем его убирать, у вас же не со всеми моделями будут разные цены в одной поставке?
В случае прихода 3-х единиц, например, вставлять 3 строки, а в случае расхода удалять?
У вас ничего не удаляется. Приходы отражаются в движениях с типом "приход", расходы с типом "расход". Единственное тогда нужно будет в расходах либо указывать, либо автоматически цеплять цену поставки, чтобы схлопнулись остатки.
Вообще Вы абсолютно вольны делать любые разрезы аналитики. Главное, чтобы их состав был хорошо продуман, и чтобы при полной продаже модели-размера-цвета остаток по ней схлопывался и не переходил на следующий месяц.
В остатках помимо цены храните либо id движения, либо дату прихода, если хотите узнать, когда этот остаток образовался.
Пардон, не уточнил. В таблицу движений только вставлять. Вставлять/удалять в таблицу остатков я имел в виду. Чтобы:
1. Не придумывать логику, по какой цене списывать товар, если есть разные цены или если списывается больше, чем есть по одной цене, но меньше, чем есть всего. Просто хранить в каждой строке в том числе и цену и удалять нужное количество строк в любом порядке, который можно в любое время легко поменять.
2. Не просчитывать количество товаров с одинаковыми ценами во время сохранения операций.
3. При формировании отчетов проще сортировать, суммировать позиции построчным обходом.
Не придумывать логику, по какой цене списывать товар, если есть разные цены или если списывается больше, чем есть по одной цене, но меньше, чем есть всего.
Можно списывать по FIFO, первый пришел, первый ушел. Получаете остатки по модели, сортируете по дате прихода и списываете построчно нужное количество. Вроде это очень простой алгоритм.
Не просчитывать количество товаров с одинаковыми ценами во время сохранения операций.
А как вы их будете физически вводить? Построчно? То есть если 10 товаров с одинаковой ценой, оператор дублирует каждую строку? Это странно - зачем?
При формировании отчетов проще сортировать, суммировать позиции построчным обходом.
Вообще-то сортировать суммировать это работа SQL-запросов, в чем смысл делать это построчно?
Или все-таки стоит группировать?
Я не вижу проблемы с группировкой, поле количество вам никак не помешает, даже если вы хотите хранить каждую строку отдельно - ставьте 1, передумаете - логика никак не нарушится. Я просто не знаю общую картину. Если каждая вещь приходит к вам по отдельной цене это одно, а если пришли 10 моделей одного цвета и размера по одной цене, было бы странно создавать в таблице остатков 10 строк, вместо того, чтобы создать одну и поставить количество 10.
Вы же делаете подсчет раз в месяц (кстати стоит проанализировать, может достаточно раз в квартал), кроме тех случаев когда операции вводятся-редактируются за прошлый месяц.
Полагаю вам нужно просто создать таблицы, заполнить их миллионом тестовых записей и поиграться с запросами, посмотреть на производительность.
Средний
