PWR_Schema_Design_Decision: 將原本依照data type分開的Observation併回一個

下圖為原本規劃的Observation結構，此結構依照資料型態是Integer、Decimal、及String來將資料放在不同的表格中。這樣的設計方式會導致查詢及Insert資料時都變得很困難

以下說明在這樣的設計下，Query及Insert資料會有多複雜

1、查詢某個病人的血糖數據

Step1:取得符合檢驗數據的actID
SELECT A_Act.actID FROM A_Act,P_Particiaption_Subject,R_Patient,E_Person
WHERE E_Person.entityID = R_Patient.entityID AND
      R_Patient.roleID = P_Participation_Subject.roleID AND
      A_Act.actID = P_Participation_Subject.actID AND
      A_Act.classCode = 'OBS'

Step2:由Observation類別取得實際檢驗值

SELECT resultValue,resultCaptureDatetime from A_ObservationInteger
WHERE  resultTitle = 'BloodSugar' UNION
SELECT resultValue,resultCaptureDatetime from A_ObservationDecimal
WHERE  resultTitle = 'BloodSugar' UNION
SELECT resultValue,resultCaptureDatetime from A_ObservationString
WHERE  resultTitle = 'BloodSugar'
上述的Query並不假設BloodSugar存放在ObservationInteger或ObservationString表格中，這樣的查詢
也比較容易撰寫。
要注意的是，在Insert資料時，BloodSugar只能Insert到ObservationDecimal中，而何種檢驗數據要放在那個表格，可記錄在另一Meta_ObservationTile表格中

=============================================

2. Insert某病人的檢驗報告，內含10種檢驗數據

Step1: 建立一筆新資料到A_Act表格
INSERT INTO A_Act (actID,classCode,moodCode)
       VALUES  (<actID>,'OBS','EVN')

Step2: 查詢Meta_ObservationTitle表格，得到那些數據要放到那些表格
SELECT observationTitle, recordTable from Meta_ObservationTile
       WHERE    observationTitle in ('BloodSugar','BodyWeight','...')

Step3: 以一個While迴圈去iterate Step2的SQL回傳值。針對每一筆回傳值，將資料Insert到相對應表格，下面以BloodSugar為例，假設查詢結果顯示BloodSugar應該放在A_ObservationDecimal

INSERT INTO A_ObservationDecimal (obsTitle,obsValue,obsCaptureDatetime,actID,code,obsUnit)
       VALUES  ('BloodSugar',100.2,20100623110102,<actID>,'checkType','mg/dl')

Step4: 等迴圈處理完畢後，Commit

Step5: 在E_Person中，檢查entityID是否已存在，若不存在則Create新的一筆E_Person Record
Step6: 查詢該E_Person實體的entityID有沒有Play病人的Role，若沒有則Create新的一筆R_Patient實體

SELECT R_Role.roleID FROM E_Person,R_Patient,R_Role
WHERE  E_Person.entityID = R_Patient.entityID AND
       R_Patient.roleID = R_Role.roleID AND
       R_Role.classCode = 'PAT'

Step7: 將actID與roleID更新到P_Participation_Subject中
INSERT INTO P_Participation_Subject (actID,roleID,typeCode)
VALUES    (<actID>,<roleID>,'SBJ')

Step8: Commit

 

為了簡化Insert及Update的動作，與Liangzhou討論過後，決定要將原本依照不同資料型態而分成不同表格的Observation，合併成一個Observation table。合併後結果如下圖

Query及Insert資料的步驟如下

1、查詢某個病人的血糖數據

Step1:取得符合檢驗數據的actID
SELECT A_Act.actID FROM A_Act,P_Particiaption_Subject,R_Patient,E_Person
WHERE E_Person.entityID = R_Patient.entityID AND
      R_Patient.roleID = P_Participation_Subject.roleID AND
      A_Act.actID = P_Participation_Subject.actID AND
      A_Act.classCode = 'OBS'

Step2:由Observation類別取得實際檢驗值

SELECT obsIntegerValue,obsDecimalValue,obsStringValue,resultCaptureDatetime from A_Observation
WHERE  resultTitle = 'BloodSugar'

在回傳值中，obsIntegerValue,obsDecimalValue和obsStringValue應該只會有一個有值，程式必需進行判斷。
也可以建立一個Meta_ObservationTile表格，記錄什麼檢驗數據是什麼資料型態。然後程式先查詢該Meta table，得知要查詢Integer、Decimal還是String

2. Insert某病人的檢驗報告，內含10種檢驗數據

Step1: 建立一筆新資料到A_Act表格
INSERT INTO A_Act (actID,classCode,moodCode)
       VALUES  (<actID>,'OBS','EVN')

Step2: 查詢Meta_ObservationTitle表格，得到那些數據要放到那些表格
SELECT observationTitle, recordTable from Meta_ObservationTile
       WHERE    observationTitle in ('BloodSugar','BodyWeight','...')

Step3: 以一個While迴圈去iterate Step2的SQL回傳值。針對每一筆回傳值，將資料Insert到A_Observation的相對應欄位，下面以BloodSugar為例，假設查詢結果顯示BloodSugar應該放在obsDecimalValue欄位中

INSERT INTO A_Observation (obsTitle,obsDecimalValue,obsCaptureDatetime,actID,code,obsUnit)
       VALUES  ('BloodSugar',100.2,20100623110102,<actID>,'checkType','mg/dl')

Step4: 等迴圈處理完畢後，Commit

Step5: 在E_Person中，檢查entityID是否已存在，若不存在則Create新的一筆E_Person Record
Step6: 查詢該E_Person實體的entityID有沒有Play病人的Role，若沒有則Create新的一筆R_Patient實體

SELECT R_Role.roleID FROM E_Person,R_Patient,R_Role
WHERE  E_Person.entityID = R_Patient.entityID AND
       R_Patient.roleID = R_Role.roleID AND
       R_Role.classCode = 'PAT'

Step7: 將actID與roleID更新到P_Participation_Subject中
INSERT INTO P_Participation_Subject (actID,roleID,typeCode)
VALUES    (<actID>,<roleID>,'SBJ')

Step8: Commit

這樣的修改對於減化Query的幫助較大，而對於減化Insert的幫助較為有限。不過至少可以讓整個data model較容易了解