数据库设计规范

1、数据库设计规范版本变更记录版本号修改时间修改人备注1、目的42、概述43、数据库逻辑设计原则43.1命名规范43.1.1表属性规范43.1.2索引63.1.3视图63.1.4存储过程63.1.5触发器63.1.6序列63.1.7变量63.2模式规范化73.3表设计73.4视图设计73.5表分区设计83.6索引设计81、 目的定义数据库设计规范，作为数据库设计、开发以及维护人员的技术参考资料。2、 概述本文主要描述数据库逻辑设计、数据库物理设计等方面的设计规范。3、 数据库逻辑设计原则3.1 命名规范命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。英文

2、单词使用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名。当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的意义。表名、字段名、视图名长度应限制在20个字符内(含前缀)。命名不允许使用SQL保留字，见附录。3.1.1 表属性规范表名采用Camel样式命名，命名格式为表名。示例：ticketType表名以英文单数命名，不采用复数方便更好的使用ORM工具生成符合编程规范的代码。示例：使用product而不是products字段名字段名称命名采用英文单词或缩写，英文单词只来自于具体业务定义，尽量表达清楚含义。同一个字

3、段名在一个数据库中只能代表一个意思。不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。采用Camel样式命名，命名格式为字段名称。，不能用双引号包含。示例：PostalCode字段名称前不要加上表名前缀,比如用户表User包含一个用户名字段,建议采用Name,不建议采用UserName形式。对于表示字符型的字段数据类型建议使用varchar,(oracle为varchar2)，而不是char，建议数据类型尽量采用如下几基本类型。MS Sql数据类型Oracle数据类型字符varcharvarchar2整数intnumber(10)带小数的数字decimal (p , s )numbe

4、r (p , s )日期datetimedate布尔型bitnumber(1)二进制数据varbinary(max)blob文本数据varchar(max)clob如无特别需要，避免使用大对象数据库类型，如ORACLE的blob，clob等，SQL Server的 varchar(max) 和 varbinary(max)等。3.1.2 索引前缀为ix+索引名称。示例：ixBusID3.1.3 视图视图名称采用Pascal样式命名（以下的对象命名样式一样）。命名格式为view+ 视图名称。示例：viewStation3.1.4 存储过程前缀为proc(为了兼容SQL Server, Oracl

5、e的函数前缀也采用proc)。按业务操作命名存储过程。3.1.5 触发器前缀为trig 。命名格式为 前缀 + 表名 + 触发器名+操作方式(Insert、Update等)。3.1.6 序列前缀为seq 。按业务属性命名。(序列为Oracle特有的对象类型)。3.1.7 变量普通变量前缀为var 。 存放字符、数字、日期型等变量。示例：varName游标变量前缀为cur 。存放游标记录集。记录型变量前缀为rec。 存放记录型数据。表类型变量前缀为tab 。 存放表类型数据。3.2 模式规范化如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余,但是如果在数据量上与性能上无

6、特别要求，考虑到实现的方便性可以有适当的数据冗余，但基本上要达到3NF。3.3 表设计每个表在创建时候，必须指定所在的表空间，不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较频繁的数据表，必须存放在用户自己创建的表空间中（对ORACLE数据库而言）。表的大小对查询的速度影响较大。有时为了提高数据库性能，把常用的属性和很少使用的属性分成两个表，可提高常用查询的速度。3.3.1 表属性规范主键外键关联对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，对于数据量比较大的要求对外健建立索引。对于有要求级联删除属性的外键，

7、必须指定ON DELETE CASCADE 或通过对应程序来实现。NULL值采用Camel样式命名，命名格式为表名。Check条件对于字段有检查性约束，要求指定check规则。触发器触发器是一种特殊的存储过程，通过数据表的DML操作而触发执行，起作用是为确保数据的完整性和一致性不被破坏而创建，实现数据的完整约束。触发器事务属性的选择时候，对表操作的事务属性必须与应用程序事务属性保持一致，以避免死锁发生。尽量避免使用触发器。3.4 视图设计每个表在创建时候，必须指定所在的表空间，不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较频繁的数据表，必须存放在用户自己创建的表空间中（

8、对ORACLE数据库而言）。视图是虚拟的数据库表，在使用时要遵循以下原则：（1）从一个或多个库表中查询部分数据项；（2）为简化查询，将复杂的检索或字查询通过视图实现；（3）提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员；（4）视图中尽量避免嵌套使用视图；除特殊需要，避免类似SELECT * FROM TableName 而没有检索条件的视图；3.5 表分区设计当表数据是分段的数据，且比较多，查询时明显感觉到数据很慢时，可考虑创建分区表。分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区还可以极

9、大简化常见的管理任务。比如，表数据是以年为分隔，对于当年的数据的操作需要添加、修改、删除和查询，而对于往年的数据，几乎不需要操作，或者操作往往只限于查询，那可考虑使用分区表。3.6 索引设计对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。对于簇集索引(clustered index)和非簇集索引(nonclustered index)的选择问题，一般在经常排序的字段上创建簇集索引，而这些字段并不一定是主键（比如没有意义的自动增长主键）。3.7 SQL编写所有sql关键词全部大

10、写，比如SELECT、 FROM、 INSERT、UPDATE、DELETE、ORDER BY等。3.7.1 字符类型数据SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。3.7.2 复杂SQL对于非常复杂的SQL(特别是有多层嵌套，带子句或相关查询的)，应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。3.7.3 INSERT语句使用INSERT语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。3.7.4 高效性减少连接运算连接是开销很大的运算，参与连接的表越多

11、、越大，开销也越大，所以尽量避免很多表连接运算。子查询时的EXISTS和IN选择连接是开销很大的运算，参与连接的表越多、越大，开销也越大，所以尽量避免很多表连接运算。如果查询谓词在子查询中，通常使用IN；如果谓词是过滤父表记录，则使用EXISTS合适。下面为EXISTS子查询改为IN子查询的例子：SELECT e.employee_id, e.first_name, e.last_name, e.salaryFROM employees eWHERE EXISTS (SELECT 1 FROM orders o WHERE e.employee_id = o.sales_rep_id AND

12、o.customer_id = 144);语句改用IN:SELECT e.employee_id, e.first_name, e.last_name, e.salaryFROM employees eWHERE e.employee_id IN (SELECT o.sales_rep_idFROM orders oWHERE o.customer_id = 144);避免使用SELECT * 语句如果不是必要取出所有数据，不要用*来代替，应给出字段列表。避免不必要的排序不必要的数据排序大大的降低系统性能。3.7.5 完整性有依赖关系的表，例如主外键关系表，在删除父表时必须级联删除其子表相应数

13、据，或则按照某种业务规则转移该数据。4、 设计工具统一使用Sybase PowerDesigner建模工具，在该工具上完成物理模型的设计。所有的数据库对象尽可能在物理模型上进行设计，而且每个物理模型都要有相应的文字描述(code对应的name、comment要填完全)，所有的数据库对象变更以数据库物理模型为基准。附录：保留关键字建议不采用MS SQL Server、Oracle、ISO的保留关键字。以下是对应官方文档对关键字的说明。Microsoft SQL Server 将保留关键字用于定义、操作和访问数据库。保留关键字是 SQL Server 使用的 Transact-SQL 语言语法的一

14、部分，用于分析和理解 Transact-SQL 语句和批处理。尽管在 Transact-SQL 脚本中使用 SQL Server 保留关键字作为标识符和对象名在语法上是可行的，但规定只能使用分隔标识符。SQL Server 保留关键字ADDEXISTSPRECISIONALLEXITPRIMARYALTEREXTERNALPRINTANDFETCHPROCANYFILEPROCEDUREASFILLFACTORPUBLICASCFORRAISERRORAUTHORIZATIONFOREIGNREADBACKUPFREETEXTREADTEXTBEGINFREETEXTTABLERECONFIG

15、UREBETWEENFROMREFERENCESBREAKFULLREPLICATIONBROWSEFUNCTIONRESTOREBULKGOTORESTRICTBYGRANTRETURNCASCADEGROUPREVERTCASEHAVINGREVOKECHECKHOLDLOCKRIGHTCHECKPOINTIDENTITYROLLBACKCLOSEIDENTITY_INSERTROWCOUNTCLUSTEREDIDENTITYCOLROWGUIDCOLCOALESCEIFRULECOLLATEINSAVECOLUMNINDEXSCHEMACOMMITINNERSECURITYAUDITCO

16、MPUTEINSERTSELECTCONSTRAINTINTERSECTSESSION_USERCONTAINSINTOSETCONTAINSTABLEISSETUSERCONTINUEJOINSHUTDOWNCONVERTKEYSOMECREATEKILLSTATISTICSCROSSLEFTSYSTEM_USERCURRENTLIKETABLECURRENT_DATELINENOTABLESAMPLECURRENT_TIMELOADTEXTSIZECURRENT_TIMESTAMPMERGETHENCURRENT_USERNATIONALTOCURSORNOCHECKTOPDATABASE

17、NONCLUSTEREDTRANDBCCNOTTRANSACTIONDEALLOCATENULLTRIGGERDECLARENULLIFTRUNCATEDEFAULTOFTSEQUALDELETEOFFUNIONDENYOFFSETSUNIQUEDESCONUNPIVOTDISKOPENUPDATEDISTINCTOPENDATASOURCEUPDATETEXTDISTRIBUTEDOPENQUERYUSEDOUBLEOPENROWSETUSERDROPOPENXMLVALUESDUMPOPTIONVARYINGELSEORVIEWENDORDERWAITFORERRLVLOUTERWHENE

18、SCAPEOVERWHEREEXCEPTPERCENTWHILEEXECPIVOTWITHEXECUTEPLANWRITETEXT此外，ISO 标准定义了保留关键字列表。不要使用 ISO 保留关键字作为对象名和标识符。ODBC 保留关键字列表（如下表所示）与 ISO 保留关键字列表相同。注意：ISO 标准保留关键字有时可能比 SQL Server 限制更多，有时则更少。例如，ISO 保留关键字列表包含 INT。SQL Server 不必将此区分为保留关键字。 Transact-SQL 保留关键字可用作数据库或数据库对象（如表、列、视图等）的标识符或名称。使用带引号的标识符或分隔标识符。不限制将

19、保留关键字用作变量和存储过程参数的名称。有关详细信息，请参阅使用标识符作为对象名称。ODBC 保留关键字保留了下列关键字以用于 ODBC 函数调用。这些关键字根本不约束 SQL 语法；然而，为确保与支持核心 SQL 语法的驱动程序兼容，应用程序应避免使用这些关键字。下面是当前的 ODBC 保留关键字列表。ABSOLUTE EXEC OVERLAPS ACTION EXECUTE PAD ADA EXISTS PARTIAL ADD EXTERNAL PASCAL ALL EXTRACT POSITION ALLOCATE FALSE PRECISION ALTER FETCH PREPARE

20、AND FIRST PRESERVE ANY FLOAT PRIMARY ARE FOR PRIOR AS FOREIGN PRIVILEGES ASC FORTRAN PROCEDURE ASSERTION FOUND PUBLIC AT FROM READ AUTHORIZATION FULL REAL AVG GET REFERENCES BEGIN GLOBAL RELATIVE BETWEEN GO RESTRICT BIT GOTO REVOKE BIT_LENGTH GRANT RIGHT BOTH GROUP ROLLBACK BY HAVING ROWS CASCADE HO

21、UR SCHEMA CASCADED IDENTITY SCROLL CASE IMMEDIATE SECOND CAST IN SECTION CATALOG INCLUDE SELECT CHAR INDEX SESSION CHAR_LENGTH INDICATOR SESSION_USER CHARACTER INITIALLY SET CHARACTER_LENGTH INNER SIZE CHECK INPUT SMALLINT CLOSE INSENSITIVE SOME COALESCE INSERT SPACE COLLATE INT SQL COLLATION INTEGE

22、R SQLCA COLUMN INTERSECT SQLCODE COMMIT INTERVAL SQLERROR CONNECT INTO SQLSTATE CONNECTION IS SQLWARNING CONSTRAINT ISOLATION SUBSTRING CONSTRAINTS JOIN SUM CONTINUE KEY SYSTEM_USER CONVERT LANGUAGE TABLE CORRESPONDING LAST TEMPORARY COUNT LEADING THEN CREATE LEFT TIME CROSS LEVEL TIMESTAMP CURRENT

23、LIKE TIMEZONE_HOUR CURRENT_DATE LOCAL TIMEZONE_MINUTE CURRENT_TIME LOWER TO CURRENT_TIMESTAMP MATCH TRAILING CURRENT_USER MAX TRANSACTION CURSOR MIN TRANSLATE DATE MINUTE TRANSLATION DAY MODULE TRIM DEALLOCATE MONTH TRUE DEC NAMES UNION DECIMAL NATIONAL UNIQUE DECLARE NATURAL UNKNOWN DEFAULT NCHAR U

24、PDATE DEFERRABLE NEXT UPPER DEFERRED NO USAGE DELETE NONE USER DESC NOT USING DESCRIBE NULL VALUE DESCRIPTOR NULLIF VALUES DIAGNOSTICS NUMERIC VARCHAR DISCONNECT OCTET_LENGTH VARYING DISTINCT OF VIEW DOMAIN ON WHEN DOUBLE ONLY WHENEVER DROP OPEN WHERE ELSE OPTION WITH END OR WORK END-EXEC ORDER WRIT

25、E ESCAPE OUTER YEAR EXCEPT OUTPUT ZONE EXCEPTION 将来的关键字下列关键字可能会在将来的 SQL Server 版本中保留使用，作为将来要实现的新功能。注意，不要使用这些关键字作为标识符。ABSOLUTEFREEPRESERVEACTIONFULLTEXTTABLEPRIORADMINGENERALPRIVILEGESAFTERGETREADSAGGREGATEGLOBALREALALIASGORECURSIVEALLOCATEGROUPINGREFAREHOSTREFERENCINGARRAYHOURRELATIVEASSERTIONIGNORE

26、RESULTATIMMEDIATERETURNSBEFOREINDICATORROLEBINARYINITIALIZEROLLUPBITINITIALLYROUTINEBLOBINOUTROWBOOLEANINPUTROWSBOTHINTSAVEPOINTBREADTHINTEGERSCROLLCALLINTERVALSCOPECASCADEDISOLATIONSEARCHCASTITERATESECONDCATALOGLANGUAGESECTIONCHARLARGESEQUENCECHARACTERLASTSESSIONCLASSLATERALSETSCLOBLEADINGSIZECOLLA

27、TIONLESSSMALLINTCOMPLETIONLEVELSPACECONNECTLIMITSPECIFICCONNECTIONLOCALSPECIFICTYPECONSTRAINTSLOCALTIMESQLCONSTRUCTORLOCALTIMESTAMPSQLEXCEPTIONCORRESPONDINGLOCATORSQLSTATECUBEMAPSQLWARNINGCURRENT_PATHMATCHSTARTCURRENT_ROLEMINUTESTATECYCLEMODIFIESSTATEMENTDATAMODIFYSTATICDATEMODULESTRUCTUREDAYMONTHTE

28、MPORARYDECNAMESTERMINATEDECIMALNATURALTHANDEFERRABLENCHARTIMEDEFERREDNCLOBTIMESTAMPDEPTHNEWTIMEZONE_HOURDEREFNEXTTIMEZONE_MINUTEDESCRIBENOTRAILINGDESCRIPTORNONETRANSLATIONDESTROYNUMERICTREATDESTRUCTOROBJECTTRUEDETERMINISTICOLDUNDERDICTIONARYONLYUNKNOWNDIAGNOSTICSOPERATIONUNNESTDISCONNECTORDINALITYUS

29、AGEDOMAINOUTUSINGDYNAMICOUTPUTVALUEEACHPADVARCHAREND-EXECPARAMETERVARIABLEEQUALSPARAMETERSWHENEVEREVERYPARTIALWITHOUTEXCEPTIONPATHWORKFALSEPOSTFIXWRITEFIRSTPREFIXYEARFLOATPREORDERZONEFOUNDPREPAREOracle Database 保留关键字A ACCESS,ADD, ALL, ALTER, AND, ANY, AS, ASC, AUDITB BETWEEN, BYC CHAR, CHECK, CLUSTE

30、R, COLUMN, COMMENT, COMPRESS, CONNECT, CREATE, CURRENTD DATE, DECIMAL, DEFAULT, DELETE, DESC, DISTINCT, DROPE ELSE, EXCLUSIVE, EXISTSF FILE, FLOAT, FOR, FROMG GRANT, GROUPH HAVINGI IDENTIFIED, IMMEDIATE, IN, INCREMENT, INDEX, INITIAL, INSERT, INTEGER, INTERSECT, INTO, ISL LEVEL, LIKE, LOCK, LONGM MA

31、XEXTENTS, MINUS, MLSLABEL, MODE, MODIFYN NOAUDIT, NOCOMPRESS, NOT, NOWAIT, NULL, NUMBERO OF, OFFLINE, ON, ONLINE, OPTION, OR, ORDERP PCTFREE, PRIOR, PRIVILEGES, PUBLICR RAW, RENAME, RESOURCE, REVOKE, ROW, ROWID, ROWNUM, ROWSS SELECT, SESSION, SET, SHARE, SIZE, SMALLINT, START, SUCCESSFUL, SYNONYM, S

32、YSDATET TABLE, THEN, TO, TRIGGERU UID, UNION, UNIQUE, UPDATE, USERV VALIDATE, VALUES, VARCHAR, VARCHAR2, VIEWW WHENEVER, WHERE, WITHPL/SQL保留关键字A ALL, ALTER, AND, ANY, ARRAY, ARROW, AS, ASC, ATB BEGIN, BETWEEN, BYC CASE, CHECK, CLUSTERS, CLUSTER, COLAUTH, COLUMNS, COMPRESS, CONNECT, CRASH, CREATE, CU

33、RRENTD DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DISTINCT, DROPE ELSE, END, EXCEPTION, EXCLUSIVE, EXISTSF FETCH, FORM, FOR, FROMG GOTO, GRANT, GROUPH HAVINGI IDENTIFIED, IF, IN, INDEXES, INDEX, INSERT, INTERSECT, INTO, ISL LIKE, LOCKM MINUS, MODEN NOCOMPRESS, NOT, NOWAIT, NULLO OF, ON, OPTION, OR, OR

34、DER,OVERLAPSP PRIOR, PROCEDURE, PUBLICR RANGE, RECORD, RESOURCE, REVOKES SELECT, SHARE, SIZE, SQL, START, SUBTYPET TABAUTH, TABLE, THEN, TO, TYPEU UNION, UNIQUE, UPDATE, USEV VALUES, VIEW, VIEWSW WH EN, WHERE,WITHPL/SQL关键字A A, ADD, AGENT, AGGREGATE, ARRAY, ATTRIBUTE, AUTHID, AVGB BFILE_BASE, BINARY,

35、 BLOB_BASE, BLOCK, BODY, BOTH, BOUND, BULK, BYTEC C, CALL, CALLING, CASCADE, CHAR, CHAR_BASE, CHARACTER, CHARSETFORM, CHARSETID,CHARSET, CLOB_BASE, CLOSE, COLLECT, COMMENT, COMMIT, COMMITTED, COMPILED,CONSTANT, CONSTRUCTOR, CONTEXT, CONVERT, COUNT, CURSOR, CUSTOMDATUMD DANGLING, DATA, DATE, DATE_BAS

36、E, DAY, DEFINE, DETERMINISTIC, DOUBLE, DURATIONE ELEMENT, ELSIF, EMPTY, ESCAPE, EXCEPT, EXCEPTIONS, EXECUTE, EXIT, EXTERNALF FINAL, FIXED, FLOAT, FORALL, FORCE, FUNCTIONG GENERALH HASH, HEAP, HIDDEN, HOURI IMMEDIATE, INCLUDING, INDICATOR, INDICES, INFINITE, INSTANTIABLE, INT, INTERFACE,INTERVAL, INVALIDATE, ISOLATIONJ JAVAL LANGUAGE, LARGE, LEADING, LENGTH, LEVEL, LIBRARY, LIKE2, LIKE4, LIKEC, LIMIT, LIMITED,LOCAL, L