資料庫設計之儲存多值的問題,資料庫儲存
儲存多值的問題在設計資料庫時是很普遍的問題,看到很多開發人員在上面吃了虧,我覺得有必要拿出來說。
業務情境:一個業務單據,有多個連絡人。一個裝置維護工作,有多個維護班組。下面來舉個例子
createtable BILL
(
bill_id numberprimarykey,
bill_name varchar2(20),
bill_contentvarchar2(200),
contact_idnumber--來至於user表的user_id
);
1. 在起初的設計中,連絡人只有一個,後來需求有變化了,連絡人又多個。有幾種方案:
方案一:在加幾個欄位,contact_id1,contact_id2,contact_id3...。
方案二:把contact_id的number類型改為varchar2,多值一起儲存,值與值之間用分割符隔開(如逗號)。
方案三:再加一張表bill_contact
createtable bill_contact
(
bill_id number,
contact_idnumber
);
altertable BILL_CONTACT
addconstraint pk_bill_contactprimarykey (BILL_ID,CONTACT_ID);
2. 對比幾個方案
方案一顯然不合適,不知道建幾個欄位合適,就算知道最多有幾個連絡人,查詢起來也很麻煩。查詢單中包含連絡人100和101的記錄,
select *from bill_contact
where (contact_id =100and contact_id1 =101)
or (contact_id =101and contact_id1 =100);
查詢單中包含連絡人100的記錄,
select *from bill_contact
where (contact_id =100or contact_id1 =101 or….);
方案二的優點在於方便,開發人員只需要改動少量的代碼,普遍被開發人員採納。a. 但好景不長,分析、統計功能非常難做,如需要列出在某一段時間內某一位連絡人的所有單據;統計出每張單據連絡人的數目等。
b. 查詢也會變得不高效,類型不一致導致隱式轉換,索引失效。
c. 修改起來複雜,需要額外在代碼中寫一段邏輯處理。
d. 有的系統主鍵用的是32位UUID,如果連絡人又10位,那這個欄位長度得是500,有點恐怖。
select *from bill_contact
where contact_idlike'100,%'
or contact_id like'%,100'
or contact_id like'%,100,%';
早前針對這種問題我專門寫過最佳化的方案,資料庫設計中單個欄位多值的處理
方案三恰好是彌補了方案二的眾多確定,開發人員總是擔心表關聯的效能太差,其實是多餘的,因為此時能走到索引。還有一個好處就是可以對連絡人的資訊進行擴充,如是第一連絡人,還是第二連絡人,這是方案二無法實現的。改造對於開發人員來說工作量比方案二要大。
select *from bill_contact a, bill_contact b
where a.bill_id = b.bill_id
and b.contact_idin (100,101);
3. 多值的問題如何抉擇呢?
方案一肯定是不要選的。
方案二適合於對多值列沒有分析統計,沒有查詢。
方案三是我心中理想的方案,雖然它可能會造成一些工作量。
資料庫設計含有多值屬性的表
這樣設計不行啊,主要是在一個表裡的ID是唯一的,後面沒有辦法跟多紀錄。
需要拆開成兩個表,用一個列關連起來。
1: nametb: id int(10),name varchar2(20),serialno int(10)
2: teltb: name varchar2(20),tel number(11)
1表是唯一的,2表是多行的。
可以實現你的目標。
資料庫儲存問題
對於固定表,每個欄位佔用的空間是固定的,對於整數類型的欄位,無論數值是多大、多小佔用位元組數都相同,對於字串欄位,無論長度多長、多短佔用的位元組數都相同,長度超過欄位定義寬度的部分無法儲存,長度低於欄位定義寬度的部分浪費。
對於動態表,裡面字串類型欄位的長度會影響佔用物理空間的大小,超過定義長度的仍然無法儲存,但是儲存內容低於定義最大長度的能節省空間的。
一般情況下,只要表有一個欄位是變成字串類型(VARCHAR),那麼這個表就是動態表,所有的字串類型(CHAR)欄位實際上都是變長的。相反,如果沒有任何一個VARCHAR欄位,那麼這個表就是固定的。
對於動態表,可以節省儲存空間,但是速度會變慢,道理很簡單,特別是修改一個記錄長度發生變化的時候,系統要做許多事情。
以上的動態和固定,都只針對字串類型,如果你的表都是儲存整數,那麼肯定是固定的。無論儲存的整數多大、多小,佔用的空間都是一樣的。
另外,對於允許為空白的欄位,該欄位佔用的空間要多一點,該類欄位的實體儲存體中要多一個記錄內容是否為空白的地方。但是,對於允許為空白的欄位,如果是固定表、或者動態表的非字串類型欄位,其佔用的空間是固定的,只有在動態表的字串類型欄位裡面,為空白的字串要比有內容的字串少佔用空間。