Data is normally organized into an information system. This system can be compared to something as simple as a loose-sheet binder, however this book describes the data design process in the context of computer-based information systems, or databases. Moreover, databases follow a design model, and we will use the most popular one—the relational model.
The complete data collection of an enterprise is larger than what our model will encompass.
We will build a model that represents only a subset of the data spectrum. The question is which subset? We'll see in Chapter 2 that we must set boundaries to the analyzed system's data scope.
To build information systems that last, data must be tamed and molded to correctly represent reality. Correctly here means:
Follow the needs of the organization, including the system's boundaries
Conform to the chosen data design model (here, the relational one)
Possess a high degree of adaptability to adjust itself to the changing environment
We owe to Dr. Edgar F. Codd the concept of the relational model, from his 1970 paper A Relational Model of Data for Large Shared Data Banks (http://www.acm.org/classics/nov95/toc.html). Dr. Codd later explained his model by defining a set of rules—the so-called Codd's Twelve rules (http://en.wikipedia.org/wiki/Codd%27s_12_rules). An ideal database management system (DBMS) would implement all those rules, but few if any do. But this is not a problem in practice since the benefits of the relational model are achieved even in products that do not apply all the rules. We are perfectly capable of building an efficient relational data design with currently available database products like MySQL.
When dealing with data design, I believe that the most important rules are number 1 and number 2. Here is a summary of these two Codd's rules.
This rule states that data is contained in tables. A table logically regroups information about a certain subject, for example, cars. The tabular format—rows and columns is the important idea here. A row describes information about a single item, for example, a specific car, whereas a column describes a single characteristic (or attribute) of each item, for example, its color. We will see in Chapter 3 that the decomposition of data into well-adjusted columns is important to have a flexible and useful structure.
The intersection of a row and a column contains the value of a specific attribute for a single item. We sometimes refer to this intersection as a cell containing our data—this is the same idea as in a spreadsheet.
Data is not retrieved or referenced by physical location—find the third record in this file. Instead, data must be fetched by referencing a table, a unique key—the primary key—and one or many column names. For example, with the cars table, we use the car serial number to retrieve this car's color.
This rule will be studied in Chapter 4, where we describe data grouping and the concept of choosing keys. Proper key choosing is of utmost importance.
Many years ago, I started to elaborate data structures using the relational model. I was using a method that could be summarized by this sentence: "determine where the data fits the best in the structure". Then I learned about the design techniques that were taught to IT specialists and evolved from the relational model.
The technique, which is frequently taught consists of building an entity-relationship diagram. In this kind of diagram, we represent nouns, for example, a car, a customer, using entities, and the relationships between them are expressed using verbs. An example of relationship binding two entities is "a customer buys a car". When the diagram is done, it must be somewhat transformed into a model consisting of tables and columns, using a technique called normalization that uses many steps to refine the model into an effective data structure.
These techniques produce reports, diagrams, and eventually a theoretical data design that can be implemented physically in a DBMS.
When I became familiar with those traditional techniques, I thought that for me at least they were a loss of time. Those methods teach a way but the ultimate goal—a working relational database and associated documentation can be achieved more directly. Moreover, those techniques suffer a problem: they cannot be applied blindfolded and mechanically. The developer always has to think about data naming, data grouping, and choosing keys while trying to balance users' needs and constraints imposed by:
the hardware
the chosen database management system
planned growth
time
budget
I realized that the traditional techniques are taught everywhere, and I respect the teachers who teach them. But believe me, when it's time to deliver an application notwithstanding the interface itself, it's important to avoid losing time to intermediate by-products and go straightforward to a working prototype. Using a more direct method during the data design phase frees more time to refine the interface, to catch unforeseen needs and address them.
This book's goal is to teach the minimum principles one has to apply in order to build an effective data structure.