WHEATBRIDGE ::Database Design

DATABASE DESIGN AND BUILDING

Designing a database is a process, not an event, and the database itself is a system in the technical sense of the word: there are independent inputs, within the boundaries of the system there are numerous interactions, and there are outputs. Put another way, a relational database is a powerful abstraction that consists of a collection of facts and what is called a predicate calculus or predicate logic.

Getting a handle on the granularity of the data, the data types, and the relationships between and among the data sets can be a formidable task. Many years ago we had the good fortune to work with Dr. Joseph Novak who, with his team of researchers at Cornell University developed the technique of concept mapping. We've found it an excellent tool for visualizing the relationships between the data elements, the client's understanding of those elements, and the big picture of where we want to go with the data.

Having said that, no two projects are identical, so there are no hard and fast rules for designing the database. There are, however, best practices to consider and a number of heuristics we can apply. Once we have a map of the data concepts and know what we are designing for, our note-card approach is as follows. First, we gather all of the types of information we need to record. Second, we divide the information items into major entities or subjects, each of which then becomes a table. Third, turn information items into columns and specify the keys. Finally, set up the table relationships and apply the rules of normalization. (Normalization decomposes relations with anomalies in order to produce smaller, well-structured relations.)

While this isn't the place for a white paper on database construction, suffice to say we have built a number of enterprise-level databases that are recording and outputting data as you read. We build everything from simple, stand alone light-weight databases for device drivers, and enterprise level databases that can scale to multiple processors.