Chinaunix首页 | 论坛 | 博客
  • 博客访问: 19001593
  • 博文数量: 7460
  • 博客积分: 10434
  • 博客等级: 上将
  • 技术积分: 78178
  • 用 户 组: 普通用户
  • 注册时间: 2008-03-02 22:54
文章分类

全部博文(7460)

文章存档

2011年(1)

2009年(669)

2008年(6790)

分类: 数据库开发技术

2008-05-28 15:03:02

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](8NOT 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](6NOT 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,1NOT 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 = OFFON [PRIMARY],
 
CONSTRAINT [IX_区划码] UNIQUE NONCLUSTERED 
(
    
[区划码] ASC
)
WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFFON [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,1NOT 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 = OFFON [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, -1CAST(@起始日期 as smalldatetime)) ,112)
                    
WHERE ID = @ID

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



 
在下一文中,将回到程序的设计,先将网上的数据导进数据库再说。
阅读(286) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~