数据库中的库存架构
这不是一个与特定语言相关的问题，而是一个处理库存的架构的正确方法的问题。
考虑以下用于存储软件的结构：
platforms (platformID*, platformName)
titles (titleID*, titleName)
以下是独特产品的连接表
products (platformID*, titleID*, releaseDate, notes)
以下是我想要跟踪我的库存的表
inventory (platformID*, titleID*, quantityAvailable)
在我见过的示例中，其他人创建了一个表，其中每个独特的副本软件存储在单独的行中，如下所示：
software(softwareID*, softwareTitle)
inventory(inventoryID*, softwareID*)
我想知道哪种方法更好？我是否应该为每个唯一的软件副本创建一个 inventoryID 并创建一行：
inventory(inventoryID*, platformID*(fk), titleID*(fk))
或者使用存储数量的表，如下所示：
inventory(platformID*(fk), titleID*(fk), quantityAvailable)
This is not a question related to a specific language, rather on the correct methodology of architectural of handling inventory.
Consider the following structure for storing software:
platforms (platformID*, platformName)
titles (titleID*, titleName)
And the following is a joiner table for unique products
products (platformID*, titleID*, releaseDate, notes)
And the following is a table I would like to keep track of my inventory
inventory (platformID*, titleID*, quantityAvailable)
In examples I have seen, others have created a table where each unique copy of a software is stored in a separate line as such:
software(softwareID*, softwareTitle)
inventory(inventoryID*, softwareID*)
I would like to know which approach is preferable? Should I create an inventoryID and thus a row for each unique software copy:
inventory(inventoryID*, platformID*(fk), titleID*(fk))
Or use a table that stores quantity instead, as such:
inventory(platformID*(fk), titleID*(fk), quantityAvailable)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
为每件库存拥有唯一行的优点是，以后如果您想跟踪诸如停留的库存、预购的库存、已售出但仍可以退货的库存等信息。
我不这样做看到这种方法有什么真正的缺点，除了它可能需要更多的工作，如果不需要这些东西，可能不会得到回报。
The advantage of having a unique row for each piece of inventory is that later if you want to keep track of things like inventory that's on layover, inventory that's on preorder, inventory that's been sold but could still be returned, etc.
I don't see any real disadvantage to this approach except that it's probably more work which might not pay off if these things aren't really needed.
我还会从 amountAvailable 开始，而不是从所有项目的行开始。 但是我仍然会选择 inventoryId，因为可能会出现这样的情况：您必须使用相同的平台/标题组合来剖析条目 - 使用 inventoryId 您将来会更容易增强。
我还建议添加一列：versionNo——软件产品的版本号。有时您可能有同一产品的不同版本。当您有此信息时，将信息放入标题中并不是一个好主意（例如您想要搜索所有“Microsoft Office”产品，无论版本如何......）。
I also would start with quantityAvailable instead of lines for all the items. But I would still opt for an inventoryId, since cases could occur, where you have to dissect the entries with the same platform/title combination -- with an inventoryId you are more enhanceable in the future.
I would also recommend to add a further column: versionNo --- the version number of a software product. Sometimes you might have differing versions of the same product. When you have this, it is not a good idea to drop the information into the title (for example you want to search for all "Microsoft Office" products regardless of version ...).
没有理由这样做，除非您想在每个唯一的软件副本上存储一些信息，例如每个副本的购买日期。这在软件清单中很少实用。
您还可以考虑在产品表中添加
quantityAvailable列，除非您认为最终您希望每个商品都有多个库存，以便能够分配以下数量的库存：特价商品、即将到期等。There is no reason to do this, unless you want to store some information on each unique software copy, such as the date each copy was purchased. This is rarely pracitical in the inventory of software.
You can also consider adding a
quantityAvailablecolumn in your products table, unless you think that eventually you'd want to have many inventories for each title, in order to be able to allocate a quantity of stock that is under special offer, that is soon going to expire, etc.