POS 数据库布局
我们当前的商店库存数据库布局包括一个表，其中包含每个商品的记录，并包括价格、成本、描述、SKU 等商品属性。然后，每个商店都有一个包含 SKU 和数量的表，以及一个根据 SKU，每个商店的其他属性也有所不同。
虽然这比在 Items 表中为每个商店的数量有一列要好，但这似乎也是一个有点丑陋的解决方案，因为当添加新商店时，您需要选择一个新表以及添加新商品时，您不仅必须插入项目表，还必须插入每个单独的商店表。
有没有比我所看到的更好的方法来解决这个问题？感觉好像是有的。
Our current database layout for store inventory includes a table that houses a record for each item and includes the item attributes such as price, cost, description, SKU, etc. Each store then has a table that has the SKU and the Quantity, and a few other attributes that are different for each store depending on the SKU.
Although this is better than having a column for each store's quantity in the Items table, it seems that this too is a somewhat ugly solution as when a new store is added, you need to select into a new table and when a new item is added, you have to insert into not only the items table but each individual store table.
Is there a better way to go about this than how I'm seeing it? It feels like there is.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
为什么不建立一个包含 SKU、StoreId 和库存级别的库存表？
例如
，商店 002 的 sku 1235 缺货，但商店 001 有充足的库存。此表格布局还允许您使用以下内容在单个表格中获得整个集团范围内的库存水平视图
Why not have a stock table with SKU, StoreId and Stock levels?
e.g.
so, store 002 is out of stock of sku 1235, but store 001 has plenty. This table layout also allows you to get a group-wide view of your stock levels in a single table using something like
通过遵循数据规范化的规则，您可能希望创建一个如下所示的表：
本质上，它是一个 store_inventory 表。这样，您就可以通过说等等来过滤到给定的商店
...
By following the rules of data normalization, you would want to create a table like this:
Essentially, it is a store_inventory table. That way, you can filter down to a given store by saying
etc...
据我所知，您确实需要三个表：
Product
StoreProduct
Store
这是一个完全有效且标准化的数据库设计，听起来基本上就是您想要的现在就有了。
You really want three tables as far as I can see:
Product
StoreProduct
Store
That's a perfectly valid and normalised database design, and sounds basically what you have right now.
听起来好像它一开始就走上了正确的道路，为该项目的所有公共属性建立了一个表。
所有商店的库存计数不应位于每个商店的单独表中，而是应位于一个附加表中，并使用商品键和商店键作为该表上的组合主键。
例如
项目
ItemKey
姓名
价格
SKU
商店
StoreKey
姓名
地址
库存
StoreKey
项目密钥
数量
在 Inventory 表上同时使用 StoreKey 和 ItemKey 将使您的记录保持唯一。
这将使您不仅可以轻松查找单个项目，还可以查找以下内容：
按 StoreKey 查询一家商店中所有商品的数量
按 ItemKey 计算所有商店中一件商品的数量
所有商店中哪些商品的数量较低
ETC
It sounds like it started on the right path with having one table for all of the common attributes of the item.
Instead of having an individual table for each store, the inventory counts for all stores should be in a single additional table, using the Item's Key and the Store's Key as a combined Primary Key on that table.
For Example
Items
ItemKey
Name
Price
SKU
Stores
StoreKey
Name
Address
Inventory
StoreKey
ItemKey
Quantity
Using the StoreKey and ItemKey together on the Inventory table will keep your records unique.
This will allow you to not only easily lookup the single item but also lookup such things as:
Quantity of all items at one store by the StoreKey
Quantity of one item at all stores by the ItemKey
What items are low at all stores by Quantity
etc