Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Driven Architecture

By Jim Craddock,

Traditional development methods typically center on developers writing customized forms, pages or screens for each aspect of user data. Object-oriented development allows today’s developers to reuse code and dramatically lower the overall number of lines of code required for a typical complex application. Data-driven development is a way to further reduce the amount of custom coding required in development by investing time upfront in an architecture that utilizes metadata (data about data).

What exactly is a data-driven application? In short, a data-driven application is one in which metadata is used to construct the interface utilized by the user to interact with data. By using metadata, it is possible to reduce the number of forms used, for example, to search and list data in ten different tables or views from ten forms down to one single form. This is made possible by coding the form to draw itself at runtime based on the user’s interaction.

An example might be the region table in the Northwind database, which has two columns – RegionID and RegionDescription. If a custom built a page is constructed to list this data, somewhere in the code for the page would be a basic select statement such as “Select RegionID, RegionDescription from Region.” however, a data-driven approach would not have that statement coded by a developer anywhere in the code. Instead, metadata would be queried to find out which columns are in the Region table, and then a SQL statement would be dynamically created to select those columns from the table.

In order to draw a page for this simplest of examples you would need to know these elements:

  • Physical Column Name (in order to build the query)
  • Logical Column Name (in order to provide a meaningful display to the user)
  • Column Sequence

Figure 1. Region Table Column Metadata

Physical Name

Logical Name

Sequence

RegionID

ID

1

RegionDescription

Description

2

Similarly, an edit page is also possible. In order to create an insert or update statement, one additional piece of metadata is needed – datatype. A datadriven editpage would have two generic, dynamically-added single line edits on the page. Then when the user submitted the data, the metadata would be queried and the appropriate insert or update statement would be created and executed.

The previous technique will create a query to list or edit a basic record with no relationships. It becomes more complicated when foreign keys are introduced into the table. Figure 2 shows the first four columns of the Northwind Products table.

Figure 2.

Column Name

Datatype

ProductID

Identity

ProductName

Nvarchar(40)

SupplierID

Int

CategoryID

Int

In the products table, the columns SupplierID and CategoryID are foreign keys. As such, instead of containing data the user would be familiar with, they contain pointers to the data stored in other tables. Obviously, we cannot just display the data contained in this table to the user, as they will have no idea what supplier or Category is represented when shown just the ID. To handle this problem, we assign add two new attributes in the metadata to the columns. Along with physical name, and logical name, etc, we add a display type for all columns and a query for the cases where the column is a foreign key.

So, the metadata stored for the Products table columns looks like this:

Physical Name

Logical Name

Sequence

DataType

DisplayType

Query

ProductID

ID

1

Identity

Textbox


ProductName

Name

2

nvarchar

Textbox


SupplierID

Supplier

3

Int

Dropdown

Suppliers

CategoryID

Category

4

Int

Dropdown

Categories

The Queries are then stored like this:

Name

Query

ID Column

Value Column

Suppliers

Select ID, CompanyName from NorthWind.dbo.Suppliers

ID

CompanyName

Categories

Select ID, CategoryName from Northwind.dbo.Categories

ID

CategoryName

Now, we can build the select statement as:

Select ProductID, ProductName, A.CompanyName, B.CategoryName
 from Products 
   left join (Select ID, CompanyName from Northwind.dbo.Suppliers) A 
	   on Products.SupplierID = A.ID
   left join (Select ID, CategoryName from Northwind.dbo.Categories) B 
	   on Products.CategoryID = B.ID

This can easily be extended to include the rest of the table. Similarly, the edit page can be extended to include dropdowns for foreign keys.

Do not assume that data-driven architectures mean you have to give up key components of other approaches. Simple and complex data validation with and without override, filtering, dynamic defaults, role-based permissions, email notifications, custom reporting and other options are all possible if time is spent creating the appropriate design.

A sample application using Northwind is available for download.

Note: This article is a high-level summary of an ASP.NET system in production supporting over 12 different systems with over 300 total users .

Total article views: 6903 | Views in the last 30 days: 1
 
Related Articles
FORUM

Not Selecting All Columns in select query

Not Selecting All Columns in select query

FORUM

select query

select query

BLOG

Query Table Metadata

There will inevitably come a day when you want to take a look at the metadata of the tables inside.....

SCRIPT

Generate Column Metadata

This utility will generate column and parameter metadata in various useful formats for tables, views...

FORUM

external metadata column

needs to be updated in the external metadata column collection

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones