如何标准化SQL数据库 [英] How to normalize a SQL Database

问题描述

我想知道是否有人对如何标准化数据库有任何建议。现在，我不是指设计结构，我的意思是如何实际将数据库数据从旧的结构移动到新的，规范化的结构。我知道我可以写一个像PHP脚本，但我想知道是否有一种方法来做它在SQL。特别是MySQL。

I was wondering if anyone had any suggestions on how to normalize a database. Now, I don't mean designing the structure, I mean how to actually move the database data from an old structure to the new, normalized structure. I know I could write something like a PHP script, but I was wondering if there was a way to do it in SQL. Specifically MySQL.

**编辑：有没有人试过像SwisSQL？

** Has anyone tried something like SwisSQL? It's a migration tool, but i'm not sure if it will do what I'm asking.

推荐答案

这里是一个示例，它是一个迁移工具，但我不确定它是否会做我要求的。规范化脚本中的表。我建议你做这样的事情

Here is an Example of Normalizing Tables in a Script. I advise you do something like this

e.g Table: tbl_tmpData
Date, ProductName, ProductCode, ProductType, MarketDescription, Units, Value
2010-01-01, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 1', 20, 20.00
2010-01-02, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 2', 40, 40.00
2010-01-03, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 3', 40, 40.00
2010-01-01, 'Cola', '02', 'Drink', 'Store 1', 40, 80.00
2010-01-02, 'Cola', '02', 'Drink', 'Store 2', 20, 40.00
2010-01-03, 'Cola', '02', 'Drink', 'Store 2', 60, 120.00
2010-01-01, 'Simiri Gum', '03', 'Gum', 'Store 1', 40, 80.00
2010-01-02, 'Simiri Gum', '03', 'Gum', 'Store 2', 20, 40.00
2010-01-03, 'Simiri Gum', '03', 'Gum', 'Store 3', 60, 120.00

您将首先创建您的日期表：

You would Create Your Date Table first:

CREATE TABLE tbl_Date
(
DateID int PRIMARY KEY IDENTITY(1,1)
 ,DateValue datetime
)

INSERT INTO tbl_Date (DateValue)
SELECT DISTINCT Date
FROM tbl_Data
WHERE 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 MarketDescription
FROM tbl_tmpData
WHERE MarketName NOT IN (SELECT DISTINCT MarketDescription FROM tbl_Market)

您将创建您的ProductType表

you would then Create your ProductType Table

CREATE TABLE tbl_ProductType
(
ProductTypeID int PRIMARY KEY IDENTITY(1,1)
 ,ProductType varchar(200)
)

INSERT INTO tbl_ProductType (ProductType)
SELECT DISTINCT ProductType
FROM tbl_tmpData
WHERE 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.ProductType
FROM tbl_tmpData tmp
INNER JOIN tbl_ProductType pt ON tmp.ProductType = pt.ProductType
WHERE 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 tmp
INNER JOIN tbl_Date t ON tmp.Date = t.DateValue
INNER JOIN tbl_Product p ON tmp.ProductCode = p.ProductCode
INNER JOIN tbl_Market m ON tmp.MarketDescription = m.MarketName 
GROUP BY t.DateID, p.ProductID, m.MarketID
ORDER BY t.DateID, p.ProductID, m.MarketID

这篇关于如何标准化SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！