Entity Relationship Modelling & Normalisation

Entity relationship modelling and normalisation is the second step in conceptual database design, after data analysis and requirements gathering, according to Coronel (2009).

Using a “top-down” approach, high-level requirements are developed (business rules, entities, relationships, attributes and keys) and initial entity relationship diagrams drawn followed by normalisation. This is often the starting point to new database designs, as opposed to extensions to existing designs as existing “normalised” data and indexes do not exist and therefore there are no rules to follow as long as the system achieves its objective. This can be a disadvantage however as any issues not identified until normalisation can cause a design re-think, which, especially in commercial projects, can be costly.

Using a “bottom-up” approach, the database is generally designed starting at the identification of components, keys and functional dependencies. This is often an ideal approach when extending existing systems with new functionality as existing designs must be adhered to and provides the great benefit of identifying potential functional issues early in the design and it also encourages the identification of reusable data (object orientation). The difficultly with a pure “bottom-up” approach is that, especially in more complex systems, it becomes difficult for the designer to ensure that the designs suit the purpose of the system as a whole and that everything works together as intended.

In database design I am primarily a top-down designer as I am seldom the person who actually does the hands on development (not that I should not consider normalisation, this is a process handed down to development teams); I therefore generally consider high level requirements and use my experience to guide me in whether or not something will actually work. This does not mean that normalisation does not take place as it is a necessary step in ensuring that (at the very least) data redundancy is minimised, updating anomalies avoided and referential integrity imposed, despite there sometimes being a performance price to pay (e.g. joins). In smaller database designs the formal step of normalisation does not exist however normalisation through experience in design still takes place.

References

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

Purcell, J (2007) The SANS Institute: Comparison of Software Development Lifecycle Methodologies [Online]. Available at http://www2.giac.org/resources/whitepaper/application/217.php (Accessed 02 May 2010).

Tech Republic (2002) To be or not to be normal: That is the database question [Online]. Available at http://articles.techrepublic.com.com/5100-10878_11-1045284.html (Accessed 02 May 2010).