设计一个数据库来处理具有产品变化的库存跟踪 [英] Designing a database that handles inventory tracking with product variations

问题描述

我正在为一个处理商店库存管理的项目设计一个数据库.

I am designing a database for a project that handles inventory management for shops.

基本上，每个Product都可以具有变化Color和Size，并且显然，每个可能变化的产品(存货)数量都将变化.

Basically, each Product can have the variations Color, and Size, and obviously, the number of products (stock) of each possible variation will vary.

示例:

[
    {
        "product": "Plain T-Shirt",
        "color": "Red",
        "size": "Medium",
        "stock": 10,
    },
    {
        "product": "Plain T-Shirt",
        "color": "Red",
        "size": "Large",
        "stock": 2,
    },

]

在这里，同一产品(纯色T恤)具有不同库存的不同款式.如您所料，我需要能够独立更新每只股票.

Here, the same product (Plain T-Shirt) has different stocks for different variations. As you can probably imagine, I need to be able to update each stock independently.

那么，设计这种数据库的最有效方法是什么?我使用的是PostgreSQL，因此设计必须是关系型的，但我也可以访问JSON字段.

So, what would be the most efficient way to design such a database? I am using PostgreSQL, so the design needs to be relational, but I also have access to JSON fields.

现在，我当前的设计看起来像这样:

Right now, my current design looks sort of like this:

产品

• 名称(字符"字段)
• 数量(JSON字段)

颜色

• 名称(字符"字段)
• 产品(多对一关系的外键)

大小

• 名称(字符"字段)
• 产品(多对一关系的外键)

颜色和尺寸可以由用户动态添加，因此系统必须对此进行补偿.

The colors, and sizes can be dynamically added by the user, so the system has to compensate for that.

关于数量"字段，假设产品P1的颜色为红色"和绿色"，尺寸为"S"和"M".我正在尝试使数量"字段如下所示:所有可能组合的4个键:(红色，S)，(红色，M)，(绿色，S)，(绿色，M)，其值代表股票的那些变化当前有库存.

Regarding the Quantities field, lets say a product P1 has the colors 'Red', and 'Green', and the sizes 'S', and 'M'. I am trying to make the Quantities field something like this: 4 keys of all the possible combinations: (Red, S), (Red, M), (Green, S), (Green, M) with values representing the stock those variations currently have in inventory.

所以，我的问题是:我走的路正确吗?这种设计有效吗?或者有更好的方法来做到这一点?谢谢.

So, my question is this: Am I on the right path? Is this design effective or is there a better way to do this? Thanks.

推荐答案

我在您的设计中看到的问题是，对于每个颜色为红色"的产品，您在颜色表中都有一个红色"的记录.与尺寸相同.然后使用JSON定义数量会产生很多不一致的机会(例如，如果您从颜色表中删除一种颜色，然后又忘记删除数量，那不是很相关").

The problem I see in your design is that for every product that is colored 'Red' you'd have a record for 'Red' in the colors table. Same with the sizes. And then using JSON to define the quantities would create a lot of opportunities for inconsistencies (e.g. if you delete a color from the colors table and then you forget to delete the quantity, it's not very 'relationy').

这是我的处理方式:使用长期关系.

Here's how I would do it: Using a trenary relationship.

您的表格如下所示:

PRODUCTS(id,name,...)
COLORS(id,name,...)
SIZES(id,size_label,...)
STOCKS(id,product_id,color_id,size_id,quantity)

STOCKS表表示三级关系.

The STOCKS table represents the trenary relationship.

通过这种方法，您可以将各个部分分开，并且更容易使用单个查询来跟踪库存.

This way you can keep things separate enough and it's easier to keep track of your stock using a single query to do it.

这篇关于设计一个数据库来处理具有产品变化的库存跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！