The Business Model and Database Design

What is a "relational database"? You can look it up on Wikipedia:

A relational database is a digital database whose organization is based on the relational model of data, as proposed by E.F. Codd in 1970.^[1] This model organizes data into one or more tables (or "relations") of rows and columns, with a unique key for each row. Generally, each entity type described in a database has its own table, the rows representing instances of that type of entity and the columns representing values attributed to that instance. Because each row in a table has its own unique key, rows in a table can be linked to rows in other tables by storing the unique key of the row to which it should be linked (where such unique key is known as a "foreign key"). Codd showed that data relationships of arbitrary complexity can be represented using this simple set of concepts.

The definition goes on to explain the differences with hierarchical data structures, etc.  Perhaps technically correct but doesn't tell the whole story.  To me the relational database is used to define how logical subsets of data are related and how they defend the integrity of the business model the database supports. 

Data Tables

In general the tables in a database should be "as little as possible" consisting of the least number of columns possible to define a unique record.  The link to other tables defines fundamental relationships between the data - like "parent-child" for example.  When constructed the entire database defines not only these relationships, but how data flows through the business process that supports it.  A well designed database defines the entire business model and can accommodate changes and additions with minor modifications.  This can happen when the designer spends enough time with his or her feet on the ground to understand the business process, and creates data structure that is granular - almost molecular - in it's composition. This takes the most time to create, but also creates the most flexible and long-lasting structure.  There are many other considerations, but there is no substitute for the really hard work of defining the business model with the database. 

A relational database is not a spreadsheet - or a collection of spreadsheets.  A spreadsheet makes sense for 2-dimensional representation of data and is used primarily to inform the human eye.  It works great for the human eye because we can quickly relate to the two-dimensions and peer down into the individual data pieces.  But it's not efficient for a computing engine - something that is designed to find and extract pieces of information as quickly as possible.  The example below shows how the eye can quickly find a measurement by triangulating between dates in the rows and the instrument in the columns. Suppose you were asked to find OW-12 on 7/20/94:

While the eye can do this in an instant, it's very inefficient form of data storage.  One way
to understand why is to look at the column headers.  They are unique for each instrument so they essentially require a custom data structure.  If you add a new measurement, you change the table structure.  Every time you search you don't know which column the result will be in.  Compare that structure with the following.

The table above would be used in a relational database to store the data shown in the spreadsheet. The data has been 'normalized' to minimize data redundancy and provide an efficient search path.  It consists of only three columns no matter how many instruments you have.  The first two columns define a unique record - the date and the sensor name. To find the record we found in the spreadsheet we work from left to right to find the day, then sensor, and then the value.  Not as easy for the eye perhaps, but easier for a database.

So how is this structure used to define a Business Model?  By first defining what constitutes a unique record in a table (called Primary Keys), and then creating relationships between tables, you define how data will be used to define your business model.

Relationships

Example - Customers, Contracts and Plants

Business Model 1

Lets assume your database is defining a customer, the contracts you have with that customer, and the plant where the work will be done.  Maybe you first consider a simple business model like:

" Plants and Contracts belong to a Customer.  Multiple Projects can be grouped under Contracts"

This can be represented with a simple organization chart as follows:

Business Model 2

What if another customer then presented you with another business model scenario, like:

 

"Project Numbers are specific to Plants, with the possibility of multiple projects under a single contract"

 

You need to be able to define something like the structure shown in the figure below where Plants are related to Projects:

 

 

 

Business Model 3

Then another customer presents you with another business model:

 

"Two separate customers with their own contracts and projects are using the same plant."

 

 

 

 

Unless you want to spend all your time programming, you want your database design to be able to represent and enforce the integrity of ALL the business models you have to support. The figure below shows an actual database design that supports the above scenarios.

 

 

 

 

It's not as complicated as it looks.  It almost looks like the data structure for Business Model 1.  The key is that Plants is not connected directly to Customers.  It is linked to both Projects (Contract_Projects) and to Customers.  The link to Customers is not direct either.  It is using a "relationship-table" where the relationships between plants and customers is defined.   All the above relationships defined by business models 1 through 3 are supported by this structure - without data redundancy.

 

 

Monitoring Dam Safety

Monitoring a dam is a lot harder than you think.  Some might think that it's easy because, in general, dams don't move.  But it's precisely for this reason that dam monitoring is difficult and requires a special discipline.  How to stay interested when there isn't anything interesting going on?

Did you know that many dams in the US have reached or exceeded their design life?  We don't build many new dams because it's difficult to get a dam through the approval process, and partially because this is so, the cost is excessive. We are increasingly dependent on aging dams (like our aging infrastructure as a whole).  If a dam is past it's design life it isn't necessarily in danger of failing.  In fact in all likelihood most well-designed and built dams will still be sitting there when the lake behind it is filled with silt.  But predicting behavior in an earthquake, for example, becomes difficult. We can't say with certainty that a dam 10 years past it's design life of 50 years (60 years old) will behave a certain way in an earthquake - since we don't have similar observation to go by.  So we must watch our dams a little more carefully.

How do we watch a dam?  In general there are four basic types of measurements one can take to monitor the long term condition of an embankment dam:

1. Pore pressure measurements
2. Surveying surface points
3. Seepage measurements
4. Visual observation

The first three methods can be automated and often are.  Of these three, pore pressure measurements is the simplest way to get a direct measurements of the dams current condition compared to its theoretical design.

Pore Pressure Measurements
The proper design of a dam requires an understanding of how water will ultimately flow through the dam.  As the diagram shows below, the idea is to NOT let the phreatic surface, or top of the saturated zone, reach the toe of the dam with enough pressure behind it to flow with destructive force.  Many dam designs incorporate clay cores and gravel drains to prevent any seepage to reach the front shell of the dam.  Many dam monitoring systems use buried pressure transducers - called "piezometers" - to measure the pore water pressure in the dam and define the phreatic surface.  These devices are buried in a dam and can measure water pressure even with very small amounts of water being present.

Figure 1 - Flow net through embankment dams with and without drain blanket

 



In general, one would like to see measured pressures fall within certain operational limits based on reservoir head and the location of the pressure sensing element.  A pressure sensing element placed anywhere within a dam should read close to the design pressure - called the design phreatic surface.  In the event of an earthquake (if the dam is located in a seismic zone) then the before and after pressures should be the same.  If they are not then one has to quickly determine why.  A certain amount of increased pressure might be expected in the less permeable portion of the dam due to  pore pressure build up during seismic shaking.  But if the dam structure fails in any way that allows a more direct connection to reservoir head, this could cause critical failure. The plot below shows historic pressure readings in a dam in Northern California compared to reservoir elevation data.

Figure 2 - Historic piezometer and reservoir elevation plot

 

 

Historical Trends

Having piezometers wired up to a recording system allows pore pressure to be monitored at regular intervals in all conditions.  This provides a historical perspective for not only evaluating dam safety, but also understanding the actual conditions within the dam.  A piezometer like that shown in Figure 2 shows an attenuated response to reservoir level, but at a lower pressure level than full reservoir head.  This is normal response for a piezometer located in the interior of the dam.

 

Some piezometers located above the phreatic surface will exhibit no response to reservoir head (Figure 3) and some in highly permeable zones (for example in the dam abutment) will mimic reservoir level when water levels are above their tip elevation (Figure 4). 

 

Figure 3 - Historic piezometer plot with no response

 

Figure 4 - Historic piezometer plot that follows reservoir level

 

 

All of these piezometer time series provide a signature - like an electrocardiogram (EKG) does for a heart - of the interior conditions within a dam.  From the outside a dam may look static and unchanging, but a electronic monitoring of the dams interior pressure provides a more dynamic picture - one that changes with the seasons and with age.  It's an interesting and invaluable perspective for assessing the health of our aging dams. 

 

References:

http://www.ferc.gov/industries/hydropower/safety/guidelines/eng-guide/chap9.pdf