Database Guidelines Articles, samples and tutorials

General:

All the names (i.e. Table name, column name, SP names etc) will follow the Pascal Case. For e.g. ThisIsAnExampleOfPascalCasing
Exceptions to this rule are the abbreviations like <PROJECTNAME> etc.

Table Names:

All table names will begin with the name of the Entity it relates to. For example tables related to Resource or Image Gallery will be like Resource, ImageGallery.
Another example Product, Company
Table names would not be plural.

Column Names:

Column names should be meaningful. They should not use incomplete words. For e.g. UpdateDate should not be written as ‘UpdDate’ etc. The word "Date" would be used and it would be suffixed.

Stored Procedures:

All Stored procedure name will begin with the name of the Table it primarily queries, followed by the verb. Thus the format will be {tablename}{verb}.
For e.g. ProductAdd, ProductUpdate, ProductGetAll etc.

Functions/ Views/ Triggers:

All Functions/Views/Trigger name will begin with the name of the Table it primarily queries, followed by the verb.
Thus the format will be {tablename}{verb}.

Moreover, for functions "_fn" will be appended. Thus the format would be {tablename}{verb}_fn.
Similarly for Views "_vw" will be appended. Thus the format would be {tablename}{verb}_vw.
And for Triggers "_trg" will be appended. Thus the format would be {tablename}{verb}_trg.

Tables(many to many relationship tables):

Product Feature and Product Detail tables will separate two tables by the underscore character.
For e.g. Product_Feature, Product_Detail. Underscores will be used only to indicate this table is related to product.

Primary Key/Foreign Key:

The primary key of a table will be defined as TableName[Id](i.e. Table name suffixed by the word ‘Id’). For e.g. ProductId(Product), CompanyId(Company)
The prefix pk or fk would not be used.

Moreover, specify description for all tables and each columns of table. Whenever you add new column or table to database, make sure you have added a description for the same. That will enable to understand the use of specific column in the project.

For performance consideration, you need to envisage creating indexes for the tables in database which has most searched columns. There will be an increasing number of records, and admin need reports with search facility based on parameter.

Types of Indexes in SQL 2005

There are cluster and non cluster indexes in SQL 2005:

CLUSTER INDEX

- A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
- The leaf nodes of a clustered index contain the data pages.
- Cluster index is applies to columns that contain a large number of distinct values. It applies to columns that are accessed sequentially.
- It applies to queries that return a range of values using operators such as BETWEEN, >, >=, <, and <= or which return large result sets.

NON - CLUSTER INDEX

- A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. Per table only 249 non clustered indexes.
- The leaf nodes of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
- Non-cluster index applies to the data rows that are not sorted and stored in order based on their non-clustered keys.
- Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.

Online Members

Database Guidelines