产品型号的数据库结构 [英] Database structure for product variants

问题描述

我正在升级网站以启用产品变体-当前该网站仅提供独立产品，但是现在需要提供特定产品的变体，例如大小或颜色.目的是使站点管理员能够轻松地插入/编辑产品变体.

I am upgrading a website to enable product variants - currently the website only provides standalone products but there is now a requirement to provide variants of a particular product, e.g. size or colour. The aim is to enable the site admin to easily insert/edit product variants.

当前结构如下:

table product
=============
id
name
description
category_id
price
stock_level

价格"和库存水平"字段现在需要与每种产品变型相关.

The fields 'price' and 'stock_level' will now need to be relevant to each product variant.

一个产品可以具有多种变体组合，例如:

A product can have multiple combinations of variants, e.g:

1. 产品ID 5-尺寸:小，颜色:黑色
2. 产品ID 5-尺寸:小，颜色:棕色

在前端，有两个下拉菜单用于选择变体(大小和颜色).选择所需的变体后，这些值将发布到PHP脚本，该脚本运行SQL查询以检查特定的变体组合是否可用.

On the front end there are two dropdowns to select the variants (Size and Colour). Upon selecting the required variants, the values are posted to a PHP script which runs an SQL query to check if that particular variant combination is available.

我正在努力为此提出解决方案.我目前已经创建了以下功能，我认为这是起点:

I am struggling to come up with a solution for this. I have currently created the following functionality, which I think is the starting point:

1. 能够创建/编辑变体类型，例如大小或颜色:

1. Ability to create/edit variant TYPES e.g. Size or Colour:

table variant_type
==================
id
name

能够将值分配给变量类型，例如小，大，黑色，棕色:

Ability to assign values to variant types, e.g. Small, Large, Black, Brown:

table variant_type_value
========================
id
name
variant_type_id

我正在努力设计用于存储产品变体组合(包括它们的价格和库存水平)的表的设计.

I am struggling to come up with the design for the table(s) that will store the product variant combinations (including their price and stock level).

请记住，在后端，将有一个添加新变体"的表单-管理员需要在此表单上选择大小"，颜色"，价格"和库存水平"添加/编辑变体.

Bear in mind, on the backend, there will be a form to "Add a new Variant" - on this form the admin will need to select 'Size', 'Colour', 'Price' and 'Stock Level' when adding/editing a variant.

推荐答案

我认为最简单的方法是拥有一个Product表.通过包括Product表的外键以及Size和Color表，其中将包含变体的所有详细信息:

I think the easiest way would be to have a Product table; that would have all the details of the variants in it, by including the foreign keys for the Product table, as well as the Size and Colour tables:

table variant
=============
variantID
productID
sizeID
colourID
stock
price

table product 
============= 
id 
name 
description 
category_id 

table size
==========
sizeID
sizeName

table colour
============
colourID
colourName

因此，您可以通过将所有四个表连接在一起来获取变量的详细信息.通常，与产品相关的信息位于product表中，您可以通过创建新表并将其链接到variant表中来添加其他类型的变体.

So you can get the details for the variants by joining all four tables together. Information that relates to the product in general goes in the product table, and you can add extra types of variant by creating new tables and linking them in the variant table.

编辑后添加:

这样，如果要添加新类型的变体，则需要添加额外的表.您还可以通过将所有变体可能性合并到一个变体表中来解决此问题:

This way, you'll need to add extra tables if you want to add a new type of variant. You could also get around it by merging all the variant possibilities into one variant table:

+--+------+------+
|ID|Type  |Option|
+--+------+------+
|1 |Colour|Brown |
|2 |Size  |Small |
+--+------+------+

然后您将在主产品表中具有多个来自variantInfo的外键.

You'd then have multiple foreign keys from variantInfo in the main product table.

我不喜欢这样-我不喜欢在同一张表中存储多种类型的信息.我只是认为这很混乱，您需要使编程逻辑更加复杂.如果您想拥有更多的变体类型，建议您现在就将它们全部设置-这是一项额外的工作，并且其中一些将不会使用，但是我认为它更容易维护.

I don't tend to like that - I don't like have multiple types of information stored in the same table. I just think it's confusing, and you need to make the programming logic more complicated. If you want to have extra variant types, I'd recommend just setting them all up now - it's a little extra work, and some of them won't be used, but I think it's a lot easier to maintain.

这篇关于产品型号的数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！