數據庫設計——醫藥銷售管理系統

2024-07-21 02:51:52

字體：大中小

來源：轉載

供稿：網友

開發環境和開發工具

操作系統：win8.1 開發環境：MySQL、Web 開發工具：Workbench、Eclipse、JDBC

功能需求分析

員工有權查看、添加會員，查看、添加供應商，查詢藥品（輸入藥品編號或名稱、類別等查詢該藥品或該類藥品庫存），添加藥品采購記錄，銷售藥品，處理退貨，盤點倉庫，查看銷售、退貨、入庫記錄，修改個人信息經理有權查看、添加、刪除會員，查看、添加、刪除供應商，查看、添加、刪除員工，盤點倉庫，查看銷售、退貨、入庫記錄，修改個人信息，無權進行銷售和退貨業務供應商和顧客對此系統沒有使用權限系統設計

這里寫圖片描述

系統設計

數據流

E-R圖

數據庫關系模式設計

登錄用戶（用戶編號，用戶名，密碼，類別）員工（員工編號，員工姓名，聯系電話，用戶編號）經理（經理編號，用戶編號）財政收支（收支編號，藥品編號，員工編號，數量，日期，總額，類型）供應商（供應商編號，供應商名稱，聯系人，聯系方式，所在城市）會員（客戶編號，客戶姓名，聯系方式）入庫記錄（入庫記錄編號，供應商編號，收支編號）退貨管理（退貨編號，銷售編號，收支編號）銷售管理（銷售編號，客戶編號，收支編號）藥品（藥品編號，藥品名稱，供應商編號，生產批號，產地，所屬類別，進價，單價，會員折扣，庫存，包裝規格，生產日期，有效期）

數據庫物理結構設計

本次項目使用的引擎是InnoDB，MySQL的數據庫引擎之一。InnoDB存儲引擎為在主內存中緩存數據和索引而維持它自己的緩沖池。InnoDB存儲它的表&索引在一個表空間中，表空間可以包含數個文件（或原始磁盤分區）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。InnoDB默認地被包含在MySQL二進制分發中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認表。

此外還使用了數據庫索引，索引是對數據庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問數據庫表中的特定信息。各表索引如下：會員：PRimary key(客戶編號) 藥品：primary key(藥品編號), INDEX 供應商編號_idx (供應商編號 ASC), 供應商：primary key(供應商編號) 登錄用戶：primary key(用戶編號) 用戶名 unique, 經理：primary key(經理編號), INDEX 經理編號_idx (用戶編號 ASC), 員工：primary key(員工編號), INDEX 員工編號_idx (用戶編號 ASC), 財政收支：PRIMARY KEY (收支編號), INDEX 藥品編號_idx (藥品編號 ASC), INDEX 員工編號_idx (員工編號 ASC), 入庫記錄：primary key(入庫記錄編號), INDEX 供應商編號_idx (供應商編號 ASC), INDEX 收支編號_idx (收支編號 ASC), 銷售管理：PRIMARY KEY (銷售編號), INDEX 客戶編號_idx (客戶編號 ASC), INDEX 收支編號_idx (收支編號 ASC), 退貨管理：PRIMARY KEY (退貨編號), INDEX 銷售編號_idx (銷售編號 ASC), INDEX 收支編號_idx (收支編號 ASC),

系統功能的實現

建表

drop database if exists 醫藥銷售管理系統;create database 醫藥銷售管理系統;use 醫藥銷售管理系統;//建立表會員create table `會員`( 客戶編號 int auto_increment, 客戶姓名 varchar(50), 聯系方式 varchar(100),primary key(客戶編號));//建立表供應商create table `供應商`( 供應商編號 int auto_increment, 供應商名稱 varchar(50), 聯系人 varchar(50), 聯系方式 varchar(50), 所在城市 varchar(50),primary key(供應商編號));//建立表藥品create table `藥品`( 藥品編號 int auto_increment, 藥品名稱 varchar(50) not null, 供應商編號 int not null, 生產批號 varchar(100), 產地 varchar(50), 所屬類別 varchar(50), 進價 decimal(10,2) not null, 單價 decimal(10,2) not null, 會員折扣 decimal(3,2), 庫存 int not null, 包裝規格 varchar(50), 生產日期 varchar(50), 有效期 varchar(50),primary key(藥品編號),INDEX `供應商編號_idx` (`供應商編號` ASC),CONSTRAINT `供應商編號`FOREIGN KEY (`供應商編號`)REFERENCES `醫藥銷售管理系統`.`供應商` (`供應商編號`)ON DELETE CASCADEON UPDATE CASCADE);//建立表登錄用戶，可以登錄醫藥銷售管理系統，類別為1代表員工，2代表經理create table `登錄用戶`( 用戶編號 int auto_increment, 用戶名 varchar(40) not null unique, 密碼 varchar(40) not null, 類別 int not null,primary key(用戶編號));//建立表經理，具有登錄用戶編號，可登錄系統create table `經理`( 經理編號 int auto_increment, 用戶編號 int not null,primary key(經理編號),INDEX `經理編號_idx` (`用戶編號` ASC),CONSTRAINT `經理登陸編號`FOREIGN KEY (`用戶編號`)REFERENCES `醫藥銷售管理系統`.`登錄用戶` (`用戶編號`)ON DELETE CASCADEON UPDATE CASCADE);//建立表員工，具有登錄用戶編號，可登錄系統create table `員工`( 員工編號 int auto_increment, 員工姓名 varchar(50), 聯系電話 varchar(100), 用戶編號 int not null,primary key(員工編號),INDEX `員工編號_idx` (`用戶編號` ASC),CONSTRAINT `員工登陸編號`FOREIGN KEY (`用戶編號`)REFERENCES `醫藥銷售管理系統`.`登錄用戶` (`用戶編號`)ON DELETE CASCADEON UPDATE CASCADE);//建立表財政開支，涉及藥品編號、負責員工、數量、金額等信息，類型有入庫、銷售、退貨等CREATE TABLE `財政收支` (`收支編號` int auto_increment,`藥品編號` int,`員工編號` int not null,`數量` int,`日期` datetime NOT NULL,`總額` decimal(10,2) NOT NULL,`類型` VARCHAR(20) NOT NULL,PRIMARY KEY (`收支編號`),INDEX `藥品編號_idx` (`藥品編號` ASC),CONSTRAINT `藥品編號`FOREIGN KEY (`藥品編號`)REFERENCES `醫藥銷售管理系統`.`藥品` (`藥品編號`)ON DELETE CASCADEON UPDATE CASCADE,INDEX `員工編號_idx` (`員工編號` ASC),CONSTRAINT `負責員工編號`FOREIGN KEY (`員工編號`)REFERENCES `醫藥銷售管理系統`.`員工` (`員工編號`)ON DELETE CASCADEON UPDATE CASCADE);//建立表入庫記錄，包含供應商、收支編號create table `入庫記錄`( 入庫記錄編號 int auto_increment, 供應商編號 int not null, 收支編號 int not null,primary key(入庫記錄編號),INDEX `供應商編號_idx` (`供應商編號` ASC),INDEX `收支編號_idx` (`收支編號` ASC),CONSTRAINT `入貨供應商編號`FOREIGN KEY (`供應商編號`)REFERENCES `醫藥銷售管理系統`.`供應商` (`供應商編號`)ON DELETE CASCADEON UPDATE CASCADE,CONSTRAINT `入庫收支編號`FOREIGN KEY (`收支編號`)REFERENCES `醫藥銷售管理系統`.`財政收支` (`收支編號`)ON DELETE CASCADEON UPDATE CASCADE);//建立表銷售管理，包含客戶、收支編號CREATE TABLE `銷售管理`(`銷售編號` int auto_increment,`客戶編號` int,`收支編號` int not null,PRIMARY KEY (`銷售編號`),INDEX `客戶編號_idx` (`客戶編號` ASC),INDEX `收支編號_idx` (`收支編號` ASC),CONSTRAINT `銷售客戶編號`FOREIGN KEY (`客戶編號`)REFERENCES `醫藥銷售管理系統`.`會員` (`客戶編號`)ON DELETE CASCADEON UPDATE CASCADE,CONSTRAINT `銷售收支編號`FOREIGN KEY (`收支編號`)REFERENCES `醫藥銷售管理系統`.`財政收支` (`收支編號`)ON DELETE CASCADEON UPDATE CASCADE);//建立表退貨管理，包含銷售、收支編號CREATE TABLE `退貨管理` (`退貨編號` int auto_increment,`銷售編號` int NOT NULL,`收支編號` int NOT NULL,PRIMARY KEY (`退貨編號`),INDEX `銷售編號_idx` (`銷售編號` ASC),INDEX `收支編號_idx` (`收支編號` ASC),CONSTRAINT `退貨銷售編號`FOREIGN KEY (`銷售編號`)REFERENCES `醫藥銷售管理系統`.`銷售管理` (`銷售編號`)ON DELETE CASCADEON UPDATE CASCADE,CONSTRAINT `退貨收支編號`FOREIGN KEY (`收支編號`)REFERENCES `醫藥銷售管理系統`.`財政收支` (`收支編號`)ON DELETE CASCADEON UPDATE CASCADE);

創建視圖方便查詢

/*倉庫*/drop view if exists warehouse; create view warehouse asselect 藥品編號,藥品名稱,供應商名稱,生產批號,產地,所屬類別,進價,單價,會員折扣,庫存,包裝規格,生產日期,有效期from 藥品,供應商 where 藥品.供應商編號=供應商.供應商編號; /*入庫記錄*/drop view if exists InDrug_records; create view InDrug_records asselect 入庫記錄編號,藥品名稱,供應商名稱,員工姓名,數量,日期,總額 from 入庫記錄,供應商,財政收支,員工,藥品where 入庫記錄.供應商編號=供應商.供應商編號 and 入庫記錄.收支編號=財政收支.收支編號 and財政收支.員工編號=員工.員工編號 and 財政收支.藥品編號=藥品.藥品編號;/*銷售記錄*/drop view if exists sales_records; create view sales_records asselect 銷售編號,藥品.藥品編號,藥品名稱,單價,會員折扣,庫存,客戶姓名,員工姓名,數量,日期,總額from 銷售管理,會員,財政收支,員工,藥品where 銷售管理.客戶編號=會員.客戶編號 and 銷售管理.收支編號=財政收支.收支編號and 財政收支.員工編號=員工.員工編號 and 財政收支.藥品編號=藥品.藥品編號;/*退貨記錄*/drop view if exists reject_records;create view reject_records asselect 退貨編號,退貨管理.銷售編號,藥品名稱,客戶姓名,員工姓名,數量,日期,總額from 退貨管理,銷售管理,會員,財政收支,員工,藥品where 退貨管理.銷售編號=銷售管理.銷售編號 and 銷售管理.客戶編號=會員.客戶編號 and 退貨管理.收支編號=財政收支.收支編號 and 財政收支.員工編號=員工.員工編號 and 財政收支.藥品編號=藥品.藥品編號;/*員工信息*/drop view if exists employee_info;create view employee_info asselect 員工編號,員工姓名,聯系電話,員工.用戶編號,用戶名 from `員工` natural join `登錄用戶`;/*收支記錄*/drop view if exists financial_records;create view financial_records asselect 類型,藥品名稱,數量,日期,總額 from `財政收支` left join `藥品` on `財政收支`.藥品編號=`藥品`.`藥品編號`;

使用過程實現“帶參數的視圖“

drop procedure if exists payments_statistics;delimiter //create procedure payments_statistics(in date_limit varchar(20))begin select count(*) as 數目,sum(`總額`) as 盈虧, (select sum(`總額`) from `財政收支` where `總額` < 0 and `日期` like date_limit) as '支出', (select sum(`總額`) from `財政收支` where `總額` >= 0 and `日期` like date_limit) as '收入' from `財政收支` where `日期` like date_limit;end//

JDBC調用方法如下

//調用過程 -- 統計盈虧、收入、支出 CallableStatement cStmt = con.prepareCall("{call payments_statistics(?)}"); cStmt.setString(1,"%"+date+"%"); cStmt.execute(); rs = cStmt.getResultSet(); if(rs.next()){ count = rs.getInt(1); all = rs.getString(2); allOut = rs.getString(3); allIn = rs.getString(4); } rs.close();

建立觸發器保證邏輯正確

drop trigger if exists stock_update;delimiter //create trigger stock_update before update on 藥品for each rowbegin if new.庫存 < 0 /* MySQL不支持直接使用rollback回滾事務，可以利用delete當前表造成異常使事務回滾 */ then delete from 藥品 where 藥品編號=new.藥品編號; end if;end //update 藥品 set 庫存=-2 where 藥品編號=1;drop trigger if exists drugs_insert;delimiter //create trigger drugs_insert before insert on 藥品for each rowbegin if new.庫存 < 0 or new.單價 < 0 or new.進價 < 0 or new.會員折扣 > 1 or new.會員折扣 < 0 then delete from 藥品 where 藥品編號=new.藥品編號; end if;end ///*退貨數量不應比售出的多*/drop trigger if exists refunds_insert;delimiter //create trigger refunds_insert after insert on 退貨管理for each rowbegin if (select 數量 from sales_records where 銷售編號 = new.銷售編號) < (select 數量 from reject_records where 退貨編號 = new.退貨編號) then delete from 退貨管理 where 退貨編號=new.退貨編號; end if;end //

將業務邏輯封裝為事務，如銷售事務

PreparedStatement ps=null; Connection con = DriverManager.getConnection(connectString,"root", "2333"); ... con.setAutoCommit(false);//設置自動提交為false ... //銷售事務 //更新庫存 String fmt1="update 藥品 set `庫存`='%d' where `藥品編號`='%s'"; String sql1 = String.format(fmt1,drug_rest-pcount,drug_id); ps = con.prepareStatement(sql1); ps.executeUpdate(); //插入財政收支記錄 String fmt2="INSERT INTO `財政收支` (`藥品編號`,`員工編號`,`數量`,`日期`,`總額`,`類型`) VALUES ('%s','%d','%d','%s','%s','銷售')"; String sql2 = String.format(fmt2,drug_id,employee_id,pcount,today,money); ps = con.prepareStatement(sql2); ps.executeUpdate(); //獲取插入的財政收支編號 ps = con.prepareStatement("select @@identity;"); rs = ps.executeQuery(); if(rs.next()){ financial_id=rs.getInt(1); } rs.close(); //插入銷售記錄 String fmt3="insert into 銷售管理(客戶編號,收支編號) values(%s,'%d')"; String sql3 = String.format(fmt3,customer_id,financial_id); ps = con.prepareStatement(sql3); ps.executeUpdate(); //提交事務 con.commit(); }catch(Exception e){ try { con.rollback(); } catch (Exception e1) { e1.printStackTrace(); }