设计一个汽车零件采购系统的数据库需要考虑多个方面，包括供应商信息、零件信息、采购订单、库存管理等。以下是一个基本的数据库设计，包括主要的表和字段，以及它们之间的关系。
1. 数据库表设计
1.1 供应商表（Suppliers）
存储供应商的基本信息。
- SupplierID (主键，自增)
- SupplierName (供应商名称)
- ContactName (联系人姓名)
- ContactPhone (联系电话)
- ContactEmail (联系邮箱)
- Address (地址)
- City (城市)
- State (州/省)
- PostalCode (邮编)
- Country (国家)
- Status (状态，如活跃、暂停等)
1.2 零件表（Parts）
存储零件的基本信息。
- PartID (主键，自增)
- PartName (零件名称)
- PartNumber (零件编号)
- Description (描述)
- UnitPrice (单价)
- MinStockLevel (最小库存水平)
- MaxStockLevel (最大库存水平)
- SupplierID (外键，关联供应商表)
1.3 采购订单表（PurchaseOrders）
存储采购订单的信息。
- OrderID (主键，自增)
- SupplierID (外键，关联供应商表)
- OrderDate (订单日期)
- ExpectedDeliveryDate (预期交货日期)
- Status (状态，如已下单、已发货、已完成等)
- TotalAmount (总金额)
1.4 采购订单明细表（PurchaseOrderDetails）
存储采购订单的详细信息。
- OrderDetailID (主键，自增)
- OrderID (外键，关联采购订单表)
- PartID (外键，关联零件表)
- Quantity (数量)
- UnitPrice (单价)
- TotalPrice (总价)
1.5 库存表（Inventory）
存储库存信息。
- InventoryID (主键，自增)
- PartID (外键，关联零件表)
- QuantityOnHand (当前库存数量)
- LastUpdated (最后更新时间)
1.6 采购申请表（PurchaseRequests）
存储采购申请的信息。
- RequestID (主键，自增)
- RequestDate (申请日期)
- Status (状态，如待审批、已批准、已拒绝等)
- TotalAmount (总金额)
1.7 采购申请明细表（PurchaseRequestDetails）
存储采购申请的详细信息。
- RequestDetailID (主键，自增)
- RequestID (外键，关联采购申请表)
- PartID (外键，关联零件表)
- Quantity (数量)
- UnitPrice (单价)
- TotalPrice (总价)
2. 数据库关系图
以下是这些表之间的关系：
- 供应商表（Suppliers）与零件表（Parts）是一对多关系。
- 供应商表（Suppliers）与采购订单表（PurchaseOrders）是一对多关系。
- 采购订单表（PurchaseOrders）与采购订单明细表（PurchaseOrderDetails）是一对多关系。
- 零件表（Parts）与采购订单明细表（PurchaseOrderDetails）是一对多关系。
- 零件表（Parts）与库存表（Inventory）是一对一关系。
- 采购申请表（PurchaseRequests）与采购申请明细表（PurchaseRequestDetails）是一对多关系。
- 零件表（Parts）与采购申请明细表（PurchaseRequestDetails）是一对多关系。
3. 数据库设计示例（SQL语句）
以下是创建这些表的SQL语句示例：
CREATE TABLE Suppliers (
SupplierID INT AUTO_INCREMENT PRIMARY KEY,
SupplierName VARCHAR(100) NOT NULL,
ContactName VARCHAR(100),
ContactPhone VARCHAR(20),
ContactEmail VARCHAR(100),
Address VARCHAR(255),
City VARCHAR(100),
State VARCHAR(100),
PostalCode VARCHAR(20),
Country VARCHAR(100),
Status VARCHAR(20)
);
CREATE TABLE Parts (
PartID INT AUTO_INCREMENT PRIMARY KEY,
PartName VARCHAR(100) NOT NULL,
PartNumber VARCHAR(50) NOT NULL,
Description TEXT,
UnitPrice DECIMAL(10, 2),
MinStockLevel INT,
MaxStockLevel INT,
SupplierID INT,
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);
CREATE TABLE PurchaseOrders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
SupplierID INT,
OrderDate DATE,
ExpectedDeliveryDate DATE,
Status VARCHAR(20),
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);
CREATE TABLE PurchaseOrderDetails (
OrderDetailID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
PartID INT,
Quantity INT,
UnitPrice DECIMAL(10, 2),
TotalPrice DECIMAL(10, 2),
FOREIGN KEY (OrderID) REFERENCES PurchaseOrders(OrderID),
FOREIGN KEY (PartID) REFERENCES Parts(PartID)
);
CREATE TABLE Inventory (
InventoryID INT AUTO_INCREMENT PRIMARY KEY,
PartID INT,
QuantityOnHand INT,
LastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (PartID) REFERENCES Parts(PartID)
);
CREATE TABLE PurchaseRequests (
RequestID INT AUTO_INCREMENT PRIMARY KEY,
RequestDate DATE,
Status VARCHAR(20),
TotalAmount DECIMAL(10, 2)
);
CREATE TABLE PurchaseRequestDetails (
RequestDetailID INT AUTO_INCREMENT PRIMARY KEY,
RequestID INT,
PartID INT,
Quantity INT,
UnitPrice DECIMAL(10, 2),
TotalPrice DECIMAL(10, 2),
FOREIGN KEY (RequestID) REFERENCES PurchaseRequests(RequestID),
FOREIGN KEY (PartID) REFERENCES Parts(PartID)
);
4. 数据库设计注意事项
- 数据完整性：确保所有外键关系正确，以维护数据完整性。
- 数据一致性：通过触发器或存储过程确保库存数量的更新与采购订单和采购申请的变动保持一致。
- 性能优化：为常用查询字段（如
PartNumber、SupplierName等）创建索引，以提高查询效率。 - 安全性：确保敏感信息（如联系人信息）的安全存储和访问控制。
通过以上设计，可以构建一个功能完善、高效且易于维护的汽车零件采购系统数据库。
以下是新能源汽车零件采购系统的数据库设计方案，包含主要实体和关系：
核心实体与表结构
1. 零件管理模块
-- 零件基本信息表
CREATE TABLE parts (
part_id INT PRIMARY KEY AUTO_INCREMENT,
part_code VARCHAR(50) UNIQUE NOT NULL COMMENT '零件编码',
part_name VARCHAR(100) NOT NULL COMMENT '零件名称',
part_type ENUM('电池', '电机', '电控', '底盘', '车身', '内饰', '其他') NOT NULL COMMENT '零件类型',
specification TEXT COMMENT '规格参数',
unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',
supplier_id INT NOT NULL COMMENT '供应商ID',
safety_stock INT DEFAULT 0 COMMENT '安全库存',
current_stock INT DEFAULT 0 COMMENT '当前库存',
is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);
-- 零件库存记录表
CREATE TABLE part_inventory_history (
record_id INT PRIMARY KEY AUTO_INCREMENT,
part_id INT NOT NULL COMMENT '零件ID',
operation_type ENUM('入库', '出库', '盘点') NOT NULL COMMENT '操作类型',
quantity INT NOT NULL COMMENT '数量',
operator VARCHAR(50) NOT NULL COMMENT '操作人',
operation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
remark TEXT COMMENT '备注',
FOREIGN KEY (part_id) REFERENCES parts(part_id)
);
2. 供应商管理模块
-- 供应商信息表
CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY AUTO_INCREMENT,
supplier_code VARCHAR(50) UNIQUE NOT NULL COMMENT '供应商编码',
supplier_name VARCHAR(100) NOT NULL COMMENT '供应商名称',
contact_person VARCHAR(50) COMMENT '联系人',
phone VARCHAR(20) COMMENT '联系电话',
email VARCHAR(50) COMMENT '邮箱',
address TEXT COMMENT '地址',
qualification_status ENUM('待审核', '已审核', '已拒绝') DEFAULT '待审核' COMMENT '资质状态',
credit_rating ENUM('A', 'B', 'C', 'D') DEFAULT 'B' COMMENT '信用等级',
is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 供应商资质文件表
CREATE TABLE supplier_qualifications (
qualification_id INT PRIMARY KEY AUTO_INCREMENT,
supplier_id INT NOT NULL COMMENT '供应商ID',
file_name VARCHAR(100) NOT NULL COMMENT '文件名',
file_path VARCHAR(255) NOT NULL COMMENT '文件路径',
file_type ENUM('营业执照', '生产许可证', '质量认证', '其他') NOT NULL COMMENT '文件类型',
valid_from DATE NOT NULL COMMENT '有效期开始',
valid_to DATE NOT NULL COMMENT '有效期结束',
is_valid TINYINT(1) DEFAULT 1 COMMENT '是否有效',
upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);
3. 采购流程模块
-- 采购申请表
CREATE TABLE purchase_requests (
request_id INT PRIMARY KEY AUTO_INCREMENT,
request_code VARCHAR(50) UNIQUE NOT NULL COMMENT '申请单号',
requester_id INT NOT NULL COMMENT '申请人ID',
department_id INT NOT NULL COMMENT '申请部门ID',
request_date DATETIME NOT NULL COMMENT '申请日期',
expected_delivery_date DATE COMMENT '期望交货日期',
status ENUM('待审批', '已审批', '已驳回', '已取消') DEFAULT '待审批' COMMENT '状态',
approval_by INT COMMENT '审批人ID',
approval_time DATETIME COMMENT '审批时间',
rejection_reason TEXT COMMENT '驳回原因',
remark TEXT COMMENT '备注',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (requester_id) REFERENCES users(user_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- 采购申请明细表
CREATE TABLE purchase_request_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
request_id INT NOT NULL COMMENT '申请单ID',
part_id INT NOT NULL COMMENT '零件ID',
quantity INT NOT NULL COMMENT '申请数量',
estimated_price DECIMAL(10, 2) COMMENT '预估价格',
FOREIGN KEY (request_id) REFERENCES purchase_requests(request_id),
FOREIGN KEY (part_id) REFERENCES parts(part_id)
);
-- 采购订单表
CREATE TABLE purchase_orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
order_code VARCHAR(50) UNIQUE NOT NULL COMMENT '订单号',
request_id INT NOT NULL COMMENT '关联申请单ID',
supplier_id INT NOT NULL COMMENT '供应商ID',
buyer_id INT NOT NULL COMMENT '采购人ID',
order_date DATETIME NOT NULL COMMENT '订单日期',
expected_delivery_date DATE COMMENT '期望交货日期',
actual_delivery_date DATE COMMENT '实际交货日期',
status ENUM('待确认', '已确认', '已发货', '已签收', '已完成', '已取消') DEFAULT '待确认' COMMENT '状态',
payment_status ENUM('未付款', '已付款', '部分付款') DEFAULT '未付款' COMMENT '付款状态',
total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总额',
remark TEXT COMMENT '备注',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (request_id) REFERENCES purchase_requests(request_id),
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
FOREIGN KEY (buyer_id) REFERENCES users(user_id)
);
-- 采购订单明细表
CREATE TABLE purchase_order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL COMMENT '订单ID',
part_id INT NOT NULL COMMENT '零件ID',
quantity INT NOT NULL COMMENT '采购数量',
unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',
subtotal DECIMAL(10, 2) NOT NULL COMMENT '小计',
received_quantity INT DEFAULT 0 COMMENT '已收货数量',
FOREIGN KEY (order_id) REFERENCES purchase_orders(order_id),
FOREIGN KEY (part_id) REFERENCES parts(part_id)
);
-- 收货记录表
CREATE TABLE goods_receipts (
receipt_id INT PRIMARY KEY AUTO_INCREMENT,
receipt_code VARCHAR(50) UNIQUE NOT NULL COMMENT '收货单号',
order_id INT NOT NULL COMMENT '关联订单ID',
receiver_id INT NOT NULL COMMENT '收货人ID',
receipt_date DATETIME NOT NULL COMMENT '收货日期',
status ENUM('部分收货', '全部收货', '已质检', '已入库') DEFAULT '部分收货' COMMENT '状态',
remark TEXT COMMENT '备注',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES purchase_orders(order_id),
FOREIGN KEY (receiver_id) REFERENCES users(user_id)
);
-- 收货明细记录表
CREATE TABLE goods_receipt_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
receipt_id INT NOT NULL COMMENT '收货单ID',
part_id INT NOT NULL COMMENT '零件ID',
received_quantity INT NOT NULL COMMENT '收货数量',
quality_status ENUM('合格', '不合格', '待检验') DEFAULT '待检验' COMMENT '质量状态',
FOREIGN KEY (receipt_id) REFERENCES goods_receipts(receipt_id),
FOREIGN KEY (part_id) REFERENCES parts(part_id)
);
4. 质量管理模块
-- 质检记录表
CREATE TABLE quality_inspections (
inspection_id INT PRIMARY KEY AUTO_INCREMENT,
receipt_item_id INT NOT NULL COMMENT '关联收货明细ID',
inspector_id INT NOT NULL COMMENT '质检员ID',
inspection_date DATETIME NOT NULL COMMENT '质检日期',
result ENUM('合格', '不合格', '返工') NOT NULL COMMENT '质检结果',
defective_quantity INT DEFAULT 0 COMMENT '不合格数量',
defect_description TEXT COMMENT '缺陷描述',
handling_method ENUM('退货', '换货', '让步接收', '返工') COMMENT '处理方式',
remark TEXT COMMENT '备注',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (receipt_item_id) REFERENCES goods_receipt_items(item_id),
FOREIGN KEY (inspector_id) REFERENCES users(user_id)
);
5. 系统用户与权限模块
-- 用户表
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL COMMENT '用户名',
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
full_name VARCHAR(100) NOT NULL COMMENT '姓名',
email VARCHAR(50) UNIQUE COMMENT '邮箱',
phone VARCHAR(20) COMMENT '电话',
department_id INT COMMENT '所属部门ID',
role_id INT NOT NULL COMMENT '角色ID',
is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
last_login_time DATETIME COMMENT '最后登录时间',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id)
);
-- 角色表
CREATE TABLE roles (
role_id INT PRIMARY KEY AUTO_INCREMENT,
role_name VARCHAR(50) UNIQUE NOT NULL COMMENT '角色名称',
description TEXT COMMENT '角色描述',
permissions TEXT COMMENT '权限列表（JSON格式）',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 部门表
CREATE TABLE departments (
department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(50) UNIQUE NOT NULL COMMENT '部门名称',
parent_id INT COMMENT '上级部门ID',
manager_id INT COMMENT '部门经理ID',
description TEXT COMMENT '部门描述',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES departments(department_id),
FOREIGN KEY (manager_id) REFERENCES users(user_id)
);
关键业务逻辑说明
-
采购流程：
- 申请部门创建采购申请 → 审批 → 生成采购订单 → 供应商发货 → 收货确认 → 质检 → 入库。
- 通过
purchase_requests和purchase_orders表关联，确保采购流程可追溯。
-
库存管理：
- 入库操作通过
goods_receipts和part_inventory_history表更新库存。 - 设置
safety_stock字段用于库存预警。
- 入库操作通过
-
供应商评估：
- 通过
supplier_qualifications管理供应商资质文件。 credit_rating字段记录供应商信用等级，影响采购决策。
- 通过
-
权限控制：
- 基于角色的访问控制（RBAC），通过
roles表管理权限。 - 用户分为管理员、采购人员、质检人员、仓库人员等角色。
- 基于角色的访问控制（RBAC），通过
数据库优化建议
-
索引设计：
- 在高频查询字段（如
part_code、order_code、supplier_code）上添加索引。 - 对关联字段（如
supplier_id、part_id）添加外键索引。
- 在高频查询字段（如
-
分区与分表：
- 对于历史数据量大的表（如
part_inventory_history），按时间分区。 - 考虑将读写频繁的表（如
parts）与历史记录表分离。
- 对于历史数据量大的表（如
-
备份策略：
- 定期全量备份数据库，对关键表（如采购订单）实时增量备份。
- 测试数据恢复流程，确保数据安全性。
该设计满足新能源汽车零件采购的核心需求，支持从申请到入库的全流程管理，同时兼顾了数据安全性和可扩展性。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
