Need help to create database schema for wholesale online tee store

Question

Hi, 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.

Answer 1

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.

Answer 2

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.

Answer 3

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.