Pitfalls in Relational Database Design
By Sue Smith
Creating an effective design for a relational database is a key element in building a reliable system. There is no one "correct" relational database design for any particular project, and developers must make choices to create a design that will work efficiently. There are a few common design pitfalls that can harm a database system. Watching out for these errors at the design stage can help to avoid problems later on.
Careless Naming Practices
Choosing names is an aspect of database design that is often neglected but can have a considerable impact on usability and future development. To avoid this, both table and column names should be chosen to be meaningful and to conform to the established conventions, ensuring that consistency is maintained throughout a system. A number of conventions can be used in relational database names, including the following two examples for a record storing a client name: "client_name" and "clientName."
Lack of Documentation
Creating documentation for a relational database can be a vital step in safeguarding future development. There are different levels of documentation that can be created for databases, and some database management systems are able to generate the documentation automatically. For projects where formal documentation is not considered necessary, simply including comments within the SQL code can be helpful.
Failure to Normalize
Normalization is a technique for analyzing, and improving on, an initial database design. A variety of techniques are involved, including identifying features of a database design that may compromise data integrity, for example items of data that are stored in more than one place. Normalization identifies anomalies in a database design, and can preempt design features that will cause problems when data is queried, inserted or updated.
Lack of Testing
Failure to test a database design with a sample of real, or realistic, data can cause serious problems in a database system. Generally, relational database design is started from an abstract level, using modeling techniques to arrive at a design. The drawback to this process is that the design sometimes will not relate accurately to the actual data, which is why testing is so important.
Failure to Exploit SQL Facilities
SQL has many capabilities that can improve the usability and success of a database system. Facilities such as stored procedures and integrity checks are often not used in cases where they could greatly enhance the stability of a system. Developers often choose not to carry out these processes during the design stages of a project as they are not a necessity, but they can help to avoid problems at a later stage.
References
Writer Bio
Sue Smith started writing in 2000. She has produced tutorials for companies including Apex Computer Training Software and articles on computing topics for various websites. Smith has a Master of Arts in English language and literature, as well as a Master of Science in information technology, both from the University of Glasgow.