mysql 电子商务网站设计
时间: 2023-11-21 20:57:05 浏览: 102
MySQL数据库在电子商务网站设计中扮演着重要的角色。以下是一个简单的MySQL电子商务网站设计:
1. 商品分类表
```sql
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
2. 商品表
```sql
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text,
`price` decimal(10,2) NOT NULL,
`category_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`),
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
3. 商品规格表
```sql
CREATE TABLE `product_spec` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `product_spec_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
4. 商品规格项表
```sql
CREATE TABLE `product_spec_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`spec_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `spec_id` (`spec_id`),
CONSTRAINT `product_spec_item_ibfk_1` FOREIGN KEY (`spec_id`) REFERENCES `product_spec` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
5. 商品规格组合表
```sql
CREATE TABLE `product_spec_combination` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `product_spec_combination_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
6. 商品属性表
```sql
CREATE TABLE `product_attribute` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `product_attribute_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
7. 商品筛选表
```sql
CREATE TABLE `product_filter` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```
8. 商品筛选值表
```sql
CREATE TABLE `product_filter_value` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`filter_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `filter_id` (`filter_id`),
CONSTRAINT `product_filter_value_ibfk_1` FOREIGN KEY (`filter_id`) REFERENCES `product_filter` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
```