Anyone familiar with the Oracle (or any) database environment knows that it is a good practice to use Dr. Codd's normal forms (usually 3rd for OLTP and at least 2nd normal for data warehouses) for design and layout of relational databases. Once you have established a good logical design, you denormalize for performance in your physical design. These normal form diagrams, commonly known as entity relationship diagrams (ERDs), should be active living documents that are maintained over the life of the database.
Another good practice that aids with performance is to pre-aggregate data with materialized views, summaries or whatever you wish to call them. Oracle automatically maintains properly created materialized views.
The next design good practice is critical to a successful project -- use source control, either a third-party tool, Oracle-provided source control or manual logging methods. Proper use of a source control package eliminates last-minute panic-builds that rob time and efficiency. Many times, use of a proper source control package will minimize reinvention of existing functionality.
Good design practice also requires that you develop and use naming conventions. Naming conventions should be implemented for variables, programs, procedures, packages, tables, columns, indexes, views and clusters (as a start!) to prevent confusion and enhance readability and maintainability of code.
Hand-in-hand with naming conventions goes the use of a standardized coding convention. Proper coding standards allow multiple developers to maintain the same code sets without inducing complexity by multiple (or no) standards being used. Coding standards allow for readable, maintainable code that can be read and understood long after the original programmers have left.
Another design good practice is to use the proper design technique for the proper environments (normal forms for OLTP, STAR for DWH and CUBE for DSS). It is obvious that the use of improper development methods will result in poorly designed and inefficient databases.
The final good practice in this area I would like to mention is probably the most overlooked and, oddly, one of the most important -- each project should have a carefully defined scope, project plan and deliverables. If you don't have a proper project plan, how will you know when you are finished? Without proper milestones, how will you measure project status? Finally, without deliverables, how will you know when a milestone is reached? A proper plan tells what needs to happen and the order in which things need to be done. For example, before equipment engineers are scheduled to install equipment, you better make sure the equipment is onsite; before a database is scheduled to be installed, the equipment had better be onsite and installed; and before you can code, the database better be designed and ready for the coders to utilize. On a recent project I was associated with, a very major upgrade, the project plan was done on-the-fly. Consequently consultants where brought onsite before they were needed, there were numerous delays because various departments didn't realize what was required when and many milestones where missed or delayed as a result.
Return to Mike Ault's Oracle "good practices."
About the author
Mike Ault is an Oracle database specialist at Quest Software and a recognized Oracle expert with over 16 years' experience as an Oracle DBA and consultant in a variety of industries and companies. A prolific author, Mike has published over 20 Oracle-related books including Oracle Administration and Management, Oracle DBA OCP ExamCram and Oracle10g Grid and RAC. He is a regular contributor to trade publications including Oracle magazine, and frequently presents at major Oracle conferences such as IOUG.
