MySQL database structure for a webshop
I am building an online shop and I have a problem. I will have products which have a direct price (for example HTC Touch 2 Smartphone: $299.00 ), but in the same time I will have products which have prices for combinations based on specifications:
In this image you can see the database diagram, which I think would be ok for the multiple-price products:
THE MAIN PROBLEM:
Since this is a webshop, people will put items in the shopping cart. I think the items inserted into the shopping cart should be from the same table (in our case it would be the [combinations] table, since there are the prices stored).
Here are some data for these tables, just to be more clear:
[products]
productid | productName
1 | Nike T-Shirt
2 | HTC Touch 2 Smartphone
[specifications]
specId | productId | specName
1 | 1 | Size
2 | 1 | Color
[specvalues]
specValueId | specId | svValue
1 | 1 | L
2 | 1 | XL
3 | 2 | white
4 | 2 | blue
5 | 2 | red
[combinations] (items into the cart)
combinationId | price | description
1 | 10 | White L Nike T-Shirt
2 | 15 | White XL Nike T-Shirt
3 | 11 | Blue L Nike T-Shirt
4 | 16 | Blue XL Nike T-Shirt
5 | 18 | Red XL Nike T-Shirt
[combinationParts]
nmid | combinationId | specValueId
1 | 1 | 1
2 | 1 | 3
3 | 2 | 2
4 | 2 | 3
5 | 3 | 1
1 | 3 | 4
2 | 4 | 2
3 | 4 | 4
4 | 5 | 2
5 | 5 | 5
I hope my diagram and database population does make sense :) .
So the question is how can I store the single price products (HTC Touch 2 Smartphone) so it can be added to shopping cart just like the multiple price products.
You might want to take a look at OpenCarts database.. It actually does a good job of this...
Essentially:
Create a tables for: Products Product Options (your 'spcifications') Product Option Values (your 'specvalues')
Each product would be listed in the 'Product Table' and have a price
In the 'Product Options' table, you essentially list the different 'specifications' to a product...
The Product Option Values table lists the actual options and the change to the base price (+/-)...
To store the completed orders, OpenCart has essentially the same tables... (with order id associated)
Recetly I ripped out the cart feature to handle player registrations for a tournament...
The base 'product' is a player registration - $25 The 'product_option' Simply lists the 'specification' / "registration type"
For the 'Registration Type' the values are stored in the 'product_option_value' table... They can either register as pay or pay & play (pay & play enters into minigames) The pay is just the default option with no change to the price... Pay and Play adds $15 to the price ($40 total)
I could easily add multiple "Product_options" and "product_option_value" sets to the product... Each either adding or subtracting from the products running total...
As for the script side, it just takes a couple loops to query and build the array with products array with product option as an sub array of products, and product option values as a sub array of each product option array
The 'product_option_value' table
--
-- Table structure for table `product`
--
CREATE TABLE IF NOT EXISTS `product` (
`product_id` int(11) NOT NULL auto_increment,
`site_id` int(11) NOT NULL,
`name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
`description` text character set utf8 collate utf8_unicode_ci NOT NULL,
`price` decimal(15,2) NOT NULL default '0.00',
`date_available` date NOT NULL,
`date_unavailable` date NOT NULL,
`status` int(1) NOT NULL default '0',
`date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`product_id`, `site_id`, `name`, `description`, `price`, `date_available`, `date_unavailable`, `status`, `date_added`, `date_modified`) VALUES
(1, 2, 'Player Registration', 'This year we have two options: Pay or Pay & Play.<br />Pay & Play allows you to enroll in the Flights Minigames for the weekend (Master''s Marks and Flights Doubles) and gives you twenty dollars worth of prize raffles. <br />Pay & Play is a $60.00 value and is only avalible during pre-registration.', 25.00, '2011-03-01', '2011-03-31', 1, '2011-03-01 00:00:00', '2011-03-01 00:00:00');
-- --------------------------------------------------------
--
-- Table structure for table `product_option`
--
CREATE TABLE IF NOT EXISTS `product_option` (
`product_option_id` int(11) NOT NULL auto_increment,
`product_id` int(11) NOT NULL,
`sort_order` int(3) NOT NULL default '0',
`name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`product_option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
--
-- Dumping data for table `product_option`
--
INSERT INTO `product_option` (`product_option_id`, `product_id`, `sort_order`, `name`) VALUES
(1, 1, 1, 'Registration Type');
-- --------------------------------------------------------
--
-- Table structure for table `product_option_value`
--
CREATE TABLE IF NOT EXISTS `product_option_value` (
`product_option_value_id` int(11) NOT NULL auto_increment,
`product_option_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`price` decimal(15,2) NOT NULL,
`prefix` char(1) collate utf8_bin NOT NULL,
`sort_order` int(3) NOT NULL,
`name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`product_option_value_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;
--
-- Dumping data for table `product_option_value`
--
INSERT INTO `product_option_value` (`product_option_value_id`, `product_option_id`, `product_id`, `price`, `prefix`, `sort_order`, `name`) VALUES
(1, 1, 1, 15.00, '+', 1, 'Pay & Play'),
(2, 1, 1, 0.00, '', 2, 'Pay');