Skip to main content

Spreading the Sheets and Letting Relations In

This post is part of an ongoing series
  1. Design for Exceptions
  2. Spreading the Sheets and Letting Relations In
  3. Forming a New Reality
There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS.

In my last post I went off on relational database design patterns. Now let's try implementing these patterns in MS Access.
accdb iconDownload the MS Access 2007 Project used in this post.

In the old days we made spreadsheets. And they got ugly really quick. Let's work with our city-zipcode analogy from last time.

We started with what is basically a spreadsheet in MS Access:

We outgrew this quickly when we added Meridian:

I didn't mention this in my last post, but I see this all too often. Basic database design cue, if you're numbering your columns your design is wrong.

Why? Boise has 29 zipcodes. New York has 161. Washington, DC has 278. Do you really want 278 columns for storing zipcodes?

So you try something else. You create what really is the right pattern, but it lacks referential integrity. If you could spell and type perfectly it would be ideal.

Ok, the answer is "in view" or "in query" for an MS Access world. This is what we want to come up with, it just isn't maintainable in the real world. Take Coeur d'Alene, Idaho for example. In our main database at work there is 34 ways to spell Coeur d'Alene because someone left town a textfield with no zip code validation. What we need to do is break this table into a discrete entity. We only have to type Coeur d'Alene once and numerical dyslexics like me only have to type zip codes once. We throw a table in the middle that is not human-readable that ties the primary keys (unique autonumbers that mean nothing) into the middle table to tie together this many-to-many relationship. Ideally, humans don't even view this table, it sits in the background and stores the relationships human define using forms looking at real world data (city names and zipcodes in this case).

Design View

Datasheet View

* There's some cool things going on here. Since zip codes in New England start with leading zeros, I've made zipcodes a text field. Uniqueness is being enforced through an Index that doesn't allow duplicates and an Input Mask is requiring all zipcodes to be five digits. With this setup both "123" and "ababa" would not be allowed. Additionally we couldn't add "83714" twice. This is being enforced at the table level so all forms, queries and other programs (ArcGIS) that may use this table will have to comply with these validation rules.

Now we relate these together. In MS Access 2007, go to the Database Tools tab and select Relationships. Click Show Table to add your tables and then drag and drop your relationships.

Your tables should already have autonumbers set up as Primary Keys for Cities.CityID, ZipCodes.ZipCodeID and Cities_ZipCodes.ID. When you make the relationships choose the following checkboxes:
  • Select Enforce Referential Integrity - This will ensure that you do not add a CityID or ZipCodeID to Cities_ZipCodes that does not exist in the main Cities or ZipCodes table.
  • Do not select Cascade Update Related Records. We we do not need to cascade updates because we are following best practices and not allowing our primary keys to mean anything in the real world. If you were to use zipcode for example as your primary key you would have to set this, because invariably zipcodes will change and your primary keys will have to change. When this is your model bad things will happen. Not right away. After you have thousands of records. Don't let your primary keys mean anything. Choose long integers or guids. Period.
  • Cascade Delete Related Records - This will delete related records in Cities_ZipCodes when you delete a City or ZipCode from the main Cities or ZipCodes table. This will prevent orphaned records in Cities_ZipCodes. Having recommended this, I will add that this is a dangerous setting for scenarios where inexperienced users have the ability to delete records as the entire history for a record could be deleted, e.g. the 278 zipcodes for New York City if a user were to delete the one record for New York. Make sure your security settings match your users. And consider not really deleting anything, just adding a flag that makes data appear to disappear for most users.

Save and close. Now with one query we can recreate the table dynamically that we had above without 34 misspelling of Coeur d'Alene.

In MS Access 2007 Select the Create Tab and click Query Design. Now use Show Table again to add your three tables. Auto-magically they will relate to each other using the rules you just added in the Relationship Diagram. All that's left is to double-click (or drag) Cities.City and ZipCodes.ZipCode and they will drop into the results area.

You can also swing over to the SQL by clicking an icon in the lower right. When you're a big boy you may come here first, but we all start GUI (it's a fact ask a mid-wife).
SELECT Cities.City, ZipCodes.ZipCode
FROM ZipCodes INNER JOIN (Cities INNER JOIN Cities_ZipCodes ON Cities.CityID = Cities_ZipCodes.CityID) ON ZipCodes.ZipCodeID = Cities_ZipCodes.ZipCodeID
ORDER BY Cities.City, ZipCodes.ZipCode;

Now run the query and you will get the same table we had in Figure 3. So we got reporting down. Now how do we swing data entry.

We'll start with a table hack. In MS Access you can bastardize a table to act like a mini-form. This is a good place to start.

Open Cities_ZipCodes and switch to design view. We're going to add lookups for those two foreign keys, CityID and ZipCodeID so they are human readable.

Select CityID and in the lower panel click the Lookup tab. Add the following settings:
PropertyValueWhat is going on here?
Display ControlComboboxThis will display a combobox pick list instead of a textbox
Row Source TypeTable/QueryThis is the data source type we are using to fill the pick list
Row SourceCitiesUse the cities table to fill the list
Bound Column1We saying that the field CityID matches the first field in the Cities table
Column Count2Pass across the first two fields: CityID and City
Column HeadsNoDon't display column headings, only really useful if you are bringing back a slew of fields such as City, County and State
Column Widths0";1"By settings CityID to 0" I am hiding it and displaying City 1" wide, but since it is the only one it will stretch to however wide the column ends up being. Note this is in inches, semi-colon delimited.

Repeat these settings for ZipCodeID, Save and open the table.

Surprise! We have a human-readable spreadsheet... but every value is validated for referential integrity!

Now before we move to forms we have one more table perversion that MS Access allows: Subdatasheets. This method, combined with the look-ups we just authored will let us manage many-to-many relationships as if they were convenient hierarchies in table view.

Open the Cities table in datasheet (table) view. If you're using MS Access 2007 you'll see some + symbols auto-magically added themselves to your datasheet. Click one and the related records in Cities_ZipCodes will be displayed.

Now, for various reasons, for example you added a second relationship to cities (maybe a states table) this won't appear magically. No worries. Follow these steps:
Select the Home tab, browse across the Records Fieldset and you'll see a small More button. Click this and select SubDataSheet. Find the table you wish to relate, if you have the Cities table open select Cities_ZipCodes and identify the parent and child fields that match, in this case CityID.

This turned into a long post. In the next installment I'll add some forms to manage our postal codes.

accdb iconDownload the MS Access 2007 Project used in this post.


Popular posts from this blog

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under to
Pick the virtual directory you want to redirect. e.g. Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g.$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

Maintaining Cross-Database Referential Integrity

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS SELECT SexID, Sex, Rank FROM Master.dbo.PIC_Sex ORDER BY Rank
Yes, that's right, Male and Female! Slugs are awesome.

Step 2:
We create a new user-defined function xdf_Val…

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.

Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/

The MIME type for KMZ files is

* application/

Source: Google KML Tutorial

To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…