Ten Worst Things to Do in a Database

Ten Worst Things to Do in a Database
I decided to do a series on the Ten Worst Things to do in a Database. I started to list practices I dislike when working with a database. It didn’t take long until I had more than Ten. Now I have to prioritize…and I’m sure I missed a few good contenders.

Already I’m finding it hard to decide what should be the Top ten. So, here’s what I’m going to do. I’m going to post my brainstorm today. Then I’m going to open it up for you to participate by adding to the list, and helping to decide which are the TOP (10) "Worst Things to Do in a Database".

Here’s how you participate:

• Tuesday and Wednesday you may submit new items to add to the list
• I’ll include the updated list in the daily Editorial on Wednesday and Thursday
• Thursday and Friday you may submit what you think are the top three worst items
• Send your new items or votes to me at facebook, twitter, or btaylor@sswug.org

So, here is my straw list:

1. Not have and exercise a Disaster Recovery Plan
2. Modify a production database inappropriately
   • Modify existing objects using ad-hoc queries
   • Develop new features in production without first developing elsewhere
3. Not Normalize your data
   • Use your business objects and Client Code to define the schema of your base data
     • Save JSON objects as binary data in a table where that data could be mined
     • Store data in XML data type because it is jagged or you don’t want to take the time to understand it
     • De-normalize for ease of consumption by business layer
       Ex. Phone, Cell, Fax, Help Desk all in separate phone columns instead of separate records in a phone table
   • Embed non-structured data with structured data in the same table and/or database
     ex. BLOB (Binary Large Object) data included in same table as normalized data
4. Not Enforce Foreign Key Relationships
5. Not Document a Database Schema
   • Tables
   • Columns
   • Relationships
6. Not Keep Table/View Schema Current
   • Retain deprecated tables or columns after all accessing code is gone
   • Include place holder columns for future data not yet populated
7. Not have and implement a data retention policy
8. Compensate for a bad Schema Design
   • Use Indexed Views to compensate for a bad schema
   • Use Triggers to compensate embedded code sprawl or related tables stored in multiple tables and/or databases
   • Use Only Surrogate Keys When Real World unique keys are available
   • Use Replication when tables are separated into different databases inappropriately
   • Use User Defined Functions to compensate for bad schema
9. Write stored procedures in a database that only accesses data in another database
10. Not tune a database
    • Not create and maintain indexes based on data access patterns
    • Not create and maintain appropriate statistics
11. Not implement appropriate security
    • Over Simplify Application User Authentication
      • Use SA or Administrative Account
      • Make All Logins Database Owner, or Administrator role
    • b. Not Utilize Schema’s
      Simply use DBO schema instead of separating roles
    • c. Not Separation Data Access
      • Not Associate Authentication and User (if shared authentication) with appropriate roles and schema’s
      • Not Restrict Columns for User
      • Not Restrict Rows for User
    • Ad-Hoc queries
      • Access Databases using Administrative Credentials
12. Not Review Application Logs
13. Not Test Database Code (Triggers, Functions, and Stored Procedures)
14. Not Harden Your Database Service and Server
15. Perform work in the Database that can be performed outside the database
    ex. encryption, compression

If you’re not sure what I mean by any of these items, feel free to ask questions on my facebook page or send me an Email.

I hope to hear from many of you. The more participate we get, the better our TOP (10) list will represent real world problems.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Call the Cops
For this article let us consider a bank robbery as an analogy for how we can look at the actions of police. When a back robbery occurs, a bank teller is going to try to set off a silent alarm. This alarm is going to notify the dispatcher that there is a potential problem, the dispatcher calls in the police and they head off to the situation as fast as they can get there.

Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)