【转】如何设计动态（不定）字段的产品数据库表？
因為最近要用到設計動態量表的功能，找了一篇技術上實現動態設計表字段的文章，借來用用。
原文地址：http://blog.sina.com.cn/s/blog_85295a390101dou0.html
項目組會議上討論的關于不定字段數目的數據庫表問題并沒有結果，今天繼續分析之后發現問題可能還更大。當時討論的結果是可能采用四種技術：
- 動態增加數據庫表字段
- 預留足夠的空白字段，運行時作動態影射
- 用xml格式保存在單字段里
- 改列為行，用另外一個表存放定制字段
?
【一】
現在我們來分析一下四種技術的優劣，不過首先可以排除的是第一點動態增加字段的方法，因為在實際操作時候幾乎是不可能的（sqlserver太慢，oracle索性不支持），基本可以不討論就排除。剩下后三點。
?
【二】
先來討論預留空白字段的方法，基本原理就是在數據庫表設計的時候加入一些多余的字段，看下面的代碼：
?
CREATE?TABLE Sample(
??name varchar(12),
??field0 varchar(1),
??field1 varchar(1),
??fieldN varchar(1)
}
然后看實際運行時候的需要，動態分配字段給系統使用，也許需要一個這樣的結構來描述分配情況：
public class Available
{
??public int CurrentUnusedFieldNumber;
??public Hashtable FieldToRealName;
}
也許某一時刻的數據狀況是這樣的： CurrentUnusedFieldNumber=3, 哈西表FieldToRealName包含內容是("field0"="SomeId", "field1"="AnyName", "field2=IsOk")
現在的問題是如果要配合Hibernate，如何來處理？以上段的數據使用狀況為例子，如果我們的類定義是這樣：
public class Entity01
{
??public string Name;
??public string SomeId;
??public string AnyName;
??public bool IsOk;
}
也許只需要修改一下xxx.hbm.xml，把 SomeId 和 field0 做成對應就ok了。但是在運行時我們怎么知道會有這樣的類定義？除非我們做動態代碼生成，自動編譯也許可以，但是問題也許就到其他方面去了；如果我們不用動態定義，那么類就只能是這樣：
public class Entity01
{
??public string Name;
??public Hashtable ExtraFieldAndValues;
}
使用的時候，用 entity01.ExtraFieldAndValues.setValue("AnyName", "boss") 的方式來引用，也許這樣是修改最少的了，但是問題是Hibernate不支持這樣的方法。
?
?
【三】
再來討論單字段存儲的方法，我們使用這樣的數據庫表定義
CREATE TABLE Sample
(
??Name varchar(12),
??Xml CLOB(102400) // 僅作說明而已
)
然后對應這樣的類定義
public class Entity01
{
??public string Name;
??public string Xml;
??public Hashtable ExtraNameAndValueFromXml;
}
我們的代碼就可以這樣使用：string id = entity01.ExtraNameAndValueFromXml.getValue("SomeId") 了。這樣解決看起來很不錯，不僅不需要Available表，而且看起來Hibernate對它的支持也很完美，但是致命的問題在于：如果保持高效的查詢？除非數據庫系統本身對此有支持，否則就只能用低效的substring或者like做查詢，這在大批量數據中根本就不可行。
是不是折衷一下，把兩種方法的優點和起來？問題有來了：怎么保持兩者之間數據的同步？難道要我們用存儲過程去解析xml內容？
所以，一個兩難的問題，需要我們認真去解決。我們通過認真的需求分析，也許可以減少可變字段的數量，但是只要有一個可變字段或者可變的可能性存在，我們始終要去解決這個兩難的問題。
期待繼續討論。
?
?
【四】
還有一種方法就是改列為行，用另外一個表存放擴展字段，定義可以如下：
CREATE TABLE SampleFields
(
??idSample Integer,
??fieldName varchar(30),
??fieldValue varchar(100)
)
其中idSample關聯到Sample表的id字段（我沒有寫出來）。這樣的話，Hibernate很容易支持，也可以支持Sql的查詢，而且可以支持把內容放到Hashtable中去，看起來是目前最好的方式了。但是在大容量數據的時候，SampleFields表的數據會是主表數據量的N倍（看定制的字段數目多少而定），同樣存在有很嚴重的性能問題。
哪位高人還能再給一個方案？
?
---------------------------------------------------------------------------------------------------------------------------------
--------2005-7-22新增-----------
很多朋友給出了很好的建議，其中蛙蛙池塘?給出了一個表結構，因為看起來不甚方便，我把類圖畫出來如下：
圖所表示的內容簡單來說是這樣：
1。一個很寬的表ProductAttributeValues，包含用到的幾乎所有可能的類型的值，但是每次只能用一個類型的值
2。將可變的列轉為行，存放到上表中
3。為了存放類型定義和一些下拉列表的內容，設計了ProductAttributeTemplates和關聯的其他表
4。ProductListItems中存放Product中所有項的說明和順序。
這種思路其實就是把產品的“知識級”（設計模式用語）和“操作級”都表現出來了，如果要劃分，則圖的左上角三個表屬于“操作級”，其余的屬于“知識級”。wljcan?網友建議我去看《設計模式》的“觀察模式”，我發現上圖其實就是一種和“觀察模式”相似的設計。《設計模式》看了很久都沒能看下去，不過這幾天正在看“觀察模式”，等有心得了，再來看看能不能對上圖的結構修改一下。
怡紅公子?提醒說oracle和sql server對xml字段其實已經不錯了，所以找了一下，但是真的是既產品中恐怕還是不敢用，不知道性能如何。雖然采用xml方式是我最推崇的方法。而且一旦采用xml方式存儲，恐怕就會有changyu網友提醒的數據類型問題，要存一個圖片的話恐怕就歇菜了（當然也不是說xml中就一定不能存圖片）。
不過我現在覺得xml字段還是要的，作為輔助存儲手段，因為畢竟未來查詢起來可能會更方便些，然后結合“觀察模式”也就是類似上圖的方法作為主要的存儲手段，雖然有一些冗余，結合我的使用 NVelocity 解析 PowerDesigner 的cdm文件?，工作量也不會太大。
CREATE TABLE [dbo].[ProductAttributeDataTypes] (
[DataTypeId] [int] NOT NULL ,
[Description] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL?
)?
CREATE TABLE [dbo].[ProductAttributeListItems] (
[LookupListId] [int] NOT NULL ,
[ListItemId] [int] IDENTITY (1, 1) NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL?
)
CREATE TABLE [dbo].[ProductAttributeLookupLists] (
[LookupListId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL?
)?
CREATE TABLE [dbo].[ProductAttributeTemplateCategories] (
[PATCategoryId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL?
)
CREATE TABLE [dbo].[ProductAttributeTemplates] (
[AttributeTemplateId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DataType] [int] NOT NULL ,
[LookupListId] [int] NULL ,
[PATCategoryId] [int] NOT NULL ,
[CustomerHelp] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL?
)?
CREATE TABLE [dbo].[ProductAttributeValues] (
[ProductId] [int] NOT NULL ,
[AttributeTemplateId] [int] NOT NULL ,
[valueInt] [int] NULL ,
[valueStr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[valueMemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[valueBool] [bit] NULL?
)
CREATE TABLE [dbo].[ProductListItems] (
[ProductListId] [int] NOT NULL ,
[ProductId] [int] NOT NULL ,
[ItemDescription] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplaySequence] [int] NOT NULL?
)
不知道大家有沒有看懂這些表之間的關系,這是一個產品目錄的表,產品有不確定的屬性,每個屬性對應的數據類型也不一樣,而且有的是用下拉列表選擇的,有的是 是或否,有的是一段兒文字,這里用的就是數據模板來做的,這是里的一部分數據庫
?
?
---------------------------------------------------------------------------------------------------------------------------------
以前有這樣的一個需求,不考慮像京東或者淘寶這樣分類下有子分類的情況,只考慮一層分類的情況下,可以隨便添加分類,可以任意給商品添加屬性,而不需要更改表的結構. 于是設計了一個這樣的結構,實現還是可以實現,一直在用,但是在操作上比較麻煩,大家討論下有沒有更好的方式.?
----------------------------- 以下是幾個關聯的對象 省去了 getter/setter 方法 ------------------?
Field.java 屬性?
Java代碼
- public?class?Field{??
- ???private?Integer id;??
- ???private?String name;?// 屬性名稱
- }??
ProductField.java 商品屬性?
Java代碼
- public?class?ProductField{??
- ?????private?Integer id;??
- ?????private?Field field;?// 屬性對象
- ??????private?String value;?// 屬性值 這里的值不管是整數 小數 還是什么類型都是用 String 類型
- } ?
Product.java 商品表?
Java代碼
- public?class?Product{??
- ???private?Integer id;??
- ???private?String name;?//商品名稱
- ???//根據商品 加載這個商品的所有屬性 和屬性值
- ???private?Set productFields =?new?HashSet();//商品屬性集合
- }??
Type.java?
Java代碼
- public?class?Type{??
- ???private?Integer id;??
- ???private?String name;?//分類名稱
- ???//通過類型 可以加載這個類型下的所有商品??一種類型對應多種商品
- ???//(暫時不考慮一種商品屬于多種類型 例如手機屬于電器類,也屬于通訊類產品)
- ???private?Set products =?new?HashSet();??
- }??
----------------------??Hibernate 配置文件就不帖出來了,以下是由POJO和hbm配置文件生成出來的表 示例-?
//表關系?
Type???????
id name????
1??電腦??????
3??手機???????
4??腦殘????????
?????????
Product?
id??name??typeid?
1???SONY????1(fk)??
2???DELL????1?
3???韓國豬???4?
4???叁欣?????3?
Fields????
id name???
1??顏色?
2??重量?
3??型號?
...?
ProductFields?
id pdctid??fieldid???value?
1??(fk)1???(fk1)1????紅色?
2??????1????????2????500g?
3??????2????????3????XYZ?
4??????2????????1????白色?
5??????3????????4????1000.00?
--加載的測試數據?
- +++++++++ 商品信息 ++++++++??
- Name:Sony??
- 價格 -?2000.00
- 型號 - YY-1939
- 名稱 - 鎖你牌手機??
我要補充的是，當商品越來越多的時候，屬性的增加也是自然的，有肯能屬性增加到1000種，?
那么我在后臺管理 新增加一個商品，給它添加屬性時，這時就需要從已經存在的屬性中選出?
它所需要的那么10種或者20種屬性。?
我的想法是，程序處理中：?
1.在頁面輸出可選擇的屬性時按屬性名稱排序，這樣可能選擇快一些。?
2.提供屬性搜索,看數據庫中有沒有這么一屬性，如果有直接使用，如果沒有就添加?
3.修改此數據庫表，屬性應該和大的類型相關聯，作為常用屬性。 次要屬性可以從后續的屬性列表中選擇。?
動態列性能上確實可能會有點問題。比如你要統計：紅色的手機的一個月銷售額就需要至少在四個表（類型、產品、屬性、銷售）進行聯合查詢。?
我以前遇到類似問題，而且時間上不允許更改設計了，當時只是采取了一些簡單的處理方式，比如針對需要的統計生成中間表，對庫進行索引優化，根據時間進行分表等等。不過我個人感覺出問題的地方往往是一些特定統計，如果你能確定或基本上確定最終這些表的統計查詢的方式，我覺得問題不大。你可以預估下數據量，假設半年后的運營， 類型、產品、屬性、銷售等數據大概是多少，聯合查詢的總記錄數是多少。如果這個數據比較恐怖，那你再可以考慮更換其他方式。
?
復雜商品的分類，類似淘寶的分類?
1.每類商品有無限級分類?
2.每個商品可能會有交叉分類?
3.每類商品的擴展屬性不一樣?
比如：?
夾克的擴展屬性為?
款式： 拉鏈夾克 風格： 休閑 品牌： other/其它 適合季節： 春秋 尺碼： M L 顏色： 其它顏色 質地： 純棉?
主板的擴展屬性為?
品牌： 微星/MSI 類型： Socket478 芯片組： Intel 845 平臺類型： Intel平臺 寶貝成色： 8成新?
這些擴展屬性都會動態的變化?
那么問題來了：?
1.全文搜索如何合理建立？?
2.可能后臺擴展屬性表是否需要動態建立？?
3.如果單件商品屬于交叉分類的話，查詢結果記錄重復是否需要？?
4.高效的無限級分類算法大家可否指點一二，這個困惑了我很久了？?
不求完整解決，給個思路也成
每個商品都是一個xml文件 每個xml文件有一個ID 每個xml文件可以采用這樣的結構?
?
<名稱/>?
<屬性/>?
<屬性/>?
......?
?
當檢索商品的時候 比如用Socket478芯片組檢索 就檢索出所有含有Socket478芯片組屬性節點的xml文件. 用一個xql語句就可以搞定. 返回的結果是xml文件 可以用xsl進行處理 然后用css顯示
這類問題（無限級別分類，可以交叉分類）很難。
正是現代 tag，分類時代的熱點問題。我也考慮調查了很久。
如果解決得好，就可以被收購了。
xml 解決起來確實比 relation table 容易一些。xml全文檢索也比較容易做。
我想，winterwolf會跳出來，終于等到了。
不過，xml也有一些限制。如果能有一種專門描述這類問題的數據結構就好了。
我想到過幾種數據結構。不過都沒有想透。
Multiple Key Hashmap。多維數組。等。
xml確實是一種解決的辦法，tianxinet的子表方法有點繁瑣了，擴展屬性有很多表也帶來了維護的困難性了。。如果像淘寶的商品分類那樣的話，你的子表數量是很驚人的。。。。?
繼續關注中
?
?
---------------------------------------------------------------------------------------------------------------------------------
前幾天有人問了我一個這樣的問題，因為時間的關系，我當時嘗試做了幾種回答，比如將產品先分大類，為每個大類設計一個產品表，在產品表中包括該類的基本屬性，并預留一些字段作為擴展屬性，對于同一大類不同的產品，考慮增加擴展表。不過這個答案似乎沒有得到認可。認真一想也是，如果這樣，表改有多少，查詢結構又該多復雜。
??????電子商務，尤其是B2B和B2C的電子商務平臺，有著自己的特殊情況的。首先，它的產品及其廣泛，差不多就涵蓋了世間所有可以出售的商品，其次，每種商品的屬性共性也不太可能分析和抽取。
??????今天在網上發現一個網友的blog(http://hi.baidu.com/ifos/blog/item/5cf3de1f03dd7b67f724e4ea.html)，他提出了四種設計思路，
beyond_dream???寫道
方案一。
就一個產品表 product，然后這個表里包括所有的產品屬性，每個屬性用一個字段表示。
方案二。
還是只用一個產品表 product 。
與方案一不同的是，私有屬性設置為一個字段 Private_Attribute ，
然后每個產品的多個私有屬性都放這個字段里，并且用一個分隔符號隔開
比如書籍，就是 它在 Private_Attribute 字段里 的表示就是 ：
出版社||||作者||||出版日期
方案三；
產品表 + 私有屬性表 + 私有屬性值 表
產品表 里 就包括一些產品的公共屬性
私有屬性表 里 設置私有屬性的名稱 ，比如出版社 、作者 、出版日期
私有屬性值 表 里就是 每個產品 私有屬性的值
例如：
產品表：?
product_id = 1 ; product_name =《ajax實踐》
私有屬性表：
Attribute_id = 1 ; Attribute_name = 出版社
Attribute_id = 2 ; Attribute_name = 作者
私有屬性值表:
id = 1 ; product_id = 1 ; Attribute_id = 1 ; Attribute_value = 清華出版社
id = 2 ; product_id = 1 ; Attribute_id = 2 ; Attribute_value = 老外
方案四；
每個不同類型的產品單獨設計一個數據庫，比如一個書籍的數據表 product_book，一個MP3的數據表 product_mp3
????????看了一下，突然萌生出一些想法來，愿與大家交流討論。
???????首先想的是，電子商務產品表設計的最佳是由哪些因素決定的。個人認為，主要包括高效率的查詢性能以及可易擴展的設計。我們于是從這兩個方面分析上述四種設計，第一種方案幾乎沒有可擴展性（列的擴展遠遠不夠于包含所有產品不同的屬性）；第二個方案看上去可擴展性不錯，不過它的屬性就全部以純文本的樣式存儲，查詢效率自然想到差；第三種方案看上去是一個折中，實際上它是產品、屬性、屬性值的笛卡爾積了，數據量將非常巨大，根本不適合大型的電子商務平臺，因為查詢效率會很低，并且對于結果的拼排將是很大的代銷；第四種方案也許擁有最好的擴展性，但是如果對于跨產品的查詢，也將是低效率的。
???????這么看來，這將是個NP了。而實際上呢？阿里巴巴做得很好。我不知道阿里巴巴是如何做到的，但是在仔細看了阿里巴巴的網站后，個人覺得有些東西其實妨礙了我們的思路。
????????列下幾個問題，可供大家思考：
?????1. 是不是產品的所有可能屬性都屬于查詢條件？ 看看阿里巴巴，它的查詢條件涵蓋所有屬性了么？
?????2. 是不是所有屬性都是具有獨立性的存在的?換句話說，如果一個物品有幾百上千種可列屬性，是否我們需要將它每一個屬性作為單獨存在的屬性來描述？
?????3. 除了傳統的數據庫的SQL查詢，我們又是否可以借助某些數據庫的特性或者說其他的查詢技術呢？（比如XPATH）。
?????4. 客戶只輸入了一個模糊條件，是不是就一定意味著需要在所有的產品信息中查詢呢？（是否可以識別用戶習慣，是否又可以像傳統搜索引擎一樣進行關鍵字排行呢？）
?????5. 用戶輸入的關鍵字查詢，又是不是對產品的所有屬性有效呢？還是只是涵蓋了產品的關鍵屬性？
?????6. 客戶的查詢真的是像傳統信息系統一樣知道精確的所有結果么？還是只想知道最佳的結果？
??????其實有興趣的朋友，可以上淘寶、上阿里巴巴，看一看，也就可以給這幾個問題列出答案了。
??????好了，雖然我不是電子商務行業，也沒有真實面對過這類問題，談到這里，就大概說一下我對電子商務平臺產品物流模型設計的思路吧，有興趣的朋友可以參考驗證一下，也可以交流一下。
1. 對產品按照大類進行區分，每個大類有一個產品表。產品表有該大類產品最基本的關鍵屬性信息（應該控制在十個以內，這些屬性，也被用作關鍵字查詢索引），而接下來，又有一些預定義的擴展屬性（命名如customized1, customized2，等等），這些擴展屬性有一個重要的前提，就是它們的屬性值都是列表選擇的，而不是自由輸入的（當然這些選擇項是可以在另外的表中定義的），這些屬性是用作在詳細的高級查詢中使用的，使用選擇而不是自由輸入第一是可以提高索引效率，第二是可以避免因為字符差錯引起的歧義，第三是可以使用區間值。每個不同的產品小類有不同的customized定義。最后有再有幾個字段，則存放其他的由客戶特性帶來的屬性值，但是是以xml的形式存放，可以方便使用XPATH來提升效率。
??????當用戶在統一的文本框輸入模糊查詢條件時，可以通過事先建立的關鍵字索引或者優先排名或者用戶習慣來確定首先尋找的產品大類范圍。這樣就將查詢首先限制在幾個主要的產品中。然后通過基本的屬性進行基于SQL的比較查詢。同時列出查詢結果所對應的產品小類。用戶可以進入產品小類進行更詳細的查詢，這個時候的查詢就包括了由customized字段定義的一些區間值或者選項值。用戶同時又可以輸入一些特別的條件（在這個區間外，也許某類產品本身的特性，如同pconline上不同產品的詳細特性），這個時候就可以運用XML技術來進行最后一些字段的過濾與篩選了。
????其實整個這個設計思路是在確保客戶的查詢有效性、響應時間及數據庫性能及結構的可擴展性上進行了一些折中和考慮，在不損失表結構的可擴展性上，既保證了每張表的表空間不會過大，也同時保證了查詢的最優有效性。它其實還需要借助其他的一些技術，比如搜索關鍵字識別及優化、結果排名、用戶習慣及模糊行為分析、基于XML的查詢等。
?????蠻想聽聽來自互聯網電子商務行業的朋友的一些想法，歡迎交流討論。
http://raylinn.javaeye.com/admin/blogs/260407?
ms 和我以前想的這個有點像。。。 固定的屬性和模糊的介紹都通過lunce來搜索。。
問題補充
我也是想用多個表，多個表有點不好的地方，就是如果用戶在A表買了一件產品，又在B表買了一件產品，那么到最后統計用戶買過的產品時，雖然可以從下訂單那個表讀出來，但是感覺有點亂~~~
問題補充：
產品類別表(類別id,名稱,...)?
產品表(產品id,名稱,規格,...)?
產品屬性表(產品屬性id,產品id,屬性id,值,...)?
這種方法行不行？
問題補充：
產品表(產品id,名稱,規格,...)?
產品屬性表(產品屬性id,產品id,屬性id,值,...)?
就說這兩個表吧，當插入一件產品的時候，屬性應該是直接去到產品表的，如果加了一個屬性表，那么插入的時候，?
難道可以插入一個產品id在產品表，然后又插入屬性在產品屬性表嗎？
方案一（推薦）：在商品類型已知的情況下，為每類商品定制一個類。class 手機，class 筆記本?
方案二：如果商品類型位置，那就用一個通用的商品類，里面放個property
?
?
方案一：商品屬性定義表(存放xml格式定義)，商品表(基本字段+屬性定義ID+屬性xml描述)，有了xml格式和xml描述，其他的xml數據解析和前臺展示就可通用處理了，細節你可以再看看。
方案二：如上。?
只是打開想了想，不知道能否幫到你
轉載于:https://www.cnblogs.com/jexwn/p/5505747.html
總結
以上是生活随笔為你收集整理的【转】如何设计动态（不定）字段的产品数据库表？的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 栈溢出笔记1.11 SafeSEH
- 下一篇： 二叉树入门题