Business and Database Logic, Part 2

In a previous post on the subject, I touched on some thoughts I had based my application needs to keep data in a database over a period of time, without using updates and deletes.

After doing some pencil and paper design, and a lot of thought, I think I figured out the easiest way to implement this.

From a database standpoint, what we really have is a simple one-to-many relationship. For example:

Let’s say we define an employees table. I want to put as little information in here as possible, and yet I want the most amount of static information available in this table as well. For the purpose of this example, let’s just assume that the only thing static about an employee is their name.

+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 1. | Tennis....| Caleb......|
+----+-----------+------------+

Now, let’s create normalized tables that reference this one with one-to-many relationships. For example, how about employee_addresses:

+----+-------------+------------+-------------+
| id | employee_id | created_on | city....... |
+----+-------------+------------+-------------+
| 1. | 1.......... | 1997.02.01 | Terre Haute |
| 2. | 1.......... | 2001.02.20 | Columbus... |
+----+-------------+------------+-------------+

So we have this database relationship that says employee number 1 has multiple entries in the address table.

However, from an application standpoint, most of the time we are only ever concerned about the most recent entry. That is, if I want to know an employee’s address, what I really want is the most recent entry in the table for that employee. Thus, we have a one-to-most-recent relationship that needs to be modeled via the application.

In Rails, if you use the has_many idiom, then you can simply do a first or last on the array it returns, as long as you’ve set up your query to order via created_on. Perhaps, though, it would be worthwhile (at least for myself) to create a has_many_needs_most_recent relationship to simplify the rest of my code.

I like this, because outside of the accessing class nobody knows the implementation details of the database and how it stores all of the historical data. However, it’s important to keep that data there from a business standpoint. My small employee example probably is not the best to drive the point home, however some of our more important tables which include device calibrations and accuracy measurements occasionally get scrutinized by customers, the ISO compliance folks, and government auditors (since we have government contracts). Keeping a “paper” trail, so to speak, on our data is very helpful when questions start to form.