What are some of your most useful database standards?

Question

I have some ideas, some that I have accumulated over time, but I really want to know what makes things go smoothly for you when modeling database:

1. Table name matches Primary Key name and description key
2. Schemas are by functional area
3. Avoid composite primary keys where possible (use unique constraints)
4. Camel Case table names and field names
5. Do not prefix tables with tbl_, or procs with SP_ (no hungarian notation)
6. OLTP databases should be atleast in BCNF / 4NF

Answer 1

• 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!