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…
- Relying on the defaults – oftentimes the default settings are not optimal and can cause performance problems (or operational problems)
- 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
- 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.
- 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.
- Not enough indexes – indexes 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).
- 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.
- 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!
- 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.
- 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).
- 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.