我有一張item物品表，目前有35個欄位，欄位型態大部分都是int。
前面15個欄位是物品的基本屬性欄位，後面19個欄位是物品的附加屬性欄位。
這個資料表紀錄的物品有40%的物品是沒有額外屬性，60%的物品會有額外屬性。
而且額外屬性欄位不是每一個欄位都會有值。
欄位如下：
item_uid ,item_no ,account , ... ,type_1 ,type_2 , ... ,type_19
因為有機會會再新增附加屬性(增加欄位type_20...)，所以我有考慮把附加屬性(type_1~type_19)另外建立一個表，如下：
create table item_ex_property(type_id int ,type_name varchar(10));
create table item_ex_info(item_uid varchar(20) ,type_id int);
item表大約會有1千萬筆資料，若採取建立item_ex_info，
那item_ex_info的資料量大約會有1億筆(600萬*10~19)這麼多的資料。
現在有考慮三種方案：
1.直接一個item表，若要新增附加屬性時，再alter table增加欄位。但附加欄位並不一定每一個欄位都有值，會有浪費空間的問題。
2.如上，把附加屬性另外建表，這樣新增附加屬性的時候也不會有什麼問題，但會有資料表太大的疑慮。
3.把item表做垂直分割，將附加欄位另外成立一張表。
所以想請教一下大家，在這方面該如何去做選擇呢?
已邀請的邦友 {{ invite_list.length }}/5
keelung0829提到：
3.把item表做垂直分割，將附加欄位另外成立一張表。
這第三方案, 你指的是資料表繼承(database table inheritance)嗎? 如果是, 建議此種方式. 這個子表就含這19+1個附屬欄位. 所以是600萬筆紀錄. 當然要修改商業邏輯來區分哪些項目有這些附屬欄位. 如果這些附屬欄位還可以再分細, 只要商業邏輯處理得來, 就再多一張或一張以上的子表.
當初的想法是把有附加屬性的欄位全部拉出來，連同item_uid成立另一個子表。
再利用left join的方式就可以找出所有的資料了。
只是不知道這想法有什麼優缺點。
至於您提及的NoSQL的概念，只有聽過這名詞，還沒有實際接觸到，這方面我在來去研讀一下，謝謝！
你也可以用NoSQL來處理, 前提是你的商業邏輯必需調整, 但我提到NoSQL並非是建議用NoSQL, 在你提到的"附加屬性的欄位全部拉出來，連同item_uid成立另一個子表", 和我說的值表並不相同, 這種值表常見於許多系統中用來提高資料欄位的擴充性, 也就是說, 不需要修改資料庫, 只需要異動商業邏輯就可以任意增加欄位, 對於你的大量資料, 如果這些欄位的使用率不高, 例如, 19+1個附屬欄位只有幾個欄位會有值, 也可以用這種方法.
hi,bizpro!
你所說的值表是上面所回應的"此資料表就是primary key, parameter, value, valuetype, foreign key"是這個意思嗎?
這方面我真的不太懂你所提的觀念，能否請您舉個例子讓我參考?
不好意思，要麻煩你了！謝謝。
create table item_ex_info(item_uid varchar(20) ,type_id int);
1.這個 table 是記錄 item_uid 有那些附加屬性的「欄位」是吧？
似乎應該還需要在後面加一個附加屬性的「值」,是否正確？
2.針對此題的情境來看
除了「空間的浪費」和「效能的考量」之外
我想還有一點可以列入考慮
就是「新增附加屬性」的「頻率」
如果頻率極低，才有可能考慮做法一的方式
3.提供第四種做法
在 item 表中用1個欄位(ext_prop)表示19個欄位的值
搭配 item_ex_property 使用
舉例如下:
沒有附加屬性時，記錄為 ,,...,, (18個逗號)
第1,3,5,19個附加屬性有值時，記錄為 A,,B,,C,,...,,D(共有18個逗號)
資料表的設計除了理論之外
有時還跟設計者的主觀看法有關
以上意見提供參考
iT邦幫忙MVPantijava提到：
在 item 表中用1個欄位(ext_prop)表示19個欄位的值
千萬別這麼做，若某帳號同時登入兩個session以上，則資料會有 race condition 之虞。
wiseguy提到：
race condition
不解
ext_prop 表示19個欄位是人類的解讀
對資料庫而言他只是另一個欄位
就像 item_no, account 一樣
不知道這麼理解正不正確
若 A session 只要改該欄位第 3 個資料，B session 只要改該欄位第 5 個資料，當順序是：
A read
B read
B write
A write
那麼 B 所修改的第 5 個資料是無效的，等於沒改。這種現象叫 race condition。可以用 transaction 解，但也就無法平行運作，有損效能。
所以為什麼資料庫的書籍一定會寫，不要在一個欄位中放入超過一種資料，不只是為了正規化的意義，也是避免 race condition。
wiseguy提到：
避免 race condition。
了解了
race condition(競速狀態)通常是用來描述多執行緒(threads)的資料同步讀寫問題, 例如兩個執行緒讀取了同一個資料或變數, 由於執行緒的排程(scheduling)關係, 並無法預測(unpredictable)哪一個執行緒會先寫回資料, 因此產生race condition, 要解決race condition, 必需讓執行緒排隊(lock, wait, queue), 就像接力賽交棒一樣, 但還是要防止deadlock的問題, 這也是採用JEE或其它具有強大Transaction Management的架構的重要性, 由架構本身來提供Transaction Management, 使得程式設計的品質能保持一致, 以避免不同功力的程式設計師產生意外的結果, 畢竟不可信的資料是最大的問題.
至於不同程式發出的不同執行緒的資料同步讀寫關係類似race condition, 如在wiseguy大的案例中, B和A互不知對方的讀寫, 即使程式中使用同步讀寫控制(synchronization), 仍然會因設計者的能力產生race condition或deadlock, 但比race condition嚴重的是, 在antijava大的設計中, read出來的可能是不可信的, 更何況是write, 不符合資料庫酸性模型(Atomicity, Consistency, Isolation and Durability, ACID), 可能需要自行維護ACID了, 例如, 寫下每一個write的差異, 然後日後檢查. 另外, 在做Query時, 必需採用全文檢索.
想不到在下一念之差
差點釀成大錯
真是罪過罪過
to antijava:
確實是有少了一個附加屬性的值。
另外，此表新增附加屬性的頻率確實是很低(還是有可能會增加1~3個)，因為在規劃的時候就已盡可能列出所有的附加屬性了。
至於您說到的第四種方法我也曾想過，只是當初有考慮到這樣要統計資料的時候會比較麻煩些，就沒有利用此方法了。不過當初考慮的時候，還真沒想到wiseguy所提及的race condition問題。
我列的3種方法中，有考慮的其實是1跟3的方法，2的方法我想可能會花費更多的資源。只是1跟3我還真不知道該怎麼去做決定！
to wiseguy:
謝謝您的提醒，這方面的問題還真沒考慮到，謝謝！
加上上述的情境
我的選擇優先順序會變成
1->2->3
因為我比較不考慮「資料量太大」或是「筆數過多」的因素
我認為
那是資料庫該煩惱的事
iThome鐵人賽
看影片追技術