What are some of your most useful database standards?

Question

• Name similarly targetted stored procs with the same prefix, for instance if you've got 3 stored procedures for Person. That way everything for person is grouped in one place and you can find them easily without having to look through all your procs to find them.
  • PersonUpdate
  • PersonDelete
  • PersonCreate
• Do similar things for tables when you have groups of tables with related data. For instance:
  • InvoiceHeaders
  • InvoiceLines
  • InvoiceLineDetails
• If you have the option of schemas within your database, use them. It's much nicer to see:
  • Invoice.Header
  • Invoice.Line.Items
  • Invoice.Line.Item.Details
  • Person.Update
  • Person.Delete
  • Person.Create
• Don't use triggers unless there's no other reasonable approach to achieve that goal.
• Give field names a meaningful prefix so you can tell what table they come from without someone needing to explain. That way when you see a field name referenced, you can easily tell which table it's from.
• Use consistent data types for fields containing similar data, i.e. don't store phone number as numeric in one table and varchar in another. In fact, don't store it as numeric, if I come across a negative phone number I'll be mad.
• Don't use spaces or other obscure characters in table/field names. They should be entirely alphanumeric - or if I had my druthers, entirely alphabetic with the exception of the underscore. I'm currently working on an inherited system where table and field names contain spaces, question marks and exclamation marks. Makes me want to kill the designer on a daily basis!
• Don't use syntax keywords as object names it'll cause headaches trying to retrieve data from them. I hate having to wrap object names as [index] that's two needless chars I didn't need to type damn you!

One thing I haven't seen mentioned yet:

Never use database keywords as object names. You do not want to have to qualify them every time you use them

If you misspell something when you create it, fix it as soon as you notice it. Don't spend years having to remember that in this table UserName is really Usernmae. It's a whole lot easier to fix when there isn't much code written against it.

Never use implied joins (the comma syntax), always specify the joins.

Putting everybody's input together into one list.

Naming Standards

• Schemas are named by functional area (Products, Orders, Shipping)
  
• No Hungarian Notation: No type names in object names (no strFirstName)
  
• Do not use registered keywords for object names
  
• No spaces or any special characters in object names (Alphanumber + Underscore are the only things allowed)
  
• Name objects in a natural way (FirstName instead of NameFirst)
  
• Table name should match Primary Key Name and Description field (SalesType – SalesTypeId, SalesTypeDescription)
  
• Do not prefix with tbl_ or sp_
  
• Name code by object name (CustomerSearch, CustomerGetBalance)
  
• CamelCase database object names
  
• Column names should be singular
  
• Table names may be plural
  
• Give business names to all constraints (MustEnterFirstName)
  

Data Types

• Use same variable type across tables (Zip code – numeric in one table and varchar in another is not a good idea)
  
• Use nNVarChar for customer information (name, address(es)) etc. you never know when you may go multinational
  

In code

• Keywords always in UPPERCASE
  
• Never use implied joins (Comma syntax) - always use explicit INNER JOIN / OUTER JOIN
  
• One JOIN per line
  
• One WHERE clause per line
  
• No loops – replace with set based logic
  
• Use short forms of table names for aliases rather than A, B, C
  
• Avoid triggers unless there is no recourse
  
• Avoid cursors like the plague (read http://www.sqlservercentral.com/articles/T-SQL/66097/)
  

Documentation

• Create database diagrams
  
• Create a data dictionary
  

Normalization and Referential Integrity

• Use single column primary keys as much as possible. Use unique constraints where required.
  
• Referential integrity will be always enforced
  
• Avoid ON DELETE CASCADE
  
• OLTP must be at least 4NF
  
• Evaluate every one-to-many relationship as a potential many-to-many relationship
  
• Non user generated Primary Keys
  
• Build Insert based models instead of update based
  
• PK to FK must be same name (Employee.EmployeeId is the same field as EmployeeSalary.EmployeeId)
  
• Except when there is a double join (Person.PersonId joins to PersonRelation.PersonId_Parent and PersonRelation.PersonId_Child)
  

Maintenance : run periodic scripts to find

• Schema without table
  
• Orphaned records
  
• Tables without primary keys
  
• Tables without indexes
  
• Non-deterministic UDF
  
• Backup, Backup, Backup
  

Be good

• Be Consistent
  
• Fix errors now
  
• Read Joe Celko's SQL Programming Style (ISBN 978-0120887972)
  

My standards for Oracle are:

• Keywords are always in UPPERCASE;
• Database object names are always in lowercase;
• Underscores will replace spaces (ie there won't be any camel case conventions that are common on, say, SQL Server);
• Primary keys will pretty much always be named 'id';
• Referential integrity will be enforced;
• Integer values (including table ids) will generally always be NUMBER(19,0). The reason for this is that this will fit in a 64-bit signed integer thus allowing the Java long type to be used instead of the more awkward BigInteger;
• Despite the misnomer of appending "_number" to some column names, the type of such columns will be VARCHAR2 not a number type. Number types are reserved for primary keys and columns you do arithmetic on;
• I always use a technical primary keys; and
• Each table will have its own sequence for key generation. The name of that sequence will be _seq.

With SQL Server, the only modification is to use camel case for database object names (ie PartyName instead of party_name).

Queries will tend to be written multi-line with one clause or condition per line:

SELECT field1, field2, field2
FROM tablename t1
JOIN tablename2 t2 ON t1.id = t2.tablename_id
WHERE t1.field1 = 'blah'
AND t2.field2 = 'foo'

If the SELECT clause is sufficiently long I'll split it out one field per line.

• Name all constraints