I'm currently working on wholesale online t-shirt shop. I have done this for fixed quantity and price, and its working fine. Now i need to do this for variable quantity and price.
I am trying to base my design on this reference site.
Basic tables I have created are:
CREATE TABLE attribute (
attribute_id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL,
PRIMARY KEY (attribute_id)
);
CREATE TABLE attribute_value (
attribute_value_id int(11) NOT NULL auto_increment,
attribute_id int(11) NOT NULL,
value varchar(100) NOT NULL,
PRIMARY KEY (attribute_value_id),
KEY idx_attribute_value_attribute_id (attribute_id)
);
CREATE TABLE product (
product_id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL,
description varchar(1000) NOT NULL,
price decimal(10,开发者_如何转开发2) NOT NULL,
image varchar(150) default NULL,
thumbnail varchar(150) default NULL,
PRIMARY KEY (product_id),
FULLTEXT KEY idx_ft_product_name_description (name,description)
);
CREATE TABLE product_attribute (
product_id int(11) NOT NULL,
attribute_value_id int(11) NOT NULL,
PRIMARY KEY (product_id,attribute_value_id)
);
I'm not getting, how to store the price based on a variable quantity. e.g -
Quantity Price 1-9 £1.91 10-99 £1.64 100-499 £1.10 500+ £1.14
Please help me to create product and its related tables. My requirement is same as above reference link.
Get a copy of the Data Model Resource Book, volume 1 - 3 and look up well designed data schemata. Simply as needed (they are powerfull). Pricing in a store is not THAT simple - seriously.
Simply create a table that matches the pricing matrix shown on the site.
CREATE TABLE product_qty_prices (
id INTEGER NOT NULL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES product,
low_qty INTEGER NOT NULL
high_qty INTEGER NOT NULL
price DECIMAL(10,2) NOT NULL )
You can also choose to omit the high_qty column and derive that in your application by looking at the low_qty from the next row, but I generally find it worth the risk of getting overlapping or incorrect ranges with this design.
Your design is a bit confused. Do you want a generic Entity-Attribute-Value data model or a domain-specific model? Modelling your specific domain directly is easier than trying to wrangle an abstract EAV implementation.
If you want to look at a sample implementation, Barry Williams's Library of Free Data Model has a good e-commerce example. Find out more.
精彩评论