基于冗余數據動態生成數據表的設計及實現

盧江興

(肇慶市第一中學，肇慶 526060)

基于冗余數據動態生成數據表的設計及實現

盧江興

(肇慶市第一中學，肇慶 526060)

數據庫設計規范化理論與軟件開發中實際的數據庫設計有差別，根據開發中的實際問題，提出基于反規范冗余技術的數據庫設計方法，并就需使用眾多小表格的教務管理系統統進行剖析，提出動態增加表格的設計，在教學管理中付諸實踐取得較好的效果。

數據庫原理；冗余；動態

0 引言

在教學管理中，會涉及到較多相似小表格的應用，例如：學生成績表、選修課成績表、文科、理科成績表、職業學校中不同專業的成績表，或者隨著時間學期的變更，會不斷產生上學期成績表、下學期成績表等。這些表格都有相類似的結構，行、列數都較少，數據量不大，需實現相類似的功能，但是各表格的字段名稱、字段數目多少存在差異。如果每個表都使用SQL語句create table實現添加表操作會很麻煩，首先不同的表有不同的結構，字段需要定義屬性，而且管理不斷新添加的表也需要額外的開銷。

能否通過數據庫的設計，使得對一張大表添加數據行就能實現類似于新增表的功能，如果可行，應該怎么設計？

為了方便應用設計、開發簡單快捷，可以作一些簡單的假設：

（1）大表中的字段全部為字符類型，如果在實際應用中應該是數值型的，這由程序才檢驗并實現，數據庫設計不作規則限制。

（2）設置一個數據表允許動態新增加字段數的上限（例如：15），這個數字可以增加，但是需要在設計時要確定。

（3）為每一個新增表設置一個字段計數cols，每增加一個字段，cols+1，直到上限值。

（4）數據表可以刪除字段，為簡化設計，刪除字段時，cols值不變，所以，當cols達到上限值時，可能會存在：新增數據表的實際字段數<上限值的情況。

這實現上述功能，需設計以下3張表：

tname:id,tname,cols //用于定義新增的表名，新表已有字段數

tStruc:id,tnameid,oShowName,iShowName//用于定義新增表的字段名稱

btable:id,tnameid,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15//大表

1 創建新表操作

Insert into tname(tname,cols)values(‘新表名一’,0);//此時，新建表字段數=0,假設添加后產生的id=10

2 為新表添加字段

Update tname set cols=cols+1 where id=10 //10為上述id

Insert into tStruc(tnameid,oShowName,iShowName)values(10,’第一個字段’,cols);

//oShowName為對用戶可見的字段名，iShowName為數值型字段，只增不減，添加每一個字段為1，第二個字段為2，如此類推，作為指向btable的字段索引。

完成字段添加后，tStruc如下表類似

iShowName字段使用數值型，通過+1操作，方便字符”A”+iShowName字段連接后，可以指向btable表的A1、A2、A3、A4 不同字段。

表1

3 為大表添加數據

查詢tStruc表，求目標表的字段內容，生成SQL語句，完成添加操作

Select*from tStruc where tnameid=10 Sql=“insert into btable(“

do while not rs.eof

sql=sql+“A”+trim(rs(“iShowName”))+“,”

rs.movenext

loop

sql=sql+“tnameid)values(“ ...

通過循環語句后，sql語句為：

insert into btable(A1,A2,A3,A4)values(...

4 刪除字段

Delete from tStruc where id=3

如果將某一字段（例如：數學）刪除，后又重新添加字段（數學），按照上述規則，cols值只增不減，新增字段后，tStruc如下表類似：

表2

可見，對新增的“數學”字段添加數據時，會添加到A5字段，而之前是A3字段，兩者是不同的。這時通過上述循環語句后，生成的sql語句改變為：

insert into btable(A1,A2,A4,A5)values(...

Cols的上限最大值：colsMax

colsMax與btable表的字段數有關，等于btable表的字段數-2，而btable表在設計前應該確定，所以cols?Max是確定的。

cols值只增不減，也是簡化程序設計的手段。

5 顯示大表數據

（1）顯示數據同樣需要查詢tStruc表，生成SQL語句。

（2）查詢btable的數據需要使用oShowName作為別名，不直接使用A1、A2作為字段名Select*from tStruc where tnameid=10 Sql=“select“

do while not rs.eof

sql=sql+“A”+trim(rs(“iShowName”))+“as“+rs(“oShowN?ame”)+“,”rs.movenext

loop

sql=sql+“tnameid,id from btable where tnameid=10“

通過循環語句后，sql語句為：select A1 as英語,A2 as語文,A4 as信息,A5 as數學,tna?

6 修改及刪除大表數據

可以直接對根據btable的id進行操作。

為方便使用，可以將上述過程寫成存儲過程或者封裝成為類。以下是一個存儲過程的例子，將由分隔符分隔的多個字符作為字段添加到tStruc表。create proc AddStrucName

@fieldname varchar(8000),

@Splits varchar(100),

@tname varchar(100)as

begin

Declare@i Int

Declare@id Int

季經理是個禿子，忌諱很多，有人說兔子跑了或燈泡亮了，季經理的臉色便陰得像要下雨一樣。季經理經常戴一頂帽子。這天，忽然一陣風把帽子吹落了，季經理以為是什么預兆，心里很不爽。開會時，恰巧有個生馬蛋子把季經理的姓念錯了，念成了李某某，季經理勃然大怒，終于發火了：誰敢把老子頭上的帽子摘了，老子就把他的職務擼了。

Declare@cols Int

Declare@fn varchar(100)

Set@fieldname=RTrim(LTrim(@fieldname))

Set@i=CharIndex(@Splits,@fieldname)

While@i>=1

Begin

@fn=Left(@fieldname,@i-1)

Select@id=id from tname where tname=@fn

Update tname set cols=cols+1 where id=@id

Select@cols=cols from tname where id=@id

Insert into tStruc(tnameid,oShowName,iShowName)values(@id,@fn,@cols);

Set@fieldname=SubString(@fieldname,@i+1,Len(@fieldname)-@i)

Set@i=CharIndex(@Splits,@fieldname)

End

end

go

例：使用存儲過程對表2017高一3班，添加字段“語文，數學，英語，信息，化學”

exec AddStrucName"語文，數學，英語，信息，化學",",","2017高一3班"

按照數據庫設計理論，一個好的數據庫設計應當滿足第3范式的要求，減少數據冗余。但是，在實際生產中，有時為了提高某些查詢或應用的性能而破壞規范規則，即反規范。在進行反規范操作之前，要充分考慮數據的存取需求、常用表的大小、一些特殊的計算數據的物理存儲位置等。要使設計的數據庫表結構有一定的可擴展性、靈活性和動態特性。使數據庫系統能夠滿足未來功能升級的需要，可以在設計過程中充分考慮使用反規范的冗余技術。

上述btable表使用預留字段的設計，不滿足第3范式，會產生冗余數據，但是，由于每一個的數據量不大，而且能方便程序設計，所以犧牲小量的存儲空間，換取程序簡化設計，還是可取的。

[1]數據庫系統基于冗余的數據庫設計方法.http://www.92to.com/xinli/2016/01-31/1112398.html.

[2]Sql Server存儲過程對分割字符串.http://blog.csdn.net/blocksom/article/details/7676002.

[3]宋金玉，陳萍，陳剛.數據庫原理與應用.ISBN：9787302372424.

Abstract：Database design specifications have different database design theory and practice in the development of software,according to the actual problems in the development,puts forward a database design method based on redundancy technology standard,and analyzes the require?ment using a large number of small table educational management system are,puts forward the form design of dynamic increase in teaching management in practice has achieved good results.

Keywords：Database Principle;Redundancy;Dynamic

Design and Implementation of Dynamic Data Table Generation Based on Redundant Data

LU Jiang-xing
（Zhaoqing No.1 Middle School,Zhaoqing 526060）

1007-1423（2017）25-0045-03

10.3969/j.issn.1007-1423.2017.25.012

盧江興（1970-），男，福建永定人，碩士，高級教師，研究方向為計算機網絡、計算機安全、教學管理軟件開發

2017-06-27

2017-08-15