Learn how to Work Smarter

Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Wednesday, December 9, 2009

Using strict keys

I finally modified the relationship between wells and water levels today; this was the troubling design:


...corrected to this:



Of course this made more trouble, adding and then correlating water levels with the new well screen identifiers (I just used "001" to replace all the Null values in ScreenIntervalID).  The next issue was a bit embarrassing -- my nice, clean data in tblGroundwaterLevel ... not so clean.  Found a number of violations, nulls, and weird missing data in this table.  Not surprising when accumulating data from a dozen different sources for one analytical tool, I have to constantly remind myself.

Enter the index.  This is a way of enforcing rules between records of data.  Check out UniqueKey; it's telling the tblGroundwaterLevel that records cannot repeat these four fields:
  • LocationName
  • ScreenIntervalID
  • LoggingDate
  • MeasuringMethod
Note that LoggingDate is a date/time field (as usual in Access/most DBs), so, if there are transducer readings at 8 am, 12, and 4 pm, each LoggingDate record is unique ("mm/dd/yyyy 08:00", "mm/dd/yyyy 12:00", and "mm/dd/yyyy 16:00").

Additionally, including "MeasuringMethod" guards against a key violation if there's a manual measurement at exactly the same moment as a transducer measurement is made, providing the data manager specifies different methods for the different data.


Keys guard against:
  • duplicate records
    • ex: importing from two different Excel files with overlap; or
    • importing the same transducer file more than once.
  • incomplete records
    • blank LoggingDates (this was a problem bringing in data from a northwest-based regulator)
    • blank levels
You'd think including StaticWaterDepth would be a good idea, but this introduces two problems:
  1. What to do when the DryWellFlag is true -- include a sounding for StaticWaterDepth?  Fake a value?
  2. We're guarding against duplicate WellID (LocationName/ScreenIntervalID) entries for the same date -- another issue I've seen importing data from a variety of sources is conflicting info for the same date/well -- so you want to catch imports like this:



(Wrinkle: while I could have -- probably should have -- made this the PrimaryKey (ensuring no nulls or duplicates in the future, and probably better compatibility with Access 2010), calling it a UniqueKey was easier because I could ignore nulls for a moment; save the index; go fix duplicates and Null ScreenIntervalIDs; and come back to enforce the index.)


Now to implement this in our template DB.

Tuesday, December 8, 2009

Spreadsheets are not databases.

Normalization -- what is it, and why is it important?

What:
A series of rules -- that's what database normalization boils down to.  I have had a terrible time trying to distill the "why" of database normalization, largely because I get all excited about the "what!"  So, in more concise terms than I can muster, enter the Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization

Why:
Databases start small, often with an Excel spreadsheet.  But -- this is important --  they're designed to get big.  Because many of us manage datasets with more than 60,000 records -- or will at some point.

How do these rules help big data sets?  Two ways:
  • managing data "types"
  • enforcing rules within a row, or record
  • enforcing rules between rows and
A data type is a date, a currency amount, etc; think of Excel's "format" function and you get the idea.  This rule persists throughout the column

Three levels of normalization:
http://www.troubleshooters.com/littstip/ltnorm.html

More depth on our target level of normalization:
http://en.wikipedia.org/wiki/Third_normal_form

Important translations:
Column => Field
Row => Record
So that's where we'll begin.  I'm going to go hop on a plane in a minute.
Home | My Schedule (Free/Busy) | Professional CV | Learn how to Work Smarter

A Little More Background

Friends & Followers