數(shù)據(jù)庫(kù)設(shè)計(jì)——醫(yī)藥銷(xiāo)售管理系統(tǒng)

2024-07-21 02:51:52

字體：大中小

供稿：網(wǎng)友

開(kāi)發(fā)環(huán)境和開(kāi)發(fā)工具

操作系統(tǒng)：win8.1 開(kāi)發(fā)環(huán)境：MySQL、Web 開(kāi)發(fā)工具：Workbench、Eclipse、JDBC

功能需求分析

員工有權(quán)查看、添加會(huì)員，查看、添加供應(yīng)商，查詢(xún)藥品（輸入藥品編號(hào)或名稱(chēng)、類(lèi)別等查詢(xún)?cè)撍幤坊蛟擃?lèi)藥品庫(kù)存），添加藥品采購(gòu)記錄，銷(xiāo)售藥品，處理退貨，盤(pán)點(diǎn)倉(cāng)庫(kù)，查看銷(xiāo)售、退貨、入庫(kù)記錄，修改個(gè)人信息經(jīng)理有權(quán)查看、添加、刪除會(huì)員，查看、添加、刪除供應(yīng)商，查看、添加、刪除員工，盤(pán)點(diǎn)倉(cāng)庫(kù)，查看銷(xiāo)售、退貨、入庫(kù)記錄，修改個(gè)人信息，無(wú)權(quán)進(jìn)行銷(xiāo)售和退貨業(yè)務(wù)供應(yīng)商和顧客對(duì)此系統(tǒng)沒(méi)有使用權(quán)限系統(tǒng)設(shè)計(jì)

這里寫(xiě)圖片描述

系統(tǒng)設(shè)計(jì)

數(shù)據(jù)流這里寫(xiě)圖片描述

E-R圖

數(shù)據(jù)庫(kù)關(guān)系模式設(shè)計(jì)

登錄用戶(hù)（用戶(hù)編號(hào)，用戶(hù)名，密碼，類(lèi)別）員工（員工編號(hào)，員工姓名，聯(lián)系電話(huà)，用戶(hù)編號(hào)）經(jīng)理（經(jīng)理編號(hào)，用戶(hù)編號(hào)）財(cái)政收支（收支編號(hào)，藥品編號(hào)，員工編號(hào)，數(shù)量，日期，總額，類(lèi)型）供應(yīng)商（供應(yīng)商編號(hào)，供應(yīng)商名稱(chēng)，聯(lián)系人，聯(lián)系方式，所在城市）會(huì)員（客戶(hù)編號(hào)，客戶(hù)姓名，聯(lián)系方式）入庫(kù)記錄（入庫(kù)記錄編號(hào)，供應(yīng)商編號(hào)，收支編號(hào)）退貨管理（退貨編號(hào)，銷(xiāo)售編號(hào)，收支編號(hào)）銷(xiāo)售管理（銷(xiāo)售編號(hào)，客戶(hù)編號(hào)，收支編號(hào)）藥品（藥品編號(hào)，藥品名稱(chēng)，供應(yīng)商編號(hào)，生產(chǎn)批號(hào)，產(chǎn)地，所屬類(lèi)別，進(jìn)價(jià)，單價(jià)，會(huì)員折扣，庫(kù)存，包裝規(guī)格，生產(chǎn)日期，有效期）

數(shù)據(jù)庫(kù)物理結(jié)構(gòu)設(shè)計(jì)

本次項(xiàng)目使用的引擎是InnoDB，MySQL的數(shù)據(jù)庫(kù)引擎之一。InnoDB存儲(chǔ)引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲(chǔ)它的表&索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤(pán)分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個(gè)表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。InnoDB默認(rèn)地被包含在MySQL二進(jìn)制分發(fā)中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認(rèn)表。

此外還使用了數(shù)據(jù)庫(kù)索引，索引是對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問(wèn)數(shù)據(jù)庫(kù)表中的特定信息。各表索引如下：會(huì)員：PRimary key(客戶(hù)編號(hào)) 藥品：primary key(藥品編號(hào)), INDEX 供應(yīng)商編號(hào)_idx (供應(yīng)商編號(hào) ASC), 供應(yīng)商：primary key(供應(yīng)商編號(hào)) 登錄用戶(hù)：primary key(用戶(hù)編號(hào)) 用戶(hù)名 unique, 經(jīng)理：primary key(經(jīng)理編號(hào)), INDEX 經(jīng)理編號(hào)_idx (用戶(hù)編號(hào) ASC), 員工：primary key(員工編號(hào)), INDEX 員工編號(hào)_idx (用戶(hù)編號(hào) ASC), 財(cái)政收支：PRIMARY KEY (收支編號(hào)), INDEX 藥品編號(hào)_idx (藥品編號(hào) ASC), INDEX 員工編號(hào)_idx (員工編號(hào) ASC), 入庫(kù)記錄：primary key(入庫(kù)記錄編號(hào)), INDEX 供應(yīng)商編號(hào)_idx (供應(yīng)商編號(hào) ASC), INDEX 收支編號(hào)_idx (收支編號(hào) ASC), 銷(xiāo)售管理：PRIMARY KEY (銷(xiāo)售編號(hào)), INDEX 客戶(hù)編號(hào)_idx (客戶(hù)編號(hào) ASC), INDEX 收支編號(hào)_idx (收支編號(hào) ASC), 退貨管理：PRIMARY KEY (退貨編號(hào)), INDEX 銷(xiāo)售編號(hào)_idx (銷(xiāo)售編號(hào) ASC), INDEX 收支編號(hào)_idx (收支編號(hào) ASC),

系統(tǒng)功能的實(shí)現(xiàn)

建表

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

創(chuàng)建視圖方便查詢(xún)

/*倉(cāng)庫(kù)*/drop view if exists warehouse; create view warehouse asselect 藥品編號(hào),藥品名稱(chēng),供應(yīng)商名稱(chēng),生產(chǎn)批號(hào),產(chǎn)地,所屬類(lèi)別,進(jìn)價(jià),單價(jià),會(huì)員折扣,庫(kù)存,包裝規(guī)格,生產(chǎn)日期,有效期from 藥品,供應(yīng)商 where 藥品.供應(yīng)商編號(hào)=供應(yīng)商.供應(yīng)商編號(hào); /*入庫(kù)記錄*/drop view if exists InDrug_records; create view InDrug_records asselect 入庫(kù)記錄編號(hào),藥品名稱(chēng),供應(yīng)商名稱(chēng),員工姓名,數(shù)量,日期,總額 from 入庫(kù)記錄,供應(yīng)商,財(cái)政收支,員工,藥品where 入庫(kù)記錄.供應(yīng)商編號(hào)=供應(yīng)商.供應(yīng)商編號(hào) and 入庫(kù)記錄.收支編號(hào)=財(cái)政收支.收支編號(hào) and財(cái)政收支.員工編號(hào)=員工.員工編號(hào) and 財(cái)政收支.藥品編號(hào)=藥品.藥品編號(hào);/*銷(xiāo)售記錄*/drop view if exists sales_records; create view sales_records asselect 銷(xiāo)售編號(hào),藥品.藥品編號(hào),藥品名稱(chēng),單價(jià),會(huì)員折扣,庫(kù)存,客戶(hù)姓名,員工姓名,數(shù)量,日期,總額from 銷(xiāo)售管理,會(huì)員,財(cái)政收支,員工,藥品where 銷(xiāo)售管理.客戶(hù)編號(hào)=會(huì)員.客戶(hù)編號(hào) and 銷(xiāo)售管理.收支編號(hào)=財(cái)政收支.收支編號(hào)and 財(cái)政收支.員工編號(hào)=員工.員工編號(hào) and 財(cái)政收支.藥品編號(hào)=藥品.藥品編號(hào);/*退貨記錄*/drop view if exists reject_records;create view reject_records asselect 退貨編號(hào),退貨管理.銷(xiāo)售編號(hào),藥品名稱(chēng),客戶(hù)姓名,員工姓名,數(shù)量,日期,總額from 退貨管理,銷(xiāo)售管理,會(huì)員,財(cái)政收支,員工,藥品where 退貨管理.銷(xiāo)售編號(hào)=銷(xiāo)售管理.銷(xiāo)售編號(hào) and 銷(xiāo)售管理.客戶(hù)編號(hào)=會(huì)員.客戶(hù)編號(hào) and 退貨管理.收支編號(hào)=財(cái)政收支.收支編號(hào) and 財(cái)政收支.員工編號(hào)=員工.員工編號(hào) and 財(cái)政收支.藥品編號(hào)=藥品.藥品編號(hào);/*員工信息*/drop view if exists employee_info;create view employee_info asselect 員工編號(hào),員工姓名,聯(lián)系電話(huà),員工.用戶(hù)編號(hào),用戶(hù)名 from `員工` natural join `登錄用戶(hù)`;/*收支記錄*/drop view if exists financial_records;create view financial_records asselect 類(lèi)型,藥品名稱(chēng),數(shù)量,日期,總額 from `財(cái)政收支` left join `藥品` on `財(cái)政收支`.藥品編號(hào)=`藥品`.`藥品編號(hào)`;

使用過(guò)程實(shí)現(xiàn)“帶參數(shù)的視圖“

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

JDBC調(diào)用方法如下

//調(diào)用過(guò)程 -- 統(tǒng)計(jì)盈虧、收入、支出 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();

建立觸發(fā)器保證邏輯正確

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

將業(yè)務(wù)邏輯封裝為事務(wù)，如銷(xiāo)售事務(wù)

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

界面效果

登陸這里寫(xiě)圖片描述主頁(yè) 倉(cāng)庫(kù) 點(diǎn)擊新增進(jìn)行藥品入庫(kù) 入庫(kù)/銷(xiāo)售/退貨記錄員工可查看/增加客戶(hù)，供應(yīng)商經(jīng)理可增刪員工、客戶(hù)、供應(yīng)商銷(xiāo)售藥品退貨財(cái)務(wù)統(tǒng)計(jì) 這里寫(xiě)圖片描述