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

Author：水如烟

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

这一篇主要说基本信息也就是“[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

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

• ·乙肝大三阳`我能跟她结婚吗?
• ·夜间起来上厕所尿尿的时候出现暂时的休克是怎么回
• ·小三洋,不会有事吧,会不会是性传播啊
• ·卡通渲染进展（一）
• ·一个使用多线程删除指定目录及子目录下所有指定文
• ·行政区划数据数据库的设计(三)
• ·行政区划数据数据库的设计(五)
• ·业务流程:Business Request的虚
• ·[手机平台]Alarm事件管理器设计备忘录
• ·经典4000元月薪过日子