| Subject: ASP.net |
Author:
Zahid Shah
|
[
Next Thread |
Previous Thread |
Next Message |
Previous Message
]
Date Posted: 11:23:13 04/18/05 Mon
Author Host/IP: NoHost/202.123.250.10
Building an End User Defined Data Model - Part 1
Part One - Why Would You Want To?
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.
_get - ie chair_get
_update - ie chair_update
_insert - ie chair_insert
_delete - ie chair_delete
_attribute_get - ie chair_attribute_get
_attribute_update - ie chair_attribute_update
_attribute_insert - ie chair_attribute_insert
_attribute_delete - ie chair_attribute_delete
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.
[
Next Thread |
Previous Thread |
Next Message |
Previous Message
]
| |
Feedback: 