Author：水如烟
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
这一篇主要说基本信息也就是“[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
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
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'
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_区划码情况]
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.查询时间
)
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
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
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
在下一文中，将回到程序的设计，先将网上的数据导进数据库再说。
