SQL Theory: Top-down Vs Bottom-up Database Design

Top-down vs. bottom-up database design (by Wolfgang Köhler, Memmingen, Germany 7/2009)

I would like to start this discussion out with a graphic from Rob (2008, p.510) that shows what is meant by these two approaches:

The idea behind the top-down approach is that the database designer gets a good overview over the customers business, the business practices, business rules and reporting requirements. He then takes this information and creates an entity-relation diagram, with the help of the Chen or Crow’s Foot notation as discussed last week. At first this will be a very rough diagram just showing entities and relationships, for example the different department in the company and how they interact. Discussions with the customer then lead to a ever finer grained model that at the end will be used as the bases to create a relational database. Burleson (2009) however points out: “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.”

 

The main advantage of the top-down approach is the visualization of the system which can be used for discussions with the customer as well as for progress tracking.

IBM.com (2009) reinforces that by saying: “User and business awareness of the product. Benefits are realized in the early phases.” This makes it a prime candidate when developing a new business database.

Contrary the bottom-up approach starts with the data and goes on to develop the structure around it. If we take for example a table we can use the normalization steps to split this one table up into multiple tables which help to prevent anomalies as well as redundancies thus ensuring data integrity. Because of the methodical approach this will lead to a technically correct database design because nothing is left to chance. However it can not ensure that the database is good thus meeting all the informational requirements the customer might have.

Burleson (2009) mentions the field of analytical statistics as an area that would have to use the bottom-up approach since all that is known in the beginning are a vast amount of data from different tests. But also a business database could be developed this way. In that case the database designer would use existing reports, data collection and possibly databases and combine them into a new system rather then relying on information given by management.

As we can see there is no really good answer to the question: “Which would you prefer to use?” As so often – it depends. Using statistical data I think there is no choice but to use the bottom-up approach, however when creating a business database I would think the best choice would be a mix of both approaches. An entity-relation diagram could be drawn up and detailed in conjunction with the customer. To avoid forgetting some important facts however it might be wise that the database designer also takes existing reports, forms and databases in consideration. Once the first draft of the database structure is completed normalization should be used to verify that it conforms to 3NF to avoid any problems arising from anomalies and redundancies.

Top-down vs. Bottom-Up Object Database Design Oracle Tips by Burleson Consulting

Object Database Design

There is still a great deal of controversy about the best way to approach database design for object-oriented systems.  Architecturally, some experts argue that the relational model is not well suited for use in an object-oriented environment while other experts maintain that relational architectures are more suitable for traditional data processing.  This has been borne out in the marketplace where we see object-oriented databases used for non-traditional applications such as telephone billing system, while the relational model enjoys predominance in business administration applications.

It is important to recognize that many of the "pure" object-oriented database management systems do not exploit many of the features of "classical" database management.  To some object-based systems, the only purpose of an object database (OODBMS) is to provide "object persistence" and very little attention is paid to concurrency-control, rollback and recovery, and the other features associated with relational database management.

One very important point:  A database does not have to support all of the formal constructs of the object-oriented approach to benefit from using the object-oriented approach.  For example, while object-oriented programming languages such as C++ allow for the creation of abstract data types, the data types offered in most commercial database systems such as, CHAR, INTEGER, REAL, VARCHAR, and BIT are sufficient for almost all database applications.

However, the relational vendors recognize the shortcomings of their architecture.  For example, almost every commercial relational database vendor has promised to deliver user-defined data types in their future releases.  Oracle, the popular relational database for midrange computers, has announced that Oracle version 8 will support abstract data typing.  This topic is fully discussed in Chapter 5, "Relational Database Objects and User-defined Data Types".

One must remember that the main difference between object-oriented and traditional systems is the idea that in an object-oriented system both data and behavior are stored in the DBMS. In an object-oriented database, instances of a class may behave differently depending upon the processing circumstances.  Consider a simple example of how an object-oriented database may differ from traditional systems.

In a traditional database system, all instances of an order record would share the same data items and processing characteristics.  Under object-oriented databases, an order will contain not only the order record itself, but the relevant behaviors that are associated with the order.  For example, there may be "rush" orders that exhibit different behaviors than would a "COD" order.

Now that we understand the basic precepts of object database design, let's take a look at the major approaches to adding physical details to the logical object model.

Top-down vs. Bottom-up object database design

There are two approaches for developing any database, the top-down method and the bottom-up method.  While these approaches appear radically different, they share the common goal of uniting a system by describing all of the interaction between the processes.  Let's examine each approach:

The top-down method starts from the general and moves to the specific.  Basically, you start with a general idea of what is needed for the system and then ask the end-users what data they need to store.  The analyst will then work with the users to determine what data should be kept in the database.  Using the top-down method requires that the analyst has a detailed understanding of the system. The top-down method also can have shortcomings.  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.

The bottom-up approach begins with the specific details and moves up to the general.  To begin a bottom-up design, the system analyst will inspect all the interfaces that the system has, checking reports, screens, and forms.  The analyst will work backwards through the system to determine what data should be  stored in the database.

To understand the differences between these approaches, let's consider some jobs that are bottom-up in nature.  In statistical analysis, analysts are taught to take a sample from a small population and then infer the results to the overall population.  Physicians are also trained in the bottom-up approach.  Doctors examine specific symptoms and then infer the general disease that causes the symptoms.

An example of jobs that require the top-down approach include project management and engineering tasks where the overall requirements must be specified before the detail can be understood.  For example, an automobile manufacturer must follow a top-down approach to meet the overall specifications for the car.  If a car has the requirement that it cost less than 15,000 dollars, gets 25 miles per gallon, and seating five people.   In order to meet these requirements the designers must start by creating a specification document and then drilling down to meet these requirements.

The analyst will have no choice but to talk and work with the users to determine what is important to the users and as a result determines what data should be stored in the database.  What the analyst usually does is create some prototype reports, screens, and forms to help the users visualize what the system will look like and how the system will work.