Ylbtech-recode (record)-Database design

Ylbtech-dbs:ylbtech-recode (record)-Database design

-- =============================================
--Databasename:recode
--desc: Record (Life record)
--Creative ideas: History cannot be modified, but can be amended to trace the evolution process.
--pubdate:16:46 2015/1/12
--Author:ylbtech
-- =============================================

1.A, Database diagram (db Diagram) back to top

1.B, database design script back to top

1.b.1,

-- =============================================--Databasename:recode--desc: Record (Life record)--Creative Ideas: History cannot be modified, but can be amended to trace the evolution process. --pubdate:16:46 2015/1/12--Author:ylbtech-- ============================================= UseMasterGO--Drop The database if it already existsIF  EXISTS (    SELECTname fromsys.databasesWHEREName=N'Recode')DROP DATABASERecodeGOCREATE DATABASERecodeGo UseRecodeGO-- =============================================--ylb: Category table--desc:-- =============================================Create TableCategory (CategoryIduniqueidentifier Primary Key,--numberingCategoryNamevarchar( $),--categoryFlagvisiblebit            --is enabled)GoGo-- =============================================--opt:1, adding categories-- =============================================--INSERT INTO Category (categoryid,categoryname,flagvisible) VALUES (', ' Uncategorized ', 0)Go-- =============================================--opt:1, view the list of categories-- =============================================SelectCategoryid,categoryname,flagvisible fromCategoryOrder  byCategoryNameGo-- =============================================--ylb: Record table--desc: Record, record version history--2) Allow only one level of records to be modified, no history changes allowed-- =============================================Create TableRecode (Recodeidint Identity(1,1)Primary Key,--numberingContentvarchar( -),--contentpubdatedatetime default(getdate()),--Release dateEndeditdatedatetime,--Last Modified DateFlagbaseint,--0: Level One: Other level two (i.e. parent unit number)CategoryIduniqueidentifier ReferencesCategory (CATEGORYID)--category number "FK")Go-- =============================================--opt:1, adding records-- =============================================--INSERT INTO Recode (Content,pubdate,flagbase,categoryid) VALUES (' Hi, Rain ', ' 2015-1-12 ', 0, ')Go-- =============================================--Opt:2, modify the record and add notes (modify the original record content, update the last modified date, and insert the modified notes)-- =============================================--Update Recode set content= ' Hi,gr rain ', Endeditdate=getdate () where recode= ' 101 'Go--INSERT INTO Recode (Content,pubdate,flagbase,categoryid) VALUES (' Hi, Rain ', ' 2015-1-12 ', 101, ')Go-- =============================================--Opt:3, List of records-- =============================================SelectRecodeid,content,pubdate,endeditdate,flagbase,categoryid fromRecodewhereFlagbase=0Order  byRecodeidGo-- =============================================--opt:3.2, Record modification notes list-- =============================================SelectRecodeid,content,pubdate,endeditdate,flagbase,categoryid fromRecodewhereFlagbase=101Order  byRecodeid

View Code

1.b.2,

1.C, functional implementation code (function implementation codes) back to top

Ylbtech Source: http://ylbtech.cnblogs.com/ This article is copyright to the author and the blog Park, Welcome to reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to the original link, otherwise reserves the right to pursue legal responsibility.

Ylbtech-recode (record)-Database design