Database Design for Developers

August 15th, 2008

Many people consider that the role of the database modeler on the development team is to merely identify the entities, attributes and relationships and throw in a few indexes for good measure.

While this can provide enough information for the development team to get started and complete their work, the ongoing application development, maintenance and documentation processes can all achieve significant improvements in efficiency, particularly when larger teams and longer development efforts are involved, by spending additional time on several other aspects of the database design. In this case, a little more effort put in at the beginning will yield significant savings over the life of the application.

The first thing that should always be done is to create and publish a document outlining database object naming conventions and rules. As a minimum, this document should include naming conventions for the following:

* Entities (usually singular nouns)
* Tables (usually plural nouns)
* Attributes and columns, in particular standardize the following:
o Identity columns
o Whether True/False or Y/N will be used when binary values are used
o How application-wide codes will be named, implemented and documented
* Indexes
* Primary, unique and foreign keys
* Stored procedures
* Constraints

The development of this document always results in a lively discussion as everyone has somewhat differing opinions of how the objects should be named and used. For the most part many different ways will work. However, the most important aspects are that this document is created at the very beginning of the design process and that it is rigidly adhered to, not only when the project starts but as enhancements and changes are made later on.

Why is this document important? First and foremost it will significantly improve the efficiency of the developers. They will know, without having to spend time looking it up every time, whether a binary value column uses the values ‘T/F’ or Y/N’ or whether a table’s name is plural or the name of the identity column. As new developers are added to the team, their ramp-up time is significantly reduced and they will be more effective, sooner and with less distraction to other developers.

One other area that should be decided during database design is how various application and system wide codes will be handled. In many instances an application ‘codes’ table is created with columns to sufficiently describe, identify and document the various codes that will be used.

Next time I will talk about using the database to ensure application-wide data integrity.