May 18, 2014 by Ozgur Ozden
Normalization in a database can be achieved by two different process namely Top-Down and Bottom-Up as described by Ceri (1987). But before we discuss the advantages and the disadvantages of bottom-up and top-down processes, I would like to briefly touch the topic of normalization.
Very broadly, normalization can be described as organizing the data in the database to function efficiently and reliably. There are two benefits of normalization. First one is to get rid of data repetition, namely data redundancy and the second one is to make sure that tables have the correct relationships established between them. There are some levels of database normalization as 1NF, 2NF, 3NF ( we may add 4NF and 5NF here as well but they are very rare like scientific and statistical cases) and BCNF.
1NF (First Normal Form) focusses and organizes the following points in a database
Removal of duplicate attributes, columns in the tables. ( i.e you should not include two columns like “date of birth” and” birth date” listed in the same table)
Identification of primary keys in these tables and columns. ( such as setting SSN as a primary key creates unique rows, entities in the table)
2NF (Second Normal Form) Includes the properties of 1NF as described above as well as the below mentioned properties.
Removal of duplicated data and creation of new child tables.
Creation of relationships between these new child tables and assigning proper foreign keys.
3NF (Third Normal Form) Just like 2NF, 3NF includes the properties we have described above in 2NF and additionally requires the following feature.
Removal of the columns, attributes in the table that are not directly related to the primary key.
BCNF (Boyce-Codd Normal Form, 3.5NF) In addition to 3NF, BCNF requires the following.
Each determinant have to posses a candidate key.
After all these brief introduction now we can take a look at the details of the processes Top-Down and Bottom-Up.
The process of depicting real world objects into ER model together with its entities, attributes and its relationships between them is called Top-Down process. Simply we start with a big picture of the database, what is needed, and move all the way down by dividing into smaller segments.
Once the ER model is created and converted into the tables, we have our database created. But at this point we need to use normalization to check the design flaws in the database. Since we start from the general real world example, en user is extremely important in top-down method to be able to store proper data in the database about the business rules and practices. But as Burleson (2009) describes “In some cases, top-down design can lead to unsatisfactory results because the analyst and end-users can miss something that is important and is necessary for the system.”
This design is valid for existing databases. Bottom-up approach starts from the specific details and moves up until reaches to a general picture. Usually database designer starts from analyzing the reports, sample screens, and forms needed or used by the end user, then decides what sort of information must be stored in the database to achieve this goal as illustrated below.
Bottom up approach is also a suitable system for migrating database from one system to another.
What are the advantages and disadvantages of each method?
The main advantage of the top-down process is to provide a good visualization of the database and provides end user input at every step of the progress. It helps ease to understand the whole system and the logic behind it such as business rules and practices. This design is also easy to maintain.
But as we have explained before, top-down method requires full and healthy communication between the end user and the database designer. Also integration cost might be higher compare to bottom up design. These points might be considered as downsides of the top-down method.
Bottom-up design mainly targets on data and its dependency so this can be considered as an advantage.
One disadvantage for bottom-up design is that the design is initiated by the existing system not by the needs of the business or the end user.
Which would you prefer to use?
I am really a data driven person so I probably prefer top-down design.
Is it really and either-or situation?
I think there is no right answer to this question. Decision should be made depend on the project and its requirements. Top-Down method puts the business rules, practices and requirements as a first priority, on the other hand bottom-up design places the emphasis on the data and its reliability. Sometimes the best solution might be combining these two methods as Centrilized Design.
Coronel, C., Morris, S. & Rob, P. (2013), Database Systems: Design, Implementation, and Management, 10thed, Boston, MA: Course Technology, Cengage Learning.
Burleson (2009) Top-down vs. Bottom-Up Object Database Design (online) Available at: http://www.dba-oracle.com/t_object_top_down_bottom_up.htm [Accessed on 4.10.2013]
(2012) Top-down database design approach (Online) Available at: http://newtutorial2012.blogspot.com/2012/07/top-down-database-design-approach.html [Accessed on 4.10.2013]
Kung (2012) Comparing Top-down with Bottom-up Approaches (Online) Available at: http://proc.isecon.org/2012/pdf/1910.pdf [Accessed on 3.10.2013]