· 7 years ago · Sep 05, 2018, 12:24 AM
1MySQL database structure for a webshop
2productid | productName
31 | Nike T-Shirt
42 | HTC Touch 2 Smartphone
5
6specId | productId | specName
71 | 1 | Size
82 | 1 | Color
9
10specValueId | specId | svValue
111 | 1 | L
122 | 1 | XL
133 | 2 | white
144 | 2 | blue
155 | 2 | red
16
17combinationId | price | description
181 | 10 | White L Nike T-Shirt
192 | 15 | White XL Nike T-Shirt
203 | 11 | Blue L Nike T-Shirt
214 | 16 | Blue XL Nike T-Shirt
225 | 18 | Red XL Nike T-Shirt
23
24nmid | combinationId | specValueId
251 | 1 | 1
262 | 1 | 3
273 | 2 | 2
284 | 2 | 3
295 | 3 | 1
301 | 3 | 4
312 | 4 | 2
323 | 4 | 4
334 | 5 | 2
345 | 5 | 5
35
36---------------+-------------+------------------------
37combinationId | price | description
38---------------+-------------+------------------------
396 | 299 | Base Model
40---------------+-------------+------------------------
41
42---------------+-------------+------------------------
43combinationId | price | description
44---------------+-------------+------------------------
456 | 299 | Base Model
467 | 349 | Exclusive Edition
47---------------+-------------+------------------------
48
49--
50-- Table structure for table `product`
51--
52
53CREATE TABLE IF NOT EXISTS `product` (
54 `product_id` int(11) NOT NULL auto_increment,
55 `site_id` int(11) NOT NULL,
56 `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
57 `description` text character set utf8 collate utf8_unicode_ci NOT NULL,
58 `price` decimal(15,2) NOT NULL default '0.00',
59 `date_available` date NOT NULL,
60 `date_unavailable` date NOT NULL,
61 `status` int(1) NOT NULL default '0',
62 `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
63 `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
64 PRIMARY KEY (`product_id`)
65) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
66
67--
68-- Dumping data for table `product`
69--
70
71INSERT INTO `product` (`product_id`, `site_id`, `name`, `description`, `price`, `date_available`, `date_unavailable`, `status`, `date_added`, `date_modified`) VALUES
72(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');
73
74-- --------------------------------------------------------
75
76--
77-- Table structure for table `product_option`
78--
79
80CREATE TABLE IF NOT EXISTS `product_option` (
81 `product_option_id` int(11) NOT NULL auto_increment,
82 `product_id` int(11) NOT NULL,
83 `sort_order` int(3) NOT NULL default '0',
84 `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
85 PRIMARY KEY (`product_option_id`)
86) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
87
88--
89-- Dumping data for table `product_option`
90--
91
92INSERT INTO `product_option` (`product_option_id`, `product_id`, `sort_order`, `name`) VALUES
93(1, 1, 1, 'Registration Type');
94
95-- --------------------------------------------------------
96
97--
98-- Table structure for table `product_option_value`
99--
100
101CREATE TABLE IF NOT EXISTS `product_option_value` (
102 `product_option_value_id` int(11) NOT NULL auto_increment,
103 `product_option_id` int(11) NOT NULL,
104 `product_id` int(11) NOT NULL,
105 `price` decimal(15,2) NOT NULL,
106 `prefix` char(1) collate utf8_bin NOT NULL,
107 `sort_order` int(3) NOT NULL,
108 `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
109 PRIMARY KEY (`product_option_value_id`)
110) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;
111
112--
113-- Dumping data for table `product_option_value`
114--
115
116INSERT INTO `product_option_value` (`product_option_value_id`, `product_option_id`, `product_id`, `price`, `prefix`, `sort_order`, `name`) VALUES
117(1, 1, 1, 15.00, '+', 1, 'Pay & Play'),
118(2, 1, 1, 0.00, '', 2, 'Pay');