产品分类数据库设计

产品分类数据库表设计文档

北京联动北方科技有限公司

2012年3月7日

系统数据库设计

(所作修改已用红色部分标出)

一．系统ER图

1-1分析

实体集：公司、产品、组件、产品类别

联系：

生产：描述公司和产品之间的关系，是一种多对多的关系。一个公司可以生产多种产品，一个产品也可能有多个公司生产。

组成：描述产品与组件之间的关系，是一种多对多的关系，一个产品由多个组件组成，一个组件也可能出现在多个产品中。

兼容：描述各个公司产品之间的兼容性，是一种多对多的关系，一个产品可以与多个产品兼容。

拥有：描述公司和产品类别之间的关系，是一种多对多的关系。一个公司可以拥有很多类别的产品，一个产品类别里面也可以包含很多公司的产品。

包含：描述产品类别和产品之间的关系，是一对多的关系。一个产品类别里面可以包含多个产品，但一个产品只能属于一个产品类别。

1-2处理

1. 公司这个实体可以独立作为一个关系，建立一张表：公司（公司编号，名称，描述）

2. 产品这个实体可以独立作为一个关系，建立一张表：（产品编号，名称，类别，版本，描述）

3. 组件这个实体可以作为一个独立的关系，建立一张组件表：（组件编号，名称，描述）

4. 生产联系转变成一个关系：生产表，记录不同公司生产的不同产品。生产表:生产（公司编号，产品编号）

5. 组成联系转变成一个关系：组成表，记录产品和组件之间的关系。产品组成表：组成（组件编号，产品编号）

6. 兼容联系转变成一个关系，是产品到产品的一个自身映射问题，记录产品与产品之间的兼容性信息。兼容表：兼容（产品1编号，产品2编号）

7.产品类别这个实体可以独立作为一个关系，建立一张表：产品列表（编号，名称，描述）

8.拥有联系转变成一个关系：拥有表,记录公司和产品类别之间的关系。拥有表：（公司编号，产品类别编号）

9.包含联系转变成一个关系，是产品类别和产品之间的关系。建立一张表：包含表（产品类别编号，产品编号）

二、数据表：

1.公司表（company）

主要用来记录公司信息。

字段字段名称数据类型可否为空备注

compa_id公司编号varchar（10）不允许为空主键

compa_name公司名称varchar（50）不允许为空

compa_desc公司描述varchar（2000）允许为空

create table company(compa_id varchar(10) not null,

compa_name varchar(50) not null,

compa_desc varchar(2000),

PRIMARY KEY(compa_id))

2.产品表（product）

主要用来存储产品信息，方便用户查询。

字段字段名称数据类型可否为空备注

prod_id 产品编号varchar(10)不允许为空主键

prod_name 产品名称varchar(50)不允许为空

prod_desc 产品描述varchar(2000)允许为空

prod_vers 产品版本varchar(10) 不允许为空

create table product(prod_id varchar(10) not null,

prod_name varchar(50) not null,

prod_desc varchar(2000),

prod_vers varchar(10) not null,

PRIMARY KEY(prod_id))

3.组件表（component）

主要用来存放组件信息。

字段字段名称数据类型可否为空备注

compo_id 组件编号varchar(10) 不允许为空主键

compo_name 组件名称varchar(50) 不允许为空

compo_desc 组件描述varchar(2000) 允许为空

create table component(compo_id varchar(10) not null,

compo_name varchar(50) not null,

compo_desc varchar(10),

PRIMARY KEY(compo_id))

4.生产表（production）

主要用来存放不同公司生产不同产品信息。

字段字段名称数据类型可否为空备注compa_id 公司编号varchar(10) 不允许为空外键（作为主键）prod_id 产品编号varchar(10) 不允许为空外键（作为主键）create table production(compa_id varchar(10) not null,

prod_id varchar(10)not null,

PRIMARY KEY(compa_id,prod_id),

FOREIGN KEY(compa_id) REFERENCES company (compa_id),

FOREIGN KEY(prod_id) REFERENCES product (prod_id))

5.组成表(makeup)

主要用来存放组件和产品之间的组成信息。

字段字段名称数据类型可否为空备注

prod_id 产品编号varchar(10) 不允许为空外键（作为主键）compo_id 组件编号varchar(10) 不允许为空外键（作为主键）create table makeup (prod_id varchar(10) not null,

compo_id varchar(10)not null,

PRIMARY KEY(prod_id,compo_id),

FOREIGN KEY(prod_id) REFERENCES product (prod_id),

FOREIGN KEY(compo_id) REFERENCES component (compo_id))

6.版本兼容表（compatibility）

主要用来存储各种产品之间的兼容性信息。

字段字段名称数据类型可否为空备注

prod1_id 产品1编号varchar(10) 不允许为空外键（作为主键）prod2_id 产品2编号varchar(10) 不允许为空外键（作为主键）

create table compatibility (prod1_id varchar(10) not null,

prod2_id varchar(10)not null,

PRIMARY KEY(prod1_id,prod2_id),

FOREIGN KEY(prod1_id) REFERENCES product (prod_id),

FOREIGN KEY(prod2_id) REFERENCES product (prod_id))

7.产品类别表（category）

主要用来存储产品类别信息。

字段字段名称数据类型是否为空备注

categ_id 产品类别编号varchar(10) 不允许为空主键

categ_name 产品类别名称varchr(50) 不允许为空

categ_desc 产品类别描述varchar(2000) 允许为空

create table category (categ_id varchar(10) not null,

Categ_name varchar(50) not null,

Categ_desc varchar(2000),

PRIMARY KEY(cate_id)

)

8.拥有表（have）

用来存储公司和产品类别之间的关系信息。

字段字段名称数据类型是否为空备注

compa_id 公司编号varchar（10）不允许为空外键(作为主键) categ_id 产品类别编号varchar（10）不允许为空外键(作为主键) create table have(compa_id varchar(10),

categ_id varchar(10),

FOREIGN KEY(compa_id) REFERENCES company (compa_id),

FOREIGN KEY(categ_id) REFERENCES company (categ_id),

)

9.包含表（contain）

用来存储产品类别和产品之间的关系信息。

字段字段名称数据类型是否为空备注

categ_id 产品类别编号varchar（10）不允许为空外键(作为主键) prod_id 产品编号varchar（10）不允许为空外键(作为主键) create table contain(categ_id varchar(10),

prod_id varchar(10),

FOREIGN KEY(categ_id) REFERENCES category (categ_id),

FOREIGN KEY (prod_id) REFERENCES product(prod_id)

)

在对版本兼容表进行插入操作时，要防止两个产品的兼容信息重复存储，使用触发器来防止信息重复。

CREATE OR REPLACE TRIGGER compatibility_check_trig

before insert on compatibility

REFERENCING OLD as old_compare_id NEW as new_compare_id

FOR EACH ROW

BEGIN

IF :old_compare_id.prod1_id==:new_compare_id.prod2_id THEN IF :old_compare_id.prod2_id==:new_compare_id.prod1_id THEN UPDATE compatibility

SET prod1_id=:new_compare_id.prod1_id,

prod2_id=:new_compare_id.prod2_id

WHERE prod1_id=:old_compare_id.prod1_id

AND prod2_id=:old_compare_id.prod2_id;

commit;

END IF;

END IF;

END

考虑到一旦出现收购现象，被收购的公司编号要改为收购公司的编号，涉及到公司编号的两个表production和have的相应列也要随之更改,使用触发器实现：

create or replace trigger company_update

after update of compa_id on company

REFERENCING OLD as old_comp NEW as new_comp

FOR EACH ROW

BEGIN

IF :old_https://www.360docs.net/doc/a14504207.html,pa_id!=new_https://www.360docs.net/doc/a14504207.html,pa_id THEN

UPDATE production

SET compa_id:=new_https://www.360docs.net/doc/a14504207.html,pa_id;

UPDATE have

SET compa_id:=new_https://www.360docs.net/doc/a14504207.html,pa_id;

COMMIT;

END IF

END