MySQL购物车结构 [英] MySQL Shopping Cart Structure

问题描述

我正在寻找一个能够处理多个类别的MySQL商店.我把所有的分类设施都整理好了，但是我没有得到的一点是这个.

I am looking to create a MySQL shop that is capable of handling multiple categories. I have all of the category facility etc sorted but the bit I am not getting anywhere with is this..

每个项目可以有多个选项，例如T恤应具有颜色"和尺寸"选项.然后，我需要从母产品创建许多变体/派生产品，以指定一个特大蓝色T恤有20个库存(例如).问题是，不仅仅是出售衣服，还可能有很多东西.因此，我还需要这种模式才能处理无数个变体，例如带有 Sports 的' 6mm ''大'生日贺卡>'设计. 6mm，大号，"Ace"为变量.这样，我可以确保我们没有任何库存控制问题.如果您有什么用，以下是我目前的网站结构.

Each item can have multiple options, for example a T-Shirt should have the options 'Colour' and 'Size'. I then need to create a number of variations/ derived products from the parent product specifying that an Extra Large Blue T-Shirt has 20 in stock (for example). The problem is, it's not just clothes being sold, it could be any number of things. So I also need this schema to be able to handle an infinite number of variants such as '6mm' 'Large' Birthday Card with 'Sports Car' design. 6mm, Large, and 'Ace' being the variables. This way I am able to ensure that we do not have any stock control issues. If it is any use to you, below is my current site structure.

现有数据库架构http://www.hallwaystudios.com/screenshots/uploads/g5B7SNKU.png

希望您能理解我的意思，并且有人可以回答我的问题！在此之前(以及课程结束之后)非常感谢

I hope you understand what I mean and that someone has an answer to my problem! Many thanks in advance (and after of-course)

推荐答案

不太清楚这里是什么问题...我可能会创建四个表:

Not too sure what the problem is here... I'd probably create four tables:

-- a table of item types (t-shirt, birthday card, etc.)
CREATE TABLE ItemTypes (
  TypeID      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  TypeName    VARCHAR(20) NOT NULL
);

-- a table of associated properties
CREATE TABLE TypeProperties (
  PropertyID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  TypeID      INT NOT NULL,
  PropName    VARCHAR(20) NOT NULL,
  INDEX(Property, TypeID),
  FOREIGN KEY(TypeID) REFERENCES ItemTypes(TypeID)
);

-- a table of specific items (XL Blue t-shirt, large bday card w/sports car, etc.)
CREATE TABLE Items (
  ItemID      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  TypeID      INT NOT NULL,
  ItemName    VARCHAR(100) NOT NULL,
  ItemPrice   DECIMAL UNSIGNED NOT NULL,
  ItemStock   INT UNSIGNED NOT NULL,
  INDEX(ItemID, TypeID),
  FOREIGN KEY(TypeID) REFERENCES ItemTypes(TypeID)
);

-- the dictionary of property values
CREATE TABLE ItemProperties (
  ItemID      INT NOT NULL,
  TypeID      INT NOT NULL,
  PropertyID  INT NOT NULL,
  Value       VARCHAR(20) NOT NULL,
  PRIMARY KEY(ItemID, Property),
  INDEX(ItemID,     TypeID),
  INDEX(PropertyID, TypeID),
  FOREIGN KEY(            TypeID) REFERENCES ItemTypes     (            TypeID),
  FOREIGN KEY(ItemID,     TypeID) REFERENCES Items         (ItemID,     TypeID),
  FOREIGN KEY(PropertyID, TypeID) REFERENCES TypeProperties(PropertyID, TypeID)
);

应该很明显，但是以防万一，示例数据看起来像:

It ought to be fairly obvious, but just in case, the example data would look something like:

INSERT INTO ItemTypes (TypeID, TypeName) VALUES
  (1, 'T-Shirt'      ),
  (2, 'Birthday Card'),
  (3, 'Balloon'      );

INSERT INTO TypeProperties(PropertyID, TypeID, PropName) VALUES
  (51, 1, 'Colour' ), (52, 1, 'Size'),
  (53, 2, 'Size/mm'), (54, 2, 'Size'), (55, 2, 'Design'),
  (56, 3, 'Colour' );

INSERT INTO Items (ItemID, TypeID, ItemName, ItemPrice, ItemStock) VALUES
  (101, 1, 'Extra Large Blue T-Shirt',                       10.99, 20),
  (102, 2, '6mm Large Birthday Card with Sports Car Design',  2.99, 17),
  (103, 1, 'Extra Large Black T-Shirt',                      10.99,  5),
  (104, 3, 'Pink balloon',                                    0.10, 60);

INSERT INTO ItemProperties (ItemID, TypeID, PropertyID, Value) VALUES
  (101, 1, 51, 'Blue'       ),
  (101, 1, 52, 'Extra Large'),
  (102, 2, 53, '6'          ),
  (102, 2, 54, 'Large'      ),
  (102, 2, 55, 'Sports Car' ),
  (103, 1, 51, 'Black'      ),
  (103, 1, 52, 'Extra Large'),
  (104, 3, 56, 'Pink'       );

这篇关于MySQL购物车结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！