Set your own standards and stick to them.
I have a complex system that generates about 80% of the stored procedures (5000) so I can easily modify error handling for example and regenerate. This liberates me to spend quality time on what little custom code needs written.
My developers love everything I do below. It saves them from constantly looking up object definitions.
I'll include a sample list so you can get started and so everyone else can find a way to feel superior to me in some way.
Use the same logical names across a set of databases to simplify backups and restores. For example, give the logical name lims_data to a USALIMS_Development database and lims_log to its log file then use the same logical names for testing and production versions also.
Create a table called something like ApplicationSettings or DatabaseSettings and store a database structure version number which is updated every time a data definition language change is applied so you always know where each database is at structurewise.
Set the database to ANSI settings and rip out all ANSI settings from all object scripts.
is_ansi_null_default_on = 1
Think of schemas as folders in a database and all objects are in folders and no objects are in the root.
Microsoft has converted the owner dbo into a schema named dbo. I would recommend creating your own schemas and using them for controlling security permissions. Precede all object references with dbo or a custom schema name in all scripts.
We use camel casing (word caps) such as AccessionPanel and AccessionNoteLog.
The names are generally singular nouns.
I would suggest Join tables be called <Table1>And<Table2>.
I would suggest Types tables be called <Table>Types or <Table>Lookup.
Avoid use of spaces or characters that are reserved words or require [quoted identifiers] syntax.
Types tables are used as lookup valid values.
I suggest adding a read-only column called InternalName so users are free to change a ShortName column without breaking code. For example a programmer may join to a QueueTypes table and filter on InternalName='On Hold' while a user may change ShortName to 'Awaiting Approval'. Alternately you could add a SystemLocked bit column.
I suggest adding a SortOrder column also which could easily be updated by most used or most recently used according to foreign key data.
Column names are nouns and follow the same convention of camel casing such as AccessionKey.
Never abbreviate any object name and programmers will never have to guess at a name.
Matching column names across tables should always have the same data type and refer to the same concept.
The primary key is generally the first column in the table and is of type int and named the same as the table with the word Key appended, AddressKey for example.
Update: We are getting away from Identity primary keys and going to guids which are easier to insert in batches.
Our last five columns of every table are a standard set of auditing columns as follows: CreatedByUserName, CreatedDate, LastUpdatedByUserName, and LastUpdatedDate, and LastSaveID.
Foreign key columns are generally named the same as the primary key column they reference except when two foreign keys point to the same primary key such as ParentAccessionKey and ChildAccessionKey.
Nearly every table should have a primary key.
These are named <TableName>_PK such as AccessGroupRelationshipPK
These are named <FKTableName>_<FKColumnName>_FK so they are always unique for example: AccessGroupRelationship_ChildAccessGroupKey_FK and AccessGroupRelationships_ParentAccessGroupKey_FK.
Decide up front if you will use cascade deletes and updates OR if you will write custom triggers to handle it, a mixture of those techniques gets confusing.
Nearly every table should have an alternate key to prevent duplicates from being inserted. It is a programmer's way of knowing when "where" criteria is unique.
These are named <TableName>_<ColumnName>_AK.
We have very few column default values since stored procedures contain all columns as parameters.
They are named <TableName>_<ColumnName>_Dflt to guarantee name uniqueness.
We generally have no unique indexes since these are all created behind the scenes as table constraints.
We have unique indexes when the index is filtered such as indexing the rows with a not null value.
The primary key and alternate key constraint naming conventions have been discussed.
Column Data Types
We have eliminated deprecated data types and inexact data types as part of Sql2008 Readiness..
We don't use Text or Image and have substituted Varchar and Varbinary for these.
We don't use Money or SmallMoney and have substituted decimal(p,s) for these.
We have eliminated the use of Float or Double and substituted decimal(p,s) where it makes sense.
We use DateTime columns when we need to store time with dates.
We use SmallDateTime as an indicator that the Time will always be set to 00:00:00. These will be converted to Date data type when upgrading to SqlServer 2008.
Define each table column as not-null as a rule. Drop back to nullable only if there is a strong business case for it. Nullability adds complexity and bugs.
Date range columns should be explicit in range such as BeginEffectiveDateOn and EndEffectiveDateBefore to show which operators should be used such as >, >=, <, <=.
User Data Types and Rules
We have eliminated the use of these as they only added confusion without adding any advantages.
All unfiltered unique indexes have been implemented as table constraints.
All remaining indexes are named <TableName>_<ColumnName1>_<ColumnName2>_IE where IE stands for Inversion Entry (duplicate values expected).
Indexes are overhead for inserts and updates and deletes so create an index only after it become a performance problem. Not every foreign key column needs indexed.
All triggers are named <TableName>_<Action> where action is usually Insert, Update, Delete, InsteadOfDelete, TemplateAuditInsert, TemplateAuditDelete, etc.
Standard stored procedures generally reference one table for its selects, inserts, updates, deletes, and processing. Use standard naming conventions for these such as <TableName>Insert, <TableName>SelectSingle, or <TableName>SelectByFK
Always use @<ColumnName> as parameter names; this allows the DBA to make sure parameter data types match changing column data types, a common bug in evolving environments.
Custom procedures that reference many tables should be named <Process><Action> such as InvoiceValidating.
Avoid cursors completely if possible. Write set oriented instead of procedural SQL as much as possible.
Currently we have no conventions though it may be useful to distinguish between scalar and table functions such as <Function>Tbl.
For new development, use schema level security. It is much easier than granting execute on each procedure.
Use WindowsGroups as logins/users so network guys can manage security by job roles.
Use synonyms to point to any objects outside of the database. This allows simple redirection when other databases are renamed or moved.