Ch10-1 : Tables & Tirrger

[Tables & Tirrger]
在開始之前,我先說明這章節用到的SQL語法部份是用MySQL相關函式的,因為我們採用的資料庫是MySQL的分支MairaDB,部份語法只能適用在MySQL上,並不完全相容於其它類型資料庫,像SQL ServerOracle DB這些資料庫。

另外這章節之後,會大量使用到資料庫語言,若是完全沒有接觸過的朋友,建議先參閱Reference,簡單先學習MySQL的建立(CREATE)、查尋(SELECT)、新增(INSERT)、刪除(DELETE)、更新(UPDATE)的基本語法後,再往下學習才會比較順暢。

我設計的註冊功能,目前會需要設計出6個表單(Tables)與1個觸發器(Tirrger),幫助使用者可以註冊與認證帳號,以下每張表單與觸發器,請使用資料庫管理工具(ex : HeidiSQL),一一建立起來。那我們往下看每張表單與觸發器各自的用途與目的是什麼吧!!


Table - 會員主檔(member_main)
現階段,為了註冊簡單,只設計出主要表單。未來有需要,可以再新增detail,寫入需要的資料。
而這張表的功能是,紀錄使用者註冊的信箱,密碼,與是否啟用帳號等等資訊,這個表單再下一個章節登入(Login)也是必須使用到的表單。每張表的設計,通常都會有主鍵PRIMARY KEY,最主要要特別注意memberNo,它是一個主鍵,未來相關表單都會需要這個Key當作關聯或查尋相關表單使用,要特別注意這部份。以下是會員主檔表單的設計。

-- 會員表單
-- memberNo 會員編號  (不可以重複,主鍵。當每個使用者註冊時,都會有唯得一個編號,可以查尋到會員相關資料)
-- email 使用者信箱   (不可以重複,不重覆鍵。每個帳號註冊,只允許一個信箱註冊一個會員資料)
-- status 認證狀態    (初次註冊者,會給預設值1,尚未認證)
-- password 密碼      
-- username 使用者名稱 (索引鍵。之後每個使用者註冊時,會檢查使用者名稱不可以重複的功能。)
-- createDate 創立日期 (ex:20160921080102)
-- modifyDate 修改日期 (ex:20160921080102)
CREATE TABLE `member_main` (
    `memberNo`   VARCHAR(15)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `email`       VARCHAR(150) NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `status`      VARCHAR(1)   NOT NULL DEFAULT '1' NULL COLLATE 'utf8_unicode_ci',
    `password`   VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `username`   VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `createDate` VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `modifyDate` VARCHAR(50)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    CONSTRAINT pk_memberNo PRIMARY KEY (memberNo),
    UNIQUE KEY (email),
    INDEX (username)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;


Table - 會員流水號表單(member_main_seq)
這表單用途是,每次呼叫時,會建立一組自動累加數字當作編號,用來給member_main.memberNo會員編號使用。
EX : 0000000001

-- 會員流水號表單,用於取得最新的ㄧ筆auto key
CREATE TABLE member_main_seq
(
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
);


Trigger - 會員流水號觸發器(tg_member_main_insert)
觸發器(Trigger),可以用來當某些情況發生時,主動替我們做ㄧ些事情的功能。以下這段觸發器是說明,當註冊會員,準備新增資料時,就會觸發到tg_member_main_insert這個功能,就自動寫入最新一組編號idmember_main_seq,再把最新一筆的member_main_seq.id,給新註冊會員的會員編號使用,就不需要用SQL檢查最後一筆會員資料,以免造成會員編號重複的問題。
EX memberNo : mem000000000001

-- 建立觸發器,在insert member_main之前
-- 會先insert到member_main_seq,取得最新一組編號後
-- 再用英文mem與數字組一個最新的memberNo
-- 給註冊會員主檔使用
DELIMITER $$
CREATE TRIGGER tg_member_main_insert
BEFORE INSERT ON member_main
FOR EACH ROW
BEGIN
  INSERT INTO member_main_seq VALUES (NULL);
  SET NEW.memberNo = CONCAT('mem', LPAD(LAST_INSERT_ID(), 12, '0'));
END$$
DELIMITER ;


Table - 會員修改紀錄表單(member_main_log)
會員資料的修改,是需要主動紀錄下來的動作,這樣我們就可以知道會員的修改歷程,當有會員資料出現問題時,就可以檢查member_main_log的紀錄。這張表單,會是紀錄上一次的會員資料,而不是當下member_login的會員資料。

-- 會員修改紀錄表單
-- memberNo 會員編號  (索引鍵)
-- email 使用者信箱  
-- status 會員狀態  
-- password 密碼      
-- username 使用者名稱 
-- createDate 創立日期 (ex:20160921080102)
CREATE TABLE `member_main_log` (
  `memberNo`     VARCHAR(15)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `email`       VARCHAR(150) NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `status`      VARCHAR(1)      NOT NULL DEFAULT '1' NULL COLLATE 'utf8_unicode_ci',
    `password`   VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `username`   VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `createDate` VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    index (memberNo)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;


Table - 會員登入紀錄(member_login_log)
當使用者登入網站,或加入會員時,會紀錄目前使用者的各種登入或加入會員的狀態。

-- 會員登入紀錄
-- memberNo 會員編號  (索引鍵)
-- email 使用者信箱  
-- status 登入狀態
-- ipAddress 網路位址
-- device 登入的裝置
-- loginDate 登入日期
CREATE TABLE `member_login_log` (
    `memberNo`   VARCHAR(15)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `status`      VARCHAR(2)   NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `ipAddress`  VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `device`       VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `loginDate`  VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
     index (memberNo)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;


Table - 會員認證表單(member_token)
當使用者成功註冊會員時,我們會寫一段認證字串到資料庫裡,再寄送認證信件給新會員使用,若是成功點選認證信,認證成功之後,他們才能登入會員,使用相關的服務。而這個表單未來會給後面章節使用

-- 會員Token表單
-- tokenString Token字串 (主鍵)
-- memberNo 會員編號 (索引鍵,可以關聯到會員主檔,知道哪個會員尚未認證)
-- type 類型 , ex:Singup 註冊
-- isUse 認證字串 (代表是否使用過該字串)
-- sendDate 寄信日期 (ex:20160920080102)
-- createDate 創立日期 (ex:20160921080102)
-- modifyDate 修改日期 (ex:20160921090102)
-- expiryDate 逾期日期 (ex:20160921100102)
CREATE TABLE `member_token` (
    `tokenString`  VARCHAR(100) NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `memberNo`     VARCHAR(15)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `type`         VARCHAR(20)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `sendDate`       VARCHAR(50)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `isUse`        BOOL            NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `createDate`   VARCHAR(50)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `modifyDate`   VARCHAR(50)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
    `expiryDate`   VARCHAR(50)  NOT NULL NULL COLLATE  'utf8_unicode_ci',
     CONSTRAINT pk_authString PRIMARY KEY (tokenString),
     index (memberNo)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;


Table - 表單狀態對照表(table_status_desc)
未來,表單可能會需要其它的狀態碼,為了集中管理,我設計出一個表單,去紀錄表單相關的狀態碼,來說明各各表單的status,各代表的意思。

-- tableName 表單名稱
-- status 狀態碼
-- statusDesc 狀態說明
-- 表單狀態對照表
CREATE TABLE `table_status_desc` (
    `tableName`  VARCHAR(50)  NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `status`       VARCHAR(10)     NOT NULL NULL COLLATE 'utf8_unicode_ci',
    `statusDesc` VARCHAR(50)  DEFAULT NULL NULL COLLATE 'utf8_unicode_ci',
     index (tableName)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

-- member_main 狀態對照
INSERT table_status_desc VALUES ('member_main','1','尚未認證');
INSERT table_status_desc VALUES ('member_main','2','已認證');
INSERT table_status_desc VALUES ('member_main','3','帳號停用');

-- member_login_log 狀態對照
INSERT table_status_desc VALUES ('member_login_log','1','加入會員成功');
INSERT table_status_desc VALUES ('member_login_log','2','登入成功');
INSERT table_status_desc VALUES ('member_login_log','3','登入失敗');



[Final]
最後我們整理一下這小節建立的表單與觸發器,它們都是之後寫實際註冊功能時,會需要用到的表單。
member_main : 會員主檔表單
member_main_seq : 會員流水號表單
tg_member_main_insert : 會員流水號觸發器
member_main_log : 會員修改紀錄表單
member_login_log : 會員登入紀錄
member_token : 會員Token表單
table_status_desc : 表單狀態對照表
下面一個小章節,就會依據這些資料庫表單的設計,設計出對應的網頁畫面。


[Reference]

SQL Tutorial
http://www.w3schools.com/sql/

MySQL Tutorial
http://www.tutorialspoint.com/mysql/

Create insert trigger to auto increment int field of composite PK
http://stackoverflow.com/questions/6618139/create-insert-trigger-to-auto-increment-int-field-of-composite-pk-string-int

results matching ""

    No results matching ""