如何在SQL中管理产品选项和仓库物料之间的关系 [英] How can I manage the relationship between product options and warehouse items in SQL
问题描述
我正在建立一个电子商务网站.
I'm building an e-commerce site.
在nordstrom机架网站上查看该产品的选项.
Take a look at the options on this product at the nordstrom rack website.
My website will have something similar.
问题是，网站上的1种产品(带有3x3选项)实际上在仓库"中转换为9种不同的产品.
The problem is, 1 product on the website, with 3x3 options, actually translates to 9 different products at the "warehouse".
因此，我有一个用于网站产品的数据库和一个用于仓库条目的数据库.
So I have a database for website products, and a database for warehouse entries.
假设变量选项的数量不确定，如何定义这两个数据库之间的关系?
How do I define the relationship between these two databases, assuming an indefinite amount of variable options?
我的解决方案是基本上拥有一个将两者连接的EAV数据库.它将存储仓库项目的单个属性，并且还将具有将其与网站产品相关联的ID.将查询以确定哪个仓库项目与所选产品选项相关.
My solution is to basically have an EAV database that connects the two. It would store single attributes for warehouse items, and would also have ID's that relate it to the website product. It would be queried to identify which warehouse item relates to the selected product options.
它看起来像这样:
product_id，warehouse_id，attr，value
product_id,warehouse_id,attr,value
仓库数据库看起来像这样(简化)
And the warehouse db would look something like this (simplified)
warehouse_id，product_id，quantity_available
warehouse_id,product_id,quantity_available
product_id是该商品所属网站上的产品.
Where the product_id is the product on the website that this item belongs to.
因此，如果我想找到一个属于product_id:1337并具有颜色color:green和size:small的仓库物品，我会这样做:
So, if I wanted to find a warehouse item that belongs to product_id : 1337 and has attributes color : green and size : small, I would do it like this:
SELECT warehouse_id FROM DATABASE_NAME WHERE product_id=1337 AND
((attr='color' AND value='green') OR (attr='size' AND value='small'));
然后我将解析结果集，并找到2个具有相同Warehouse_id的条目.那将是同时具有color = green和size = small的id.
I would then parse the result set and find 2 entries that have the same warehouse_id. That would be the id that has both color = green and size = small.
但是，我认为这种方法很糟糕.它需要创建一个荒谬的大型数据库.在3x3选项的情况下，每个产品需要9个仓库条目，每个仓库条目需要2个EAV条目，这意味着一个产品总共需要18个EAV条目.
However, I think this method sucks. It requires to create a preposterously large database. Where in a 3x3 option case, you need 9 warehouse entries for every product, you would need 2 EAV entries for every warehouse entry, which means 18 total EAV entries for a product.
你们有更好的建议吗?
我想出了如何使用内部联接简化查询.它不需要解析.
I figured out how to simplify my query with inner join. It requires no parsing.
select a.warehouse_id from warehouse_EAV a, warehouse_EAV b
where a.product_id=1337 AND a.warehouse_id=b.warehouse_id AND
(a.attr='color' AND a.val='green') AND (b.attr='size' AND b.val='small');
推荐答案
我不确定我是否理解荒谬的大型"数据库的含义.没有魔法，您必须真正存储所有难题.不过，我可能会完全想不到您的问题.
I'm not sure I understand what you mean by "preposterously large" database. There is no magic, you have to store all the pieces of the puzzle really. I might just be missing your question entirely, though.
在我看来，模型听起来像:
Seems to me the model sounds something like:
产品具有产品差异.仓库存储产品变化的库存.
Products has product variations. Warehouses store inventory of product variations.
PRODUCTS (product_id, name)
PRODUCT_VARIATIONS (product_var_id, product_id, variation_id, value)
VARIATION_DIMENSION (product_id, variation_id, name)
VARIATION_CHOICES (variation_id, choice_value)
WAREHOUSES (warehouse_id, name, location)
INVENTORY (product_var_id, warehouse_id, quantity, cost, on_order)
，如果您要展示产品:
select p.product_id, name, min(price) min_price, max(price) max_price
from products p, product_variations pv
where p.product_id = pv.product_id
group by p.product_id, name
示例.
您有一件衬衫.它以S，M，L进来.它有红色和蓝色.
You have a Shirt. It comes in S, M, L. It has Red and Blue.
PRODUCTS:
( 'SHIRT1', 'Really cool shirt!' )
PRODUCT_VARIATION: (Key is product_var_id, variation_id)
( 'R_S_SHIRT1', 'SHIRT1', 'SHIRT1_COLOR', 'Red' )
( 'R_S_SHIRT1', 'SHIRT1', 'SHIRT1_SIZE', 'Small' )
( 'R_M_SHIRT1', 'SHIRT1', 'SHIRT1_COLOR', 'Red' )
( 'R_M_SHIRT1', 'SHIRT1', 'SHIRT1_SIZE', 'Medium' )
( 'R_L_SHIRT1', 'SHIRT1', 'SHIRT1_COLOR', 'Red' )
( 'R_L_SHIRT1', 'SHIRT1', 'SHIRT1_SIZE', 'Large' )
( 'B_S_SHIRT1', 'SHIRT1', 'SHIRT1_COLOR', 'Blue' )
( 'B_S_SHIRT1', 'SHIRT1', 'SHIRT1_SIZE', 'Small' )
( 'B_M_SHIRT1', 'SHIRT1', 'SHIRT1_COLOR', 'Blue' )
( 'B_M_SHIRT1', 'SHIRT1', 'SHIRT1_SIZE', 'Medium' )
( 'B_L_SHIRT1', 'SHIRT1', 'SHIRT1_COLOR', 'Blue' )
( 'B_L_SHIRT1', 'SHIRT1', 'SHIRT1_SIZE', 'Large' )
VARIATION_DIMENSIONS (Key is product_id, variation_id)
('SHIRT1', 'SHIRT1_COLOR', 'Color')
('SHIRT1', 'SHIRT1_SIZE', 'Size')
VARIATION_CHOICES (Key is both variation_id, choice_value)
('SHIRT1_COLOR', 'Red')
('SHIRT1_COLOR', 'Blue')
('SHIRT1_SIZE', 'Small')
('SHIRT1_SIZE', 'Medium')
('SHIRT1_SIZE', 'Large')
WAREHOUSES
('NYC01', 'Main Warehouse', 'New York, NY')
INVENTORY
( 'R_S_SHIRT1', 'NYC01', 5, 3.00, 0 )
( 'R_M_SHIRT1', 'NYC01', 0, 3.00, 10 )
( 'R_L_SHIRT1', 'NYC01', 6, 3.00, 0 )
( 'B_S_SHIRT1', 'NYC01', 1, 3.00, 10 )
( 'B_M_SHIRT1', 'NYC01', 0, 3.00, 10 )
( 'B_L_SHIRT1', 'NYC01', 3, 3.00, 0 )
库存的红色小衬衫有多少?
How many red small shirts are in stock?
SELECT *
FROM INVENTORY
WHERE product_var_id IN (
SELECT product_var_id
FROM PRODUCT_VARIATIONS
WHERE value = 'Red'
AND variation_id = 'SHIRT1_COLOR'
INTERSECT
SELECT product_var_id
FROM PRODUCT_VARIATIONS
WHERE value = 'Small'
AND variation_id = 'SHIRT1_SIZE'
)
这篇关于如何在SQL中管理产品选项和仓库物料之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
