iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 10
0
Software Development

以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作系列 第 10

Day10:範例 db Table Schema 及測試用 store procedure 的說明。

接下來的內容,我會建構一個非常小,但是還算齊全的訂單資料庫。因為只是用來展示,和實際的檔案結構比起來,當然是精簡非常多,不過還是有基本的參考價值,因為篇幅的關係,只能列出 MS-SQL 的版本,MariaDB 的版本請自行修改。

/*
  zenOrder 測試 DB 的 Table Schema
*/
--客戶資料
CREATE TABLE zen_customer (
	cid varchar(10) NOT NULL PRIMARY KEY, 
	cname nvarchar(20), 
	connectperson nvarchar(60), 
	deliveraddress nvarchar(200), 
	pid varchar(10), 
	doc_path varchar(200), 
	memodoc nvarchar(200), 
	create_user nvarchar(20), 
	create_date datetime, 
	update_user nvarchar(20), 
	update_date datetime
);

--人員資料
create table zen_person (
  pid varchar(20) not null,
  pname nvarchar(100),
  did varchar(10),  --所屬部門(popup)
  birthday datetime,
  address nvarchar(200),
  pic_path varchar(200),  --上傳圖片
  doc_path varchar(200),  --上傳文檔
  pmemo nvarchar(600),
  create_date datetime,
  create_user varchar(20),
  edit_date datetime,
  edit_user varchar(20)
  primary key (pid)
)

--部門
create table zen_department (
  did varchar(10) not null,
  dname nvarchar(100),
  dboss varchar(20),  --部門主管(popup)
  dmemo nvarchar(200)
  primary key (did)
);

--人員履歷紀錄
create table zen_resume (
  pid varchar(20) not null,
  rsdate1 varchar(10),    --起始期間(年月)
  rsdate2 varchar(10),    --結束
  resume_corp nvarchar(100),
  resume_title nvarchar(100),
  resume_desc nvarchar(600),
  isboss char(1),  --是否主管
  primary key (pid,rsdate1,rsdate2)
);

/*
  倉庫檔
*/
CREATE TABLE zen_warehouse (
	wid varchar(10) NOT NULL PRIMARY KEY, 
	wname nvarchar(20), 
	wtype varchar(10), 
	wtel varchar(100), 
	waddress nvarchar(200), 
	memodoc nvarchar(600)
);

/*
  商品檔
*/
CREATE TABLE zen_product (
	prodid varchar(50) NOT NULL PRIMARY KEY, 
	prodcname nvarchar(100), 
	scancode varchar(20), 
	saleprice decimal(12, 2), 
	pricea decimal(12, 2), 
	priceb decimal(12, 2), 
	image_path1 varchar(200), 
	image_path2 varchar(200), 
	memodoc nvarchar(200), 
	create_user nvarchar(20), 
	create_date datetime, 
	update_user nvarchar(20), 
	update_date datetime
);

/* 訂單主檔 */
CREATE TABLE zen_order2_m (
	billno varchar(12) NOT NULL PRIMARY KEY, 
	billdate datetime, 
	onhanddate datetime, 
	issign char(1), 
	cid varchar(10), 
	connectperson nvarchar(60), 
	deliveraddress nvarchar(200), 
	pid varchar(10), 
	did varchar(10), 
	invoicetype smallint, 
	wid varchar(10), 
	doc_path varchar(200), 
	memodoc nvarchar(2000), 
	totalamount decimal(18, 6), 
	taxamount decimal(12, 2), 
	create_user nvarchar(20), 
	create_date datetime, 
	update_user nvarchar(20), 
	update_date datetime, 
	sign_user nvarchar(20), 
	sign_date datetime
);

/* 訂單明細檔 */
CREATE TABLE zen_order2_d (
	billno varchar(12) NOT NULL,     --關聯訂單表頭
	seqno int,                       --序號(order by seqno 顯示排序用)
	prodid varchar(50),              --產品編號
	prodcname nvarchar(100),         --品名
	qty decimal(18, 6),              --數量  
	unit varchar(10),                --單位
	price decimal(18, 6),            --單價
	subamount decimal(18, 6),        --小計(qty*price)
	memochar1 nvarchar(30),          --備註一
	memochar2 nvarchar(30),          --備註二 
	uniqueno int NOT NULL IDENTITY(1,1),
	PRIMARY KEY (billno, uniqueno)
);

/* 庫存餘額檔 */
CREATE TABLE zen_invfinal (
	prodid varchar(10) NOT NULL, 
	wid varchar(10) NOT NULL, 
	qty decimal(18, 6), 
	lowerqty decimal(18, 6),
	PRIMARY KEY (prodid, wid)
);

有了基本的資料庫結構,接下來就是要實際練習開發幾支 Store Procedure,下面先列出幾支功能列表,稍後的內容會繼續完善。Store Procedure 並不困難,只要多寫、多想、多看範例,隨著經驗的累積,就會越來越有效率。所以下面盡量以實際的範例說明。

您也可以先練習看看,可以自己先設定規格,然後實作。稍後會附上簡單的規格及主要的程式碼片段。

  • 客戶一覽表 (要能依業務、部分地址(%中山北路%) 查詢)
  • 人員一覽表 (要能依部門、部分姓名(%威%) 查詢)
  • 訂單日報簡要表 (日期區間、客戶區間、業務區間 查詢)
  • 訂單日報明細表 (日期區間、客戶區間、業務區間、商品區間 查詢)
  • 逾期未出貨的訂單明細表 (預定交貨日大於今天的訂單)
  • 久未成交的客戶一覽表 (30天內無交易紀錄)

上一篇
Day9:[以資料庫為開發核心] 開發架構的說明
下一篇
Day11:再論範例資料庫
系列文
以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言