mysql购物车_MySQL购物车结构

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

-- 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)

);

它应该是相当明显的,但为了以防万一,示例数据看起来像：

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' );