行政区划数据数据库的设计(一)

Author:水如烟

这是《HOW TO:利用Excel的QueryTable下载网上数据  》的续篇。
代码写得怎么不好，至少给出了一种途径，所以可以冠名HOW TO；数据库设计就不能HOW TO了，只能是给出自己的思维供各位指正，仅供参考。

关于数据库的设计我是没有看过有关书籍，全是凭自己领悟感觉，零散的吸收网上的介绍。这里说设计，纯粹是非专业者的设计，所以下面的设计“动作”，读者不可轻易接受或相信。

在上一文中,已经实现了从网上下载数据到本地的DataTable.

行政区划数据，自2002年6月30日起至2005年12月31日，已经有8个版本的数据。
每个版本的数据至少有3500条，后期版本在前期版本基础上增加区划码或是修改区划名称。

我们的目的是查询某时间某个区划码是否存在，若存在当时的区划名称是什么。


数据库基于SQLServer2005,用它也算是接触学习吧。

2005有了架构，我当是Framework里头的命名空间。

一般的，建立一个数据库时,我先建立如下架构(命名空间)：
Base，基础的；
Edit，编辑的；
FUNCTION，一般函数或存储过程；
Action，库内的处理函数或存储过程；
Program，外部程序调用的函数或存储过程。

用命名空间的好处，不只是带来库文件组织的便利，还可以方便的设计用户权限。

在表名列名的命名方面，我直接用中文，为的是不写文档，从中文名字中看它的意义。这个好多人看着肯定就不舒服的了。

在具体“说”之前，我先将目前的设计和效果以图片形式展现出来，给各位一个直观印象，具体说的另一文。

数据库组织：


表关系：

查询效果：


某一区划码不同时期的意义：

.

 

行政区划数据数据库的设计(一)

 

在具体设计数据库之前，我们应该先熟悉“数据对象”的特性。在这里，面对的数据对象就是区划码，它的格式如下：

 

六位数字名称

如：

340302 东市区

 

它的特性如何？它是国家的权威性的数据，由不得我们人为的臆想，所以必须寻求权威性的也就是权威发布机构的说明。就算里面的数据是错的，对于我们使用方来说，也须忠实的使用“错误”数据。

 

那几个版本的数据我都看了，也比较了更改过名称的数据，一部分是更名，一部分它真是修正了前期版本的名称。

 

更名部分还有一种情形，就是两个字的，只是中间空格数不一样。这种情形我进行了处理。在下载网上数据后，凡名称字串里有空格的，在写入本地数据库前，都去掉。

 

        Private Sub AddRow(ByVal table As DataTable, ByVal line As String)

            line = line.Trim

            If line.Length < 7 Then Exit Sub

 

            Dim tmpCode As String

            Dim tmpName As String

 

            tmpCode = line.Substring(0, 6)

            tmpName = line.Substring(6).Trim

 

            If Not IsNumeric(tmpCode) Then Exit Sub '前六位需是数字

 

            '去掉名称中间的空格

            table.Rows.Add(New String() {tmpCode, tmpName.Replace(" ", "")})

        End Sub

 

●

 

六位数字的区划码相关规约有：

《关于印发《关于统计上对全国行政区划代码编制、使用和管理的暂行规定》的通知》

《中华人民共和国行政区划代码》国家标准（GB 2260-1995）

 

代码结构：

我还是比较了数据，发现：

首组，代表省；

中组，其中01-20、51-99表示省直辖市，21-50表示地区（州、盟）；

尾组，01-20表示市辖区或地辖区，21-80表示县（旗），81-99表示省直辖县级市。

 

在设计数据库时，我考虑了这种动态定义。如果自己理解不对，可以修改。

 

这篇简单说了设计前的基本的准备工作，确定了设计中应把握的基本原则。

下篇转入技术上的设计。

 

我以前从来不说，只写代码，就算也有“系列”的，也全是代码的。基础差，脑袋中没几个专用术语，所以是“说”的话会误导读者，特别担心会误导初学者。所以一再强调“仅供参考”。别信我说的，但可以参考我的思维方法。

 

写这个“系列”，一是可以总结一下自己的数据库设计方面的“想法”，二是代码贴在自己的BLOG上当作备份，三呢从头到尾的写出来，给初学者了解一个解决方案的大概过程。

 

文字的排版方面我不大热心，以后找个时间熟悉一下吧。现在只是多用换行，尽量的使界面清晰一些。

 

●

 

转到“说”技术，真头疼了。我不能边从MSDN上找术语边说，只能用自己的“术语”了。

 

我只说自己的做法，不跟别的方法进行对比，对比方法优劣不是我的能力所及。我甚至不知道如何有效使用索引。

 

整个数据库的设计核心是引入了“稳定数据日期”概念。所有的函数包括存储过程都围绕这个“稳定数据日期”进行操作。

如果不了解这个概念，就不容易读懂这个数据库设计。或许专业设计上已有这种模式，但是我不知道，所以下面还是说自己的。

 

一项数据，在一定的时间内它的属性值（有意义的字段值）如果相对的稳定，并且要反映不同时期它的属性值，就可以引入这个概念。

除了这里的区划码数据适合这种情形，日常中还有职员的岗位变动、考核的参照标准等等，在法津上约定的还有职员的年龄退休年限、工龄退休年限、个税基数等等。适用范围很广。

 

针对这种数据的“稳定数据日期”，定义为8位的nvarchar，通常的日期转换为八位数字串，如“2006-9-19”转换为“20060919”。它具有一个默认值，“Current”。

“Currnet”总影射到最近的时间，比如我现在要查2008年8月8日的数据，虽然实际时间还没到，它还是一样的起作用，指向了2008年8月8日的数据。

 

凡使用这个概念的表，它都有两个字段，如图中所示的“截止日期”和“起始日期”，这两个字段的类型都是“稳定数据日期”类型。这种表至少有一个约束，指定“截止日期”不小于“起始日期”。

“起始日期”表示这项数据自这个日期起生效；“截止日期”表示这项数据生效的最后日期。如果“截止日期”值是“Current”，那么它适用到现在，并且随时间推移一直生效下去。

 

这种数据具有时间顺序的强制性。如果表中记录已存在起始日期为20060101的数据，再插入或修改2005年的数据，那是不充许的，也不会成功。

 

这种表由“截止日期”和数据的特定外键组成主键，保证某一时间内（日期范围）它的记录项是唯一的。

 

当某一数据变动了，如果表中有相应的特定记录，它将相应记录载止日期为“Current”的记录进行修改，把载止日期的值改为最新数据的起始日期的前一天；表中存在还是不存在的相应记录，最后都插入一条新记录，新记录的截止日期值为默认值，也就是“Current”。

 

当某一属性在某一时间之后停止使用了，将相应记录载止日期的“Current”值修改为截止有效日期。那样，查询该日期之后日期的数据中，不会再有反映这个属性的数据。其实质是取消了该记录的“Current”影射。

 

在这里，区划码的数据最旧的起始日期为20020331，所以只能查自2002年3月31日起的数据，再前的查不到了。所以初始化数据库时，应考虑把它延后至某一个日期，比如19491001，不过那时香港还不是特别行政区哪。

 

 

这篇介绍了数据库设计的核心，是引入了“稳定数据日期”概念。

这一篇主要说基本信息也就是“[Base].区划码”和“[Edit].区划码情况”的设计，以及对它操作的函数“[FUNCTION].区划码情况”和存储过程“[Program].增添记录”。

 

当本“系列”结束时，我将提供整个数据库的生成脚本和程序代码供下载。

 

设计数据库时，针对某一信息建表，我把握的准则是先弄清楚那些是主体表，那些是附属表。主体是信息承载的宿主，附属是为方便查询主体信息引进的。

 

如这里的表，图中的“基本信息”就是主体的，“属性信息”就是附属的。没有附属的也可以，只是查询起来不方便。因为附属的是为查询服务的，在实际使用中，附属的反客为主，查询中基本是针对附属表及相关的函数、存储过程；信息能量的利用发掘，也体现在附属的上面。主体的通常只在数据编辑时用到。

 

设计初期重点考虑主体表。附属表可以在使用中再设计再增加也不迟。在我的设计过程中，开始也仅只有文首提到的四个对象。在这些文中，我尽可能反映我的设计过程的原貌，让各位能够把握这个过程中我的思维。原本也有把自己想法记载下来的目的。

 

△

针对数据本身，也要分析主体和附属。附属的作用于主体，分开处理。看过网上的一些关于数据库设计方面的文章，针对不同的场境应用不同的模式，我记不住，也悟不透，只好还是跟着感觉走。

 

下面入“正题”。

△

建库RegionalCodeWorks，Works尾缀吸收了SQLServer2005的示例命名做法，加上它觉得整个库都有生机了，特舒服。

脚本是针对我的机器的。如使用这个脚本要修改成本地参数，最好在Management上直接建。

 

USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'RegionalCodeWorks')
BEGIN
CREATE DATABASE [RegionalCodeWorks] ON PRIMARY 
( NAME = N'RegionalCodeWorks', FILENAME = N'g:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATARegionalCodeWorks.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'RegionalCodeWorks_log', FILENAME = N'g:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATARegionalCodeWorks_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
END

 

△

建命名空间（也就是架构）：

 

以下的脚本可以直接使用了。

 
USE [RegionalCodeWorks]
GO
/****** 对象: Schema [Program]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Program')
EXEC sys.sp_executesql N'CREATE SCHEMA [Program] AUTHORIZATION [dbo]'
 
GO
/****** 对象: Schema [FUNCTION]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'FUNCTION')
EXEC sys.sp_executesql N'CREATE SCHEMA [FUNCTION] AUTHORIZATION [dbo]'
 
GO
/****** 对象: Schema [Edit]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Edit')
EXEC sys.sp_executesql N'CREATE SCHEMA [Edit] AUTHORIZATION [dbo]'
 
GO
/****** 对象: Schema [Base]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Base')
EXEC sys.sp_executesql N'CREATE SCHEMA [Base] AUTHORIZATION [dbo]'
 
GO
/****** 对象: Schema [Action]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Action')
EXEC sys.sp_executesql N'CREATE SCHEMA [Action] AUTHORIZATION [dbo]'

 

△

建默认值：

 
USE [RegionalCodeWorks]
GO
/****** 对象: Default [Base].[RegionalDateDefault]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Base].[RegionalDateDefault]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
EXEC dbo.sp_executesql N'/****** 对象: Default [Salary].[SalaryDateDefault]    脚本日期: 09/17/2006 14:44:13 ******/
--USE EmployeeWorks
CREATE DEFAULT [Base].[RegionalDateDefault] AS N''Current'''
GO

 

△

建自定义数据类型：

 

USE [RegionalCodeWorks]
GO
/****** 对象: UserDefinedDataType [Base].[RegionalDate]    脚本日期: 09/17/2006 19:14:50 ******/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'RegionalDate' AND ss.name = N'Base')
CREATE TYPE [Base].[RegionalDate] FROM [nvarchar](8) NOT NULL
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Base].[RegionalDate]' , @futureonly='futureonly'
GO
/****** 对象: UserDefinedDataType [Base].[RegionalCode]    脚本日期: 09/17/2006 19:14:49 ******/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'RegionalCode' AND ss.name = N'Base')
CREATE TYPE [Base].[RegionalCode] FROM [nchar](6) NOT NULL
GO

 

△

建表[Base].区划码

USE [RegionalCodeWorks]
GO
/****** 对象:  Table [Base].[区划码]    脚本日期: 09/19/2006 19:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Base].[区划码](
    [区划码ID] [smallint] IDENTITY(1,1) NOT NULL,
    [区划码] [Base].[RegionalCode] NOT NULL,
    [一级]  AS (substring([区划码],(1),(2))),
    [二级]  AS (substring([区划码],(3),(2))),
    [三级]  AS (substring([区划码],(5),(2))),
 CONSTRAINT [PK_区划码] PRIMARY KEY CLUSTERED 
(
    [区划码ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_区划码] UNIQUE NONCLUSTERED 
(
    [区划码] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_bindrule @rulename=N'[Base].[RegionalCodeRule]', @objname=N'[Base].[区划码].[区划码]' , @futureonly='futureonly'

△

建表[Edit].区划码情况

USE [RegionalCodeWorks]
GO
/****** 对象: Table [Edit].[区划码情况]    脚本日期: 09/19/2006 19:04:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Edit].[区划码情况](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [区划码ID] [smallint] NOT NULL,
    [截止日期] [Base].[RegionalDate] NOT NULL,
    [名称] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [起始日期] [Base].[RegionalDate] NOT NULL,
 CONSTRAINT [PK_区划码情况] PRIMARY KEY CLUSTERED 
(
    [区划码ID] ASC,
    [截止日期] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[区划码情况].[截止日期]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[区划码情况].[起始日期]' , @futureonly='futureonly'
GO
ALTER TABLE [Edit].[区划码情况] WITH CHECK ADD CONSTRAINT [FK_区划码情况_区划码] FOREIGN KEY([区划码ID])
REFERENCES [Base].[区划码] ([区划码ID])
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [FK_区划码情况_区划码]
GO
ALTER TABLE [Edit].[区划码情况] WITH CHECK ADD CONSTRAINT [CK_区划码情况] CHECK (([截止日期]>=[起始日期]))
GO
ALTER TABLE [Edit].[区划码情况] CHECK CONSTRAINT [CK_区划码情况]
 
 

△

建函数[FUNCTION].区划码情况，作用是查询指定日期的[Edit].区划码情况中的所有数据。

USE [RegionalCodeWorks]
GO
/****** 对象: UserDefinedFunction [FUNCTION].[区划码情况]    脚本日期: 09/19/2006 19:06:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [FUNCTION].[区划码情况] 
(   
     @当前时间 [Base].[RegionalDate]    = N'Current'
)
RETURNS TABLE 
AS
RETURN
(
    SELECT a.*
    FROM [Edit].[区划码情况] a
    RIGHT OUTER JOIN
    (
       SELECT [区划码ID]
              ,MIN(DISTINCT [截止日期])AS 查询时间
       FROM   [Edit].[区划码情况]
       WHERE   @当前时间BETWEEN [起始日期] AND [截止日期] 
       GROUP BY[区划码ID]
    ) b
    ON a.区划码ID = b.区划码ID AND a.截止日期= b.查询时间
)
 
 

△

建存储过程[Program].增添记录

USE [RegionalCodeWorks]
GO
/****** 对象:  StoredProcedure [Program].[增添记录]    脚本日期: 09/19/2006 20:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        LzmTW
-- Create date:                    20060917
-- Description:    增添记录
-- =============================================
CREATE PROCEDURE [Program].[增添记录]
     @区划码        [Base].[RegionalCode]
    ,@名称            nvarchar(100)
    ,@起始日期    [Base].[RegionalDate]
AS
BEGIN
    SET NOCOUNT ON;

    --如果@起始日期小于表区划码情况的最新起始日期，则退出
    DECLARE @最新起始日期    [Base].[RegionalDate]

    SELECT @最新起始日期 = MAX(起始日期)
    FROM [Edit].区划码情况

    IF @起始日期 < @最新起始日期
        RETURN

    --如果主表区划码没有记录，则更新;取区划码ID
    DECLARE @区划码ID    smallint

    SELECT    @区划码ID = 区划码ID 
    FROM    [Base].[区划码]
    WHERE    区划码 = @区划码;

    IF @区划码ID IS NULL
    BEGIN
        INSERT INTO [Base].[区划码](区划码)
        VALUES(@区划码)

        SET  @区划码ID = @@IDENTITY
    END
    
    --表区划码情况的更新
    DECLARE     @ID            int
                           ,@原名称    nvarchar(100)

    SELECT     @ID = ID
        ,@原名称 = 名称
    FROM  [FUNCTION].区划码情况(@起始日期)
    WHERE 区划码ID = @区划码ID

    IF @ID IS NULL
        BEGIN
            INSERT INTO [Edit].[区划码情况]
                (
                   区划码ID
                 ,名称
                 ,起始日期
                )
            VALUES
                (
                  @区划码ID
                 ,@名称
                 ,@起始日期
                )
        END
    ELSE
        BEGIN

           --忽略同名的记录，也就是保持Current有效
            IF    @名称 = @原名称
                RETURN
            ELSE
                BEGIN
                    --将原记录的截止日期更新为当前起始日期的前一日
                    UPDATE [Edit].[区划码情况]
                    SET 截止日期 = CONVERT(nchar(8) ,DATEADD(d, -1, CAST(@起始日期 as smalldatetime)) ,112)
                    WHERE ID = @ID

                    INSERT INTO [Edit].[区划码情况]
                        (
                           区划码ID
                         ,名称
                         ,起始日期
                        )
                    VALUES
                        (
                          @区划码ID
                         ,@名称
                         ,@起始日期
                        )                
                END
        END    
END

 

在下一文中，将回到程序的设计，先将网上的数据导进数据库再说。