Блог

Я пытаюсь спроектировать базу данных, которая включает продукты, типы продуктов, атрибуты продукта, в которой также хранятся стоимость и запасы (inventory). Я бы хотел, чтобы это имело как можно меньше const / hardcoding, а также было масштабируемым в будущем.

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

Вот моя текущая схема базы данных:

Я думал о добавлении столбца ‘ProductAttributeID’ в таблицы ProductPricing и Product Stock / Supply.

Это сработало бы для одного атрибута, но не в том случае, если бы учитывались два.

ie:

 Product = Shirt
Attribute = Size (small, medium, large)
Attribute#2 = Color (red, green, blue)
...
  

Я полагаю, я мог бы создать таблицу ProductAttributeModifier, чтобы добавлять / вычитать / делить / умножать значение на общее значение.

 ProductAttributeModifier
========================
ProductAttributeID (bigint) FK_,
Operator (char(1)), // , -, *, /, %
CostValue decimal(7,2)
  

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

Есть ли какие-либо подводные камни в этом методе?

Как бы я поступил с поставкой продуктов? Я думал о добавлении нескольких столбцов в качестве ограничений внешнего ключа для разных атрибутов ProductAttributes, но это не масштабируемо и требует будущих знаний о продукте.

Возможно, будет ли другая таблица работать как соединение с отдельным количеством? С повторением всех атрибутов? Есть ли подводные камни и в этом методе?

 ProductAttributeSupply
======================
ProductSupplyId (bigint) FK_,
AttributeID (bigint) FK_,
Quantity (int/bigint)
  

Было бы разумнее поместить общую совокупность поставок в view / sproc?

Я ожидаю, что диаграмма будет обрабатывать следующие сценарии:

1. Продукт без одного или нескольких атрибутов
2. Будьте готовы к атрибутам, изменяющим стоимость (от средней до большой)
3. Должен ли модификатор стоимости быть необязательным
4. Рассчитайте необходимое количество продукта со всеми атрибутами, соответствующими нашему инвентарю
5. Правильно сохраняйте количество продуктов, соответствующих каждому атрибуту, когда мы получаем в нашей базе данных. ‘ProductStock’

Ваш ProductAttributeModifier подход мог бы сработать, но я не понимаю, зачем вам нужно что-то менять в отношении поставок.

 Products:
   - 1: small green shirt
   - 2: large green shirt

Attributes:
  - 1: size
  - 2: color

ProductAttributes (ProductId, AttributeId):
  - (1, 1) small
  - (1, 2) green
  - (2, 1) large
  - (2, 2) green
  

Вам не нужны атрибуты в вашем складе / поставке, у вас просто есть количества на маленьких зеленых рубашках и больших зеленых рубашках, верно?

Если вы хотите, чтобы атрибуты изменяли базовую цену, просто используйте FK ProductPricing в ProductType («рубашка») и получите представление, которое рассчитает цену продукта на основе цены типа атрибуты, скажите что-то вроде:

 CREATE VIEW vProductWithPrice
AS
SELECT p.Id
     , (pp.Price 
         SUM(CASE av.PriceModifierOperator
               WHEN '-' THEN -av.PriceModifierValue
               WHEN ' ' THEN  av.PriceModifierValue
               WHEN '%' THEN pp.Price * av.PriceModifierValue / 100.0
               ELSE 0
             END)
       ) AS Price
  FROM Products p
  JOIN ProductTypes pt
    ON p.ProductTypeId = pt.ProductTypeId
  JOIN ProductPricing pp
    ON pp.ProductTypeId = pt.ProductTypeId
   AND pp.TerminationDate IS NULL -- currently active
  JOIN ProductAttributes pa
    ON pa.ProductId = p.ProductId
  JOIN AttributeValue av
    ON av.AttributeValueId = pa.AttributeValueId
 GROUP BY p.Id, pp.Price
  

Здесь я использую что-то вроде AttributeValue с полями типа:

   - AttributeValueId
  - AttributeId (link to e.g. size)
  - Value (small/large/etc.)
  - PriceModifierOperator ( , -, %)
  - PriceModifierValue
  

Имейте в виду, что выполнение таких операторов, как * или / , потребует действительно тяжелой работы, потому что:

1. необходимо учитывать порядок (вы применяете только базовую цену или после атрибутов, или, может быть, после некоторых атрибутов, но перед другими; в конце концов 5 * 3 1 2 != (5 1) * 3 2 != (5 1 2) * 3; и
2. объединение нескольких операторов * было бы проблемой, вы не можете просто сделать BasePrice * SUM(value) , если один равен * 2, а другой равен * 3, тогда вы получите BasePrice * 5 вместо BasePrice * 6 , и есть предостережения при использовании EXP(SUM(LOG(Value)))