如何规范化SQL数据库

这是在脚本中标准化表的示例。我建议你做这样的事情

e.g Table: tbl_tmpDataDate, ProductName, ProductCode, ProductType, MarketDescription, Units, Value2010-01-01, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 1', 20, 20.002010-01-02, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 2', 40, 40.002010-01-03, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 3', 40, 40.002010-01-01, 'Cola', '02', 'Drink', 'Store 1', 40, 80.002010-01-02, 'Cola', '02', 'Drink', 'Store 2', 20, 40.002010-01-03, 'Cola', '02', 'Drink', 'Store 2', 60, 120.002010-01-01, 'Simiri Gum', '03', 'Gum', 'Store 1', 40, 80.002010-01-02, 'Simiri Gum', '03', 'Gum', 'Store 2', 20, 40.002010-01-03, 'Simiri Gum', '03', 'Gum', 'Store 3', 60, 120.00

您将首先创建日期表：

CREATE TABLE tbl_Date(DateID int PRIMARY KEY IDENTITY(1,1) ,Datevalue datetime)INSERT INTO tbl_Date (Datevalue)SELECt DISTINCT DateFROM tbl_DataWHERe Date NOT IN (SELECt DISTINCT Datevalue FROM tbl_Date)

然后，您将创建您的市场表

CREATE TABLE tbl_Market(MarketID int PRIMARY KEY IDENTITY(1,1) ,MarketName varchar(200))INSERT INTO tbl_Market (MarketName)SELECt DISTINCT MarketDescriptionFROM tbl_tmpDataWHERe MarketName NOT IN (SELECt DISTINCT MarketDescription FROM tbl_Market)

然后，您将创建您的ProductType表

CREATE TABLE tbl_ProductType(ProductTypeID int PRIMARY KEY IDENTITY(1,1) ,ProductType varchar(200))INSERT INTO tbl_ProductType (ProductType)SELECt DISTINCT ProductTypeFROM tbl_tmpDataWHERe ProductType NOT IN (SELECt DISTINCT ProductType FROM tbl_ProductType)

然后，您将创建您的产品表

CREATE TABLE tbl_Product(ProductID int PRIMARY KEY IDENTITY(1,1), ProductCode varchar(100), ProductDescription varchar(300) ,ProductType int)INSERT INTO tbl_Product (ProductCode, ProductDescription, ProductType)SELECt DISTINCT tmp.ProductCode,tmp.ProductName, pt.ProductTypeFROM tbl_tmpData tmpINNER JOIN tbl_ProductType pt ON tmp.ProductType = pt.ProductTypeWHERe ProductCode NOT IN (SELECt DISTINCT ProductCode FROM tbl_Product)

然后，您将创建数据表

CREATE TABLE tbl_Data(DataID int PRIMARY KEY IDENTITY(1,1), DateID varchar(100), ProductID varchar(100), MarketID varchar(300) ,Units decimal(10,5) , value decimal(10,5))INSERT INTO tbl_Data (ProductID, MarketID, Units, Value)SELECt t.DateID       , p.ProductID       , m.MarketID       , SUM(tmp.Units)       , SUM(tmp.VALUE)FROM tbl_tmpData tmpINNER JOIN tbl_Date t ON tmp.Date = t.DatevalueINNER JOIN tbl_Product p ON tmp.ProductCode = p.ProductCodeINNER JOIN tbl_Market m ON tmp.MarketDescription = m.MarketName GROUP BY t.DateID, p.ProductID, m.MarketIDORDER BY t.DateID, p.ProductID, m.MarketID