10.1.2 数据库设计
本系统的E/R图如图10.2所示。
本系统的数据库系统使用MySQL建立,包含5张数据表,分别用于存放E/R图中的5个实体。
auction_user表用于存放系统的注册用户信息,其表结构如下所示。
1
2
3
4
5
6
7
8
9
10
| mysql> desc auction_user;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | UNI | NULL | |
| userpass | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set
|
kind表用于存放物品种类,其表结构如下所示:
1
2
3
4
5
6
7
8
9
| mysql> desc kind;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| kind_id | int(11) | NO | PRI | NULL | auto_increment |
| kind_name | varchar(50) | NO | | NULL | |
| kind_desc | varchar(255) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set
|
item表用于存放物品,其表结构如下所示。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| mysql> desc item;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| item_id | int(11) | NO | PRI | NULL | auto_increment |
| item_name | varchar(255) | NO | | NULL | |
| item_remark | varchar(255) | YES | | NULL | |
| item_desc | varchar(255) | YES | | NULL | |
| kind_id | int(11) | NO | MUL | NULL | |
| addtime | date | NO | | NULL | |
| endtime | date | NO | | NULL | |
| init_price | double | NO | | NULL | |
| max_price | double | NO | | NULL | |
| owner_id | int(11) | NO | MUL | NULL | |
| winer_id | int(11) | YES | MUL | NULL | |
| state_id | int(11) | NO | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+
12 rows in set
|
state表用于存放拍卖物品的状态,其表结构如下所示:
1
2
3
4
5
6
7
8
| mysql> desc state;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| state_id | int(11) | NO | PRI | NULL | auto_increment |
| state_name | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
2 rows in set
|
bid表用于存放竞价记录,其表结构如下所示:
1
2
3
4
5
6
7
8
9
10
11
| mysql> desc bid;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| bid_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| item_id | int(11) | NO | MUL | NULL | |
| bid_price | double | NO | | NULL | |
| bid_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+----------------+
5 rows in set
|
原文链接: 10.1.2 数据库设计