A few years ago, I was presented with an interesting challenge from a prospective customer - design a
database application without knowing the eventual database structure.
Weird, huh?
I guess a little background will explain it a little better.
My client needed a system designed to house, manage and produce massive information for their customers
who would be accessing the data through a proprietary client application, which would continue to change
over time. This application's interface with the back-end system is through a structured query language
file, used to load its own proprietary, client database.
With an expected client load of tens of thousands in the near-term, growing to several hundred thousand
within five years, each requiring possible daily updates and modifications, we had an interesting design
challenge to meet.
"How will we do this?" was the first question.
We could have designed a classic database, defined all the attributes as we knew them at the time and then
built the application based on this design. The issue here was that as the client application changed over
time, we would likely need to change the database application to stay in sync. Although it would be a great
way of keeping our development team busy for the next five years, our customer would never have bitten.
We needed a system which allowed our customer to manage what gets stored in the database and how our
application would react to the data going in and coming out of the system - without calling us up to
make minor changes to the application every time.
Not a simple task.
So we decided to design a rules-based database engine. Although a rules-based system is more complex
(read expensive) than a nicely boxed-in system, it gives the users the control they need when business
rules change over time, thus eventually costing your customer less in the long-term.
Rules-Based Database Engine
The definition of a rules-based database engine is a system which not only has data stored in the
database, but also meta-data which defines "data-about-the-data." This allows you to
define extra information which is then interpreted by the application - how do the different
pieces of information bits interact with one another? Sort of like Properties and Methods
all stored within the database.
In a non-normalized database, the data model and the data would look like Figure 1.
Figure 1 - Non-normalized Data Model
Here all of the data associated with an item is stored on the same 'master record.' There are
significant advantages to using this system - mainly data access speed. With an implementation
like this, you can guarantee you have all of the data for an item in one database call, without
costly indices or other overhead.
The downside, however, is that any changes to the attribution of the record will require a database
administrator (DBA) to modify the data structure of the table record - and this typically results
in a change to the application code which refers to this data.
Shown in Figure 2 is the basic data model for a normalized database, using the meta-data concept.
Here, the 'master record' is the main item you are referring to.
Figure 2 - Normalized Data Model
For example, say our database will hold information about chairs. Here, the master record will
refer to a particular chair. It receives an identifier and possibly a name. We can also track
things like create date and creator on this master record.
All other items are then stored as attributes - color, type, material, location and other attributes
can be stored about the chair itself.
If we know everything we'll ever know about these chairs when we start, maybe a non-normalized
approach makes sense. Our data table might look like the one shown in Figure 3- Non-normalized
table data. From a developer's point of view, this is simple to access and manipulate. We also
have the advantage of speed - we make one call to the database and we have all the data we need
about our chair.
Figure 3 - Non-normalized Table Data
Now, let us go back to our original design issue: we don't know what the attributes of a chair will be in 5
years. How do we design a table like this?
In Figure 2 - Normalized Data Model we see how we can implement this, through dynamic attributes. Now we can
store the references to the chair itself and its attributes separately. If we get a new attribute we need to
track, we can simply define the meta-data for that attribute and begin saving it in the attribute table.
In the above example, we can see how the items relate to one-another through their ID's.
The tblChair table has been simplified to only hold an ID for this example, but that ID is then referenced on
the tblChair_attribute table in the 'CHAIR_ID' column. This tells us which chair we're dealing with
and the 'ATTRIBUTE_TYPE_ID' column then tells us which item from the tblAttribute_metadata table we're
describing.
We can even extend the meta-data table to include things like defaults and other attribute-specific-attributes.
In the next article, we'll discuss the various items which are used to drive the application into making
intelligent choices when handling the data.
Accessing the Data
Now that we have designed the underlying data model - you are likely thinking... hmmm - nice, but it must be
a pain to access and maintain data spread out like this.
Well, luckily modern database engines make our lives easier with several different access methods. Using
pre-defined queries and procedures, we can make life easy for the developer when they want to view, edit
or delete items in the normalized data model.
In the non-normalized world, we could simply call a simple SQL select statement to access our data and
we'd have it in all would be good. With the normalized data, it makes more sense to write an interface
layer above the data and use that as the access method from your application itself.
The most likely method would be through stored procedures written within the database. Standardizing my
naming conventions makes it easy for the developers to understand what interfaces they need when working
with the data in the model. The naming convention becomes the name of the master table item (ie chair)
with the action for the main items or the name of the master table item with _attribute followed by the action.
In a future article, we'll dig into the details of what these various procedures do and how they can be
extended with complex meta-data to do verification and validation.
What these procedures provide the developer, however, is an easy, seamless method of accessing the data in
the database. Should there be a change in the data model or the attribution of the master items (like a
new unforeseen chair attribute in our example above) the developer's code can handle the changes without
new user interface or business rules being written.
The key here is to design your application to read the database to determine how each item is defined. You
define the generic item - like our chair above - and then you query the database to see how a chair is
defined. Then you'll need to render the information in your application so that it can be viewed and
edited. This also means that we can keep information on the display and constraints in the data model
so that we can display the items properly and ensure that they conform to whatever requirements the customer
may desire.
In the next couple articles, we'll talk about how to design this using T-SQL and ASP.NET, so that you can
accommodate these dynamic changes.
Maintaining the System
Now, you say, this all seems nice and easy, but how do we continue to extend the system after the developers
have disappeared and are enjoying drinks on the beach? Well, with an interface to the meta-data table, your
customer can add, remove and possibly hide various attributes.
By allowing your end-user 'administrative' access to this set of tables, they can add or remove items in the
data model, within the constraints of how you have implemented the application code.
We developed a web interface to these tables for our customer, which allows them to quickly add new attributes
to their master items - like chairs - and have them displayed and managed through the end-user interface. We
can even accommodate complex criteria and look-up tables without new application code needing to be written.
The end result is that, although a more complex and initially time-consuming endeavor than designing a static
data model, it eventually allows your customers the freedom to maintain their application without you, the
developer.
Is that a good thing? Hopefully your customer will understand and bring you back for that next job!
Where to Now?
In the next article, we'll investigate designing the data model and the specifics for handling constraints
in the database. I'll also cover how you can implement this using the tools contained within Visual Studio
2003 and SQL/Server 2000.
We'll then move on to implementing a type-classed architecture within Visual Studio and work through
developing the dynamic interface so that the application can display these unknown attributes.
We will also work on the administrative interface basics for you. Along the way, I'll discuss some of
the powers built into Visual Studio and how it can make your life easier.
I'll wrap things up with how you can extend this architecture beyond .NET. Using the power of the database
to its maximum has allowed us to develop ancillary applications which aren't .NET but we can still guarantee
data integrity since we designed the system to be application independent.
In this article, Joseph Poozhikunnel examines the importance of the three X's -- namely XML, XML Schema, and XSLT -- in a service oriented architecture (SOA). He then defines the design considerations that need to be adopted when designing a system based on SOA and examines the pitfalls that can arise if they're not followed.
[Read This Article][Top]
In this article, Joseph Poozhikunnel defines an Enterprise Service Bus (ESB) that can be created to support any Service Oriented Architecture (SOA) adopted by an organization. The type of ESB required could vary as there is no "one size fits all", therefore the article examines a few of the mechanisms available that could be adopted to implement an ESB. [Read This Article][Top]
In the seconmd part of his series on building an end user defined data model, Peter Scheffler gets into the actual meat of the model and discusses real-world implementation details and the actual table layouts. [Read This Article][Top]
The basic premise of a Service Oriented Architecture (SOA) system is to decouple applications from each other in order to make them autonomous. In this article, Joseph Poozhikunnel presents a simple SOA framework that can be used as a starting point for a system that addresses your specific business needs. [Read This Article][Top]
A well rounded versioning and deployment strategy considers several overlapping and interdependent .NET Framework concepts. In this article, Michele Leroux Bustamante will take you through a ten step program that reviews these core concepts, their relationship, and provides guidance for successful application deployments for the .NET Framework. [Read This Article][Top]
Adnan Masood continues his discussion of Microsoft SQL Server Analysis
services and Microsoft SQL Server Reporting services. In this part, he
discusses the steps that go into building more advanced reports. [Read This Article][Top]
Adnan Masood discusses Microsoft's comprehensive integrated business
intelligence, data mining, analysis and reporting solution: Microsoft SQL
Server Analysis services and Microsoft SQL Server Reporting services. [Read This Article][Top]
Chip Irek examines the architectural issues and component design issues of building a .NET application in a service-oriented architecture. [Read This Article][Top]
The importance of reuse can't be overstated, especially in light of the
degree to which we go out of our way to avoid it, but implementing a reuse strategy means creating high-quality low-cost applications that just might save your job. [Read This Article][Top]
One of the most critical components of any application is the help file
collection. The fourth article in Brian Korzeniowski's Enterprise Template series examines Dynamic Help in Visual Studio .NET and focuses on the logical process of creating help content.
[Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
