Mine are almost identical to Ric's.
The difference is that I never name a column [ID], I use [TableNameID]. For m-m xref tables, I always name the table [TableName1TableName2XRef]. For foriegn keys, I use the exact same column name as the PK in the parent table. For candidate keys, I prepend the table name to the column name. When naming constraints & indexes, I start with PK_, FK_, CK_ (candidate key), CC_ (check constraint), & IX_ plus the table name plus each column in the index. I try to start boolean columns with "Is", as in [IsActive].
Invoice table:
InvoiceID (PK)
ClientClientContactID (FK)
ProjectID (FK)
InvoiceNumber (CK)
...
Invoice Detail table:
InvoiceDetailID (PK)
InvoiceID (FK)
LineNumber
...
Client table:
ClientID (PK)
ClientName (CK)
...
ClientContact table:
ClientContactID (PK)
ContactName (CK)
...
ClientClientContact table:
ClientClientContactID (PK)
ClientID (FK)
ClientContactID (FK)
IsActive
IsPrimary
...
I think that anything logical and consistient will give you good results.
>My conventions are:
>
>Fields:
>No spaces on field names, EVER!
>The PK of a table is always named ID (or the same for all tables), and when using it as a FK the convention is (tablename)ID (I write a lot of dynamic SQL and DMO, this makes things easier) - it also forces you to write readable SQL by using the (table).(field name) convention
>
>Tables:
>Use the tbl Prefix (tblCustomers) - very optional
>
>
>SP:
>Never preffix with sp_ (forces an extra trip thru the Master DB before resolving in current DB)