Top Ten Most Common Physical Database Design Mistakes

Continuing with the recent trend here in terms of posting Top Ten lists, today I offer up the Top 10 most common physical database design mistakes…

  1. Relying on the defaults – oftentimes the default settings are not optimal and can cause performance problems (or operational problems)
  2. Not basing the physical on a logical model – every database should begin its life as part of a logical data model; failure to do so will probably cause data integrity issues
  3. Over-relying on logical design – I sometimes call this the dreaded disease of ERwin-itis; you know, where the DDL is spit out by the design tool and no changes can be made. This can be as bad as no logical model.
  4. Normalization problems – could exhibit itself as too much denormalization… or even over-normalization. Be sure the design is usable given today’s DBMS capabilities and your organization’s requirements.
  5. Not enough indexesindexes optimize data access – build as many of them as you need to assure optimal performance (without causing data modification issues… because indexes need to be modified when the table data is updated/inserted/deleted).
  6. Indexing by table, not by workload – indexes should be created to optimize your query workload. Yet many still create indexes when creating tables… well before the SQL to access the data is known.
  7. Too much (or not enough) free space – use free space appropriately. Free space provides room for data to grow in between reorganizations. Don’t specify too much free space (or queries that must read multiple pages of data may not perform well). Don’t specify too little free space (or data may become disorganized too quickly). And if the data is static, then don’t specify any free space at all!
  8. Failing to plan for data purging or archiving – if you never plan to remove data from your tables then they will grow and grow and grow and… eventually, they may become unmanageable.
  9. Failure to share data – Not IBM DB2 Data Sharing, but sharing data. Databases are designed for sharing data among users and applications. Failing to share data is the reason you may have 287 different customer databases (for example).
  10. Kludging – By kludging, I mean trying to avoid a certain feature/function and instead using something in a way it was not intended…  for example: avoiding NULLs at all costs, storing numbers in CHAR columns, storing dates in CHAR columns, etc.
Advertisements

About craig@craigsmullins.com

I'm a strategist, researcher, and consultant with nearly three decades of experience in all facets of database systems development.
This entry was posted in DBA. Bookmark the permalink.

4 Responses to Top Ten Most Common Physical Database Design Mistakes

  1. Pingback: DB2 Hub | Top Ten Most Common Physical Database Design Mistakes

  2. Pingback: Top Ten Most Common Physical Database Design Mi...

  3. Good list for people to keep in mind while designing databases. You may want to add non-atomic column values, such as storing city name, phone number, street number, etc., in one column, as part of Kludging!

  4. NBA says:

    Nice Blog, thanks for sharing this kind of information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s