Effective Database Design in Complex Systems

Effective database design in complex systems is the skilled balancing and compromises taken of three aspects; design standards, processing speed and information requirements (Coronel, Morris & Rob (2009)).

Database design standards are subject to the technology used, which dictates required technical standards, and best practices, which is gained by the designer through training, experience and not forgetting individual ethics based on working pressure (i.e. whether or not the designer is able to dedicate sufficient time and resources to adequate planning).

In terms of design standards it is, of course, paramount that technical standards are followed to ensure that the resulting database functions properly however just important are those best design practises such as creating and following business rules, minimising data redundancy, ensuring integrity of all kinds and presenting components in an understandable fashion (e.g. descriptive naming) and logical manner (e.g. breaking fields down to atomic levels). Such best practices, if not adhered to, affect the other key database design aspects however there are additional design considerations outside best practice which can affect the goal of a database.

The level of complexity and size of the database are important considerations as without taking these into account, even the most perfectly designed database when considering elegance and information requirements could be unusable. For example, database standards dictate that the relationship between attributes should be accurately mapped, however the lower the number of relationships introduced into the schema, the faster the processing speed, a direct contradiction in terms of design and one where compromises would have to be made.

Finally, the information requirements of the database must be examined to establish what is important to the intended uses of the database and the attribution detail adjusted accordingly to maximise usefulness and minimise processing overhead whilst adhering to best practices. For example, it would be costly from a processing point of view if the database stored values that were calculated as the result of form input or automatic process that could be repeated on the fly.

In my practical industry experience I have witnessed more database designs achieving their goals where the designer had such experience that they were able to visualise and articulate the majority of the schema and rules before any discussion with the client and to adjust those designs based on initial discussions, rather than prescribing a process for them to follow, perhaps this is finding the right designer who can produce best practice.

In conclusion, proper design includes a lot of time in planning the data considering the ultimate objectives of usability and usefulness. Non-functioning, slow and non-business focussed databases show a lack of consideration in any of the above areas and are likely to lead to a database that not only is not used to its full potential but does not achieve its objective and, ultimately, will be unused.

References

Coronel, Morris & Rob (2009) Database Systems: Design, Implementation, and Management (9th Edition). Cengage Learning.

U.S. Small Business Administration (2008) Database Design Standards [Online] Available at http://www.sba.gov/idc/groups/public/documents/sba_program_office/sba_ocio_db_design_standards.pdf (Accessed 23 April 2010).