a Reference for DataBase Object Naming Rules

refere to:
http://blogs.msdn.com/steven_bates/archive/2006/03/20/Database-Object-Naming-Rules.aspx



Version 0.0.0.1, 2007.9.19

Ez.Zhao

Content

1     Doc Goal

2     Rules of TableName

3     Rules of FieldName

3.1      Primary Key Names

3.2      Foreign Key Names

3.3      Default Value Constraint Names

3.4      Check Constraint Names

3.5      Index Names

4     Rules Of Stored Procedure Names

5     Rules of User Defined Functions (UDF) Names

6     Rules Of View Names

7     Rules Of Trigger Names

1 Doc Goal

2 Rules of TableName

Common rules: TableName : named by data object/view, and according to the common way that analyse a business object’s attribution, there will be four kinds of table: EntityTable, EntityExpandTable, RelationTable, MasterSlaveTable.

l         EntityTable:

n         Define:a table just to define the business object’s attribution

n         Format: first character of each word is separated by Capital letter;

n         Example:

n         Note:

l         EntityExpandTable:

n         Define: if a table has many field and most or some of them are not used often.. Please thinking about putting them into an expand table

n         Format:Master Table Name + “ ExpInfo“;

n         Example:
eReviewRecordExpInfo: eReviewRecord + “ExpInfo”

n         Note:

l         RelationTable:

n         Define: Maps are used to produce one->many or many->many relationships

n         Format: the two EntityTables Names + “Map”,

n         Example:
 WorkPackageProjectMap : “WorkPackage” + “Project”+ “Map”;

n         Note:
 if the two tables have a relationship of master- slave, the first EntityTableName is the master table and the second table is the slave table;

l         Master/Slave Table OR Module/SubModuleName/…/Table:

n         Define: if some business objects have master/slave relationship or some business objects belong to the other business objects. Please think about using the following formatting on them.

n         Format: MasterTableName+SlaveTableName OR ModuleName+SubModuleName+TableName (please make the module levels less than three)

n         Example:
StandardsAuditException: “StandardsAudit” + “Exception”;

n         Note:

3 Rules of FieldName

Common Rules:

l         One business object should be thought as a unit, and it starts with a upper-case letter, and the subsequent words are lowercase; and the second business object just do the same.

l         the whole length of the field name should be less than 20, if it will be more than that , please think about some short names that are common for the developers.

l          “Created” and “Updated” must be included in all tables for replication purposes in future.

l         Pascal Case

l         Avoid underscore

l         Format: <TableName(for PK only)><Qualifier><Name>

use the following components in the order below;

• Qualifier: Optional; Description, to clarify the meaning of the field. For example, if a product has two images, this would clarify the field, eg. FrontImage and RearImage
• Name: Required; This is a database independent “datatype” descriptor which is used to classify the type of data. Below is a common list of standard classifiers. The exception to this is a Boolean. This should be Prefixed with “Is” as this more positively represents the meaning o the value. Flag suffix is considered optional “Flag” or Eg. IsEnabled or IsEnabledFlag

Classifier	Description	Suggested SQL Data Type
Address	Street or mailing address data	nvarchar
Age	Chronological age in years	int
Average	Average; consider a computed column	numeric
Amount	Currency amount	money
Code	Non Database Identifier
Count
Data	A field containing extensible data	xml
Date	Calendar date	smalldatetime
Datetime	Date including time	datetime
Day	Day of month (1 - 31)	tinyint
Description	Brief narrative description	nvarchar(MAX)
Duration	Length of time, eg minutes	int
ID	Unique identifier for something	int
Image	A graphic image, such as a bitmap	varbinary(MAX)
Flag	Not Required: Flag indicates a boolean indicator, where the Qualifier verb does not make it clear it is a verb. Examples of a Qualifier are: Is, Has, Uses. Eg IsEnabled	bit
Month	Month of year
Name	Formal name	nvarchar
Number
Percent	Number expressed as a percent
Quantity	A number of things	any numerical
Rate	Number expressed as a rate	any numerical
Ratio	A proportion, or expression of relationship in quantity, size, amount, etc. between two things	any numerical
Sequence	A numeric order field	int
Text	Freeform textual information	nvarchar(MAX)
Time	Time of day	smalldatetime
Title	Formal name of something	nvarchar
Version	Timestamp	timestamp
Weight	Weight measurement	any numerical
XML	A field containing xml data	xml
Year	Calendar year or julian year number

3.1       Primary Key Names

Primary Key: ID: All tables have ID as the label for ids, not TableID or others，and it should start as “PK_”;

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix primary key with “PK_”
  • TableName: Required; Table name of table being keyed
• Examples:

o    PK_Customer

3.2       Foreign Key Names

Foreign Key: Foreign TableName + “ID”; And it should start as “FK_”;

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix foreign key with “FK_”
  • Reference Table Name(s): Required; Table name of table in the join, for which a unique index is on column(s) being linked. Where both have a unique index, such as linking key, order is optional
  • Foreign Table Name(s): Required; Table name of table in the join, for there is not a unique index on the column(s) being linked.
• Example foreign key names:
  • FK_Country_Customer
  • FK_Customer_Sales

3.3       Default Value Constraint Names

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix default value constraint with “DF_”
  • TableName: Required; Table name
  • ColumnName: Required; Column name
• Example foreign key names:
  • DF_Author_Gender

3.4       Check Constraint Names

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix check constraint with “CK_”
  • TableName: Required; Table name
  • Integer: Required; Where the integer id is used to distinguish the check constraint from other check constraints on the same table.
• Example foreign key names:
  • CK_Author1

3.5       Index Names

Index names are unique within a table so it isn’t necessary to include the tablename in the index. When looking at execution plans it is helpful to have a hint about the columns being indexed

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “IX”
  • Clustered: Required; if Clustered Index include “C”
  • Unique: Required; if Unique Index include “U”
  • Column Names: Required; Include the list of columns indexed, using underscores between the column names. For an index that covers all columns in the table, use the word All.

·         Example Index Names:

o    IXCU_AuthorID   (clustered unique)

o    IXU_AuthorID (unique)

o    IX_AuthorID_AuthorName (composite index)

o    IXC_AuthorID (clustered not unique)

4 Rules Of Stored Procedure Names

·         Use PascalCase

• Naming Format: use the following components in the order below;
  • Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
  • Action: Required; eg Save, Load, Get, Set, SetSingle, Search, Delete
  • Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
  • Return Type: Optional; Indicates the type of data return
  • Format: “USP_”+ObjectName+Action+…
• Example Stored Procedure Names:
  • AuthorSave
  • AuthorLoad
  • AuthorLoadByAuthorID
  • AuthorLoadByName
• Do not:
  • Use special characters.
  • Use stored procedure group numbers (e.g. myProc;1).
  • prefix names with “sp_” as those are reserved for procedures shipped by SQL Server.

5 Rules of User Defined Functions (UDF) Names

·         Use PascalCase

• Naming Format: use the following components in the order below;
  • Prefix: Required; “udf_”
  • Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
  • Action: Required; eg Get, Set, SetSingle, Search, Delete
  • Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
  • Return Type: Optional; Indicates the type of data return
• Example Function Names:
  • udf_AuthorGetID
• Often stored procedures will replicate (wrap) a user defined function. In this case the names should be identical with the exception of the additional prefix on a UDF.
• Note, udfs cannot have any “effects” so cannot modify data.

6 Rules Of View Names

For Views which provide a view on the data which makes them read only.

·         Use PascalCase, except for prefix

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “VW_”
  • Object: Required; usually the concatenation of tables in the view
  • Qualifier: Optional; additional descriptive words which help to clarify the purpose of the view.

7 Rules Of Trigger Names

·         Use PascalCase, except for prefix

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “TRG”
  • Type: Required; depending on type of trigger, after or instead of. prefix with “A_” or “I_”
  • Object: Required; usually the table being iterated over.
  • Actions covered: Required; composite key of actions, “Upd”, “Ins”, “Del”

·         Example Trigger Names:

o    TRGA_CustomerInsUpdDe

o    TRGA_ProductDel

o    TRGI_AuthorUpd