Управление запасами сборок и их составных частей (взаимоотношения)

Я должен отслеживать запас отдельных деталей и комплектов (узлов) и не могу найти удовлетворительного способа сделать это. Пример поддельной и гиперупрощенной базы данных:

Table prod:
prodID  1
prodName Flux capacitor
prodCost 900
prodPrice 1350 (900*1.5)
prodStock 3
-
prodID  2
prodName Mr Fusion
prodCost 300
prodPrice 600 (300*2)
prodStock 2
-
prodID  3
prodName Time travel kit
prodCost 1200 (900+300)
prodPrice 1560 (1200*1.3)
prodStock 2

Table rels
relID  1
relSrc  1 (Flux capacitor)
relType  4 (is a subpart of)
relDst  3 (Time travel kit)
-
relID  2
relSrc  2 (Mr Fusion)
relType  4 (is a subpart of)
relDst  3 (Time travel kit)

prodPrice: рассчитывается на основе стоимости, но не линейным способом. В этом примере для стоимости 500 или меньше наценка составляет 200%. При стоимости 500-1000 наценка 150%. При стоимости 1000+ наценка составляет 130% Вот почему набор для путешествий во времени намного дешевле, чем отдельные детали.

prodStock: вот моя проблема. Я могу продавать комплекты или отдельные детали, так что запас комплектов виртуальный.

Проблема при покупке: некоторые поставщики продают мне комплект для путешествий во времени целиком (с одним штрих-кодом), а некоторые продают мне отдельные части (с другим штрих-кодом). .

Проблема, когда я продаю: если я продаю только комплекты, подсчитать запасы будет легко: «У меня есть 3 конденсатора Flux и 2 Mr Fusions, поэтому у меня есть 2 набора для путешествий во времени и конденсатор Flux». Но я могу продавать комплекты или отдельные детали. . Таким образом, я должен одновременно отслеживать запасы отдельных деталей и возможных комплектов (и я должен компенсировать цену продажи).

Наверное, это действительно просто, но я не вижу простого решения. Резюме: я должен найти способ отслеживать запасы, и база данных/программа должна это делать (я не могу попросить клерка исправить запас)

Я использую php+MySql. Но это больше логическая задача, чем программная

Обновление: к сожалению, решение Eagle не будет работать.

  • отношения могут и являются рекурсивными (один набор использует другой набор)
  • Есть комплекты, в которых используется более одной детали (2 конденсатора потока + 1 Mr Fusion).
  • Мне действительно нужно сохранить стоимость запаса комплекта. Та же база данных используется для веб-страницы, на которой пользователи хотят покупать детали. И я должен показать доступный запас (иначе они даже не попытаются купить). И не может позволить себе подсчитывать акции при каждом поиске пользователя на веб-странице.

Но мне понравилась идея с логическим значением, помечающим акции как виртуальные


mysql database database-design database-relations
person The Disintegrator    schedule 03.06.2010    source источник
comment
Разве prodCost из prodID 3 не должно быть 900 + 300 (или хотя бы 1350 + 600)?   -  person Senseful    schedule 03.06.2010
comment
Да, я передумал насчет стоимости запчастей и забыл изменить стоимость комплекта. Обновлен мой ответ   -  person The Disintegrator    schedule 03.06.2010

Ответы (1)


arrow_upward
1
arrow_downward

Хорошо, во-первых, поскольку prodStock для набора для путешествий во времени является виртуальным, вы не можете хранить его в базе данных, это будет по сути вычисляемое поле. Вероятно, было бы полезно, если бы у вас было логическое значение в таблице, которое говорит, вычисляется ли prodStock или нет. Я представлю, что у вас есть это поле в таблице, и назову его пока isKit (где TRUE означает, что это комплект, а prodStock нужно вычислить).

Теперь, чтобы рассчитать количество каждого товара, который есть на складе:

select p.prodID, p.prodName, p.prodCost, p.prodPrice, p.prodStock from prod p where not isKit
union all
select p.prodID, p.prodName, p.prodCost, p.prodPrice, min(c.prodStock) as prodStock
from 
  prod p
  inner join rels r on (p.prodID = r.relDst and r.relType = 4)
  inner join prod c on (r.relSrc = c.prodID and not c.isKit)
where p.isKit
group by p.prodID, p.prodName, p.prodCost, p.prodPrice

Я использовал псевдоним c для второго продукта, обозначающего «компонент». Я явно написал not c.isKit, так как это не будет работать рекурсивно. union all используется вместо union из соображений эффективности, поскольку они оба возвращают одинаковые результаты.

Предостережения:

  • Это не будет работать рекурсивно (например, если для набора требуются компоненты из другого набора).
  • Это работает только с наборами, для которых требуется только один конкретный предмет (например, если набор для путешествий во времени требует 2 потоковых конденсатора и 1 мистера Фьюжн, это не сработает).
  • Я не проверял это, поэтому могут быть небольшие синтаксические ошибки.
  • Это вычисляет только поле prodStock; для других полей вам понадобится аналогичная логика.

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

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

person Senseful    schedule 03.06.2010
comment
Мне нравится идея логического значения. Обновил мой вопрос. Я использую правильное слово для этого? Это отношения между продуктами или лучше сказать, и поэтому мой вопрос остается незамеченным? - person The Disintegrator; 03.06.2010