300字范文 > MySQL设计工厂管理数据库（Ⅰ）—表结构设计

MySQL设计工厂管理数据库（Ⅰ）—表结构设计

时间：2022-02-18 17:50:31

引言设计思路工厂管理E-R图设计工厂管理逻辑图实现过程项目（project）表实现职工（staff）表设计零件（components）表设计供应商（supplier）表设计仓库（storehouse）表设计实现效果小结参考

引言

学习MyQL第5天，主要在学习其的基础的操作知识，想通过实例练习巩固所学，遂成此文。

设计思路

工厂管理E-R图

由图易知:该数据库主要包括五个主体：

1、项目（project）

2、供应商（supplier）

3、仓库（storehouse）

4、零件（components）

5、职工（staff）

设计工厂管理逻辑图

根据E-R图要求，笔者尝试设计的逻辑图如下图所示：

实现过程

实现工具为MySQL，编辑工具为Navicat for MySQL

项目（project）表实现

CREATE TABLE `project` (`Supplier_supply` int(10) NOT NULL COMMENT '供应量',`project_ID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目号',`budget` int(10) NOT NULL COMMENT '预算',`start_date` datetime(0) NOT NULL COMMENT '开工日期',PRIMARY KEY (`project_ID`) USING BTREE,INDEX `supply`(`Supplier_supply`) USING BTREE,CONSTRAINT `Supplier_supply` FOREIGN KEY (`Supplier_supply`) REFERENCES `supplier` (`supply`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

职工（staff）表设计

CREATE TABLE `staff` (`staff_ID` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职工号',`staff_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职工姓名',`age` int(3) NOT NULL COMMENT '年龄',`job_title` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职称',`Whether_to_lead` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '是否领导',`storehouse_ID` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所在仓库',PRIMARY KEY (`staff_ID`) USING BTREE,INDEX `storehouse_ID`(`storehouse_ID`) USING BTREE,CONSTRAINT `storehouse_ID` FOREIGN KEY (`storehouse_ID`) REFERENCES `storehouse` (`storehouse_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

零件（components）表设计

CREATE TABLE `components` (`component_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '零件号',`comonent_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '零件名称',`specification` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '规格',`unit_price` decimal(10, 2) NOT NULL COMMENT '单价',`inventory` int(25) NOT NULL COMMENT '库存量',`description` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述',PRIMARY KEY (`component_ID`, `inventory`) USING BTREE,INDEX `inventory`(`inventory`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

供应商（supplier）表设计

CREATE TABLE `supplier` (`supplier_ID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '供应商号',`name` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',`address` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '地址',`telephone_number` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '电话号码',`account_number` char(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账号',`supply` int(10) NOT NULL COMMENT '供应量',PRIMARY KEY (`supplier_ID`, `supply`) USING BTREE,INDEX `supply`(`supply`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

仓库（storehouse）表设计

CREATE TABLE `storehouse` (`storehouse_ID` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '仓库号',`area` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '面积',`s_telephone_number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话号码',`component_stock` int(10) NOT NULL COMMENT '零件库存',PRIMARY KEY (`storehouse_ID`) USING BTREE,INDEX `component_stock`(`component_stock`) USING BTREE,CONSTRAINT `component_stock` FOREIGN KEY (`component_stock`) REFERENCES `components` (`inventory`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;