Database Top-Level View

Designing Database Tables – Part 6

Forward: In this part of the series, we look at database top-level view and we begin a second approach to determine tables in the third normal form.

By: Chrysanthus Date Published: 5 Aug 2012

Introduction

This is part 6 of my series, Designing Database Tables. In this part of the series, we look at database top-level view and we begin a second approach to determine tables in the third normal form. With this approach, some of your tables will be in a form higher than the third normal form, but I will not address issues higher than that here. I assume that you have the knowledge of all the previous parts of the series before you read this part. I call this new approach, the package approach of creating database tables.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Package
A package is a collection of related classes. That is, a package is a collection of related tables. In division 1 of this series, we saw many associations (relationships). The tables in a package are related by at least one of those associations.

Let us now think of a complete database of a company: The complete database is made up of connected packages. Open the following link in a new tab window to see an example of connected packages (come back to this page without closing the opened tab window):

http://www.broad-network.com/ChrysanthusForcha/Database-top-level-view.htm

Notice how the packages are drawn and how they are connected.

Example
I will use the example described below to explain the new approach to creating database tables. I call it the package approach. Here is the example: We consider a company that manufactures motorcycles and sells them. For simplicity we assume that the company does not only assembles parts to form motorcycles, but that it also manufactures all the parts for the motorcycles.

The diagram in the opened tab window is the top-level view of the database of the company. The database tracks the production of the motorcycles, purchasing of raw materials and sales of complete motorcycles. The packages in the diagram have been labeled as Sales, Motorcycles, Assembly, Employee, Purchasing and Location.

Each of these packages is a set of closely related tables. One package is related to another package through tables with one or more of the associations, we have learned. The connection between one package and the other is something you cannot really precise.

The connection between one package and another is in general terms.

The Purchasing package deals with the purchasing of raw materials. The company purchases raw materials and uses the raw materials to produce the parts. After the parts have been produced they have to be assembled. For simplicity, let us assume that the production of the parts in the company’s factory are automatic; so we do not need a package or a table that keeps information about the production of the motorcycle parts. So, in the top-level view there has to be a connection between the Purchasing package and the Assembly package. Note in the diagram that the arrow is pointing to the Assembly package. That is all the kind of information you need to know about the relationship between one package and another, before you draw the dotted line and arrow connection between the packages.

Producing the Database Top-Level View
There is no definite scientific procedure to come up with the top-level view of a database. To have the top-level view you have to meet the highest manager (CEO) of the company and probably some of the company workers and discus with them on how the company operates (or how the company will operate if it is a new company). From the discussion you get with them, you produce the database top-level view. At this point you do not need to know how the resulting tables will look like (or how they would be). However, once you discuss with them on how the company operates (business processes), you will be able to come up with a good database top-level view.

With big customers, more than one person from the software company will visit the different sections of the customer company and come up with the different parts of the top-level view. So you can produce normalized tables (tables that are in at least the third normal form) beginning from the top-level view. I call this approach the package approach (see below).

Producing Normalized Tables from Top-Level View
In division 1 of this series, you learned the different types of relationships. Precisely, you learned the one-to-one, one-to-many, many-to-many, n-ary, aggregation, composition, generalization, and reflexive associations.

Here is the secret. To create normalized tables from the top-level view, go to each package and for each package, check if it can be broken down into any of the above associations. You then go to each block in a broken down package and check if it can still be broken down into any of the associations. You continue this way for each resulting block in a package, until no new block can be broken anymore. In practice, you do not have to keep breaking down for too long in a package before you reach the final blocks. Those final blocks are almost at, at least, the third normal form. However, that is not all, you still have to establish the tables (final blocks), by giving them columns and keys. When you do that as I will explain, the tables will be in at least the 3NF.

Note: To break down a package, you may still have to dialog with the workers of the company who ask for the database.

For the following parts of the series, I will break down the Purchasing, Assembly, Motorcycle and Employee packages. To save time I will not break down the sales and Location packages. The Location package has tables that deal with addresses, cities phone umbers and states of the employees.

It is the responsibility of you, the database designer (and to some extent the customer who asked for the database) to decide on which names you will give to the packages.

Determining an Association
You know whether a particular association exists in a package depending on the business rules. We shall see illustrations of these in the following parts of the series.

Chrys

Broad Network

Related Articles

Database Top-Level View

Designing Database Tables – Part 6

Introduction

Related Links

Comments