Bliss and the Four Table Structure.

One of the most common design considerations I need to make in laying down a data structure is the flexibility that the end-user requires. Sometimes it's just the ability to modify what shows up in a drop down box- and then they get a lookup table. Sometimes it's the ability to create and apply properties to business objects- and then they get my standard tag structure (but that's another post). But sometimes, what the end-user really requires is the ability to create their own data structure.

Let's say your client wants to be able to attach information to their customer entity. But not just one piece of information- entire sets of it. If their customer deals with a specific process, they want to be able to record a great amount of detail about that process. And they want to be consistent about how they record it across many customers. A notes field is not going to lend itself to that structure. Let's further say that they might want to change what they record about this process tomorrow, and they don't want to have to wait for you to show up, modify a table and re-design a form to have a field for this new piece of data.

The data that goes into this structure just makes sense. You would want to capture this stuff anyway, and this format for capturing data works pretty well for allowing your end user to define what they want to capture without a need for your intervention.

I call it the four table structure. It defines what I like to think of as Virtual Tables. This is not ground breaking stuff. You've probably already made a few data structures that act this way without realizing it. What might be different about this is thinking of it as a virtual table. If you were to take the data from our example up there and envision it as an actual table using these mappings, the result would look something like this.

It's a table!  It's a freaking table! Made out of data!