To read the article online, visit http://www.4GuysFromRolla.com/articles/082008-1.aspx

Creating a Dynamic Data-Driven User Interface (Part 1)

By Scott Mitchell


Introduction


Most data-driven web applications have a fixed data model and user interface. What I mean by "fixed" it that the data it needs to be captured is known in advance. Consequently, the database's tables are created before a single line of code is written and the application's user interfaces are dictated by this pre-determined data model. While most applications work with a fixed data model there are scenarios where the parts of the data model need to be defined by the end user. Such applications are more difficult to create because both the data model and user interface need to be flexible enough to allow the user to specify the information to be captured.

Imagine that you were creating a web application to be used by small law firms for managing their clientele. You would need a database table to capture information about each client. This table would have columns for each attribute of a client, such as: FirstName, LastName, Email, Address1, Address2, City, and so on. Regardless of what attributes you define for this table you can be certain that there will be a law firm that needs to store additional information not already captured. To allow for this level of flexibility you could enable each law firm to define additional client-related attributes specific to their law firm.

This article is the first in a four-part series of articles examine how to build a data model and user interface to allow for such dynamic, data-driven websites. In this installment we look at how to capture such dynamic, end user-defined information in the database. Read on to learn more!

An Overview of a Dynamic, Data-Driven User Interface


Before we get started building the data model, let's take a moment to discuss the ins and outs of a dynamic, data-driven user interface. Such user interfaces are most commonly used in web applications where a single application serves as the website for multiple customers. In the Introduction I gave an example of a web application used by multiple law firms. The idea with a dynamic, data-driven user interface is that in addition to text data fields with various clients might need to define custom attributes. Such a model requires the following components:
  • A data model that allows each customer to define their own data attributes along with the ability to record values for these attributes. From the law firm example, customers would need to be able to define custom attributes for their clients. Correspondingly, the data model would need to be able to record what custom attributes each law firm wants to record as well as the values for these custom attributes each law firm's clientele.
  • A web page that allows the customer to define their custom attributes. This page would prompt the user to define the name and data type of each custom attribute. It could also provide more advanced features, such as specifying whether any attributes were required or other validation rules.
  • A web page that implements the dynamic, data-driven user interface. This web page, when visited by a user, loads the appropriate Web controls to collect the custom attributes that apply to the customer who is currently logged on. Additionally, this page needs to populate the dynamically loaded Web controls with the current values in the database and provide the user the ability to edit these custom attributes and save their changes back to the database.
This article examines how to create the data model to capture dynamic, user specified attributes. Part 2 looks at building the web page where customers define their custom attributes, and Part 3 explores the most complex piece of the puzzle - dynamically creating the user interface based on the currently logged on customer. The fourth and final installment will explore more advanced concepts related to this topic.

Over the course of this article and the next two we will implement a simplified version of the example cited in the Introduction - a web application for a law firm that enables each firm to define custom attributes to capture for their clientele.

Creating the Fixed Part of the Data Model: The Membership Schema and Related Entities and Customers and Clients Tables


Most applications that have a dynamic, data-driven component to them also have a fixed data model. In fact, typically the vast majority of the data model is fixed while only a small part is dynamic. For this example there are three fixed data model entities:
  • The schema for the SqlMembershipProvider implementation and related entities. This website supports user accounts and requires visitors to log in. When a user logs in we need to determine what customer they are associated with so that we can display the user interface with the appropriate dynamic attributes. We also need an additional table that indicates what customer each user account belongs to.
  • The Customers table. This table has a row for each customer, where customers are the various law firms that have purchased accounts to use our software.
  • The Clients table. Each law firm will maintain its list of clientele as records in this table. This table holds the fixed attributes for customers that are common to all law firm customers, attributes like: FirstName, LastName, Email, Address1, Address2, City, and so on. The dynamic client attributes that are specific to a particular customer will be stored in another table.
First things first - we needed to implement ASP.NET's SqlMembershipProvider and configure the site to use forms-based authentication. A thorough description of this process is beyond the scope of this article. For detailed step-by-step instructions, refer to the following resources: my Forms-Based Authentication, Membership, and Roles tutorials, which are available in both VB and C# and includes several screenshots and detailed instructions; and my Examining ASP.NET 2.0's Membership, Roles, and Profile article series. Implementing the SqlMembershipProvider adds a number of database tables, views, and stored procedures. The aspnet_Users table contains a record for each user account, and each record is uniquely identified via the UserId field, which is of type uniqueidentifier. In addition to the SqlMembershipProvider-related tables, we need to add another table that indicates what customer account each user account belongs to. We'll return to this once we have created the Customers table.

The Customers table is used to model the various law firms that used our website. This table contains a record for each law firm (our customers). Create this table so that it has the following simple schema:

Column NameData TypeNotes
CustomerIduniqueidentifierPrimary key; default value of NEWID()
FirmNamenvarchar(50)

Note that the CustomerId field is of type uniqueidentifier and has a default value of NEWID(). The uniqueidentifier is SQL parlance for a Globally Unique Identifier (GUID); the NEWID() default automatically assigns a GUID value to this column when inserting a new record. If you would rather make this column of type int and market as an IDENTITY column, feel free to do so. For more information on using uniqueidentifier primary keys versus using integer-based primary keys, see Techniques for Uniquely Identifying Database Records.

Now that we have the Customers table implemented we can go back and add a table that associates a user account with a customer. Create a new table named ExtendedUserInfo with the following schema:

Column NameData TypeNotes
UserIduniqueidentifierPrimary key; foreign key to aspnet_Users.UserId
CustomerIduniqueidentifierForeign key to Customers.CustomerId

This table establishes a one-to-one correspondence with the aspnet_Users table (which contains a record for each user account in the system). Currently this table contains only one column - CustomerId - but you could include more columns here to capture additional user information.

the final table we need to create to model the fixed portion of our database design is the Clients table. Recall that this table has one record for each client in each law firm, and that this table holds the fixed client attributes that are common among all law firms. Create this table with the following schema:

Column NameData TypeNotes
ClientIduniqueidentifierPrimary key; default value of NEWID()
CustomerIduniqueidentifierForeign key to Customers.CustomerId
FirstNamenvarchar(50) 
LastNamenvarchar(50) 
Emailnvarchar(100) 

The following ER diagram shows the three tables we created by hand along with the aspnet_Users table and the relationships among these tables.

An ER diagram of the fixed portion of the data model.

The download available at the end of this article includes a database with these tables, along with some test records in each table.

Modeling the Dynamic, Customer-Specific Client Attributes


Each customer is allowed to define custom dynamic attributes for their clientele. When defining such attributes the customer must supply the name of the attribute and its data type. A dynamically defined attribute can have one of the following data types:
  • String
  • Boolean
  • Numeric
  • Date
This list of data types is missing a key option - a lookup table type that permits the user to select from a list of allowable options (i.e., picking a single option from a drop-down list or, perhaps, multiple options from a list box). We will expand this list to include more interesting data types in Part 4.

We need a database table to model these available data type options. Create a new table named DynamicAttributeDataTypes with the following schema:

Column NameData TypeNotes
DataTypeIdintPrimary key
DataTypeNamenvarchar(50) 

Note that the primary key column, DataTypeId, is of type int but is not an IDENTITY column. This is because we want control over the DataTypeId values associated with each data type.

Next, add the following records to this table:

DataTypeIdDataTypeName
1String
2Boolean
3Numeric
4Date

In addition to specifying a name and data type we may also want to let the user provide validation information, such as whether the field is required, or if a numeric value must be between a certain range of values. We'll explore some of these options in Parts 2 and 4.

We now need a table that has a record for each customer's dynamic attributes. Create a table named DynamicAttributesForClients with the following schema:

Column NameData TypeNotes
DynamicAttributeIduniqueidentifierPrimary key; default value of NEWID()
CustomerIduniqueidentifierForeign key to Customers.CustomerId
DataTypeIdintForeign key to DynamicAttributeDataTypes.DataTypeId
AttributeNamenvarchar(50)The name of the dynamic attribute
SortOrderintUsed to specify the order with which the attributes are stored when displayed in the UI

We now have a table (DynamicAttributesForClients) that stores each customer's set of dynamic attributes for their clientele. All that remains is to create a table that stores these dynamic values for a particular client. Create a table named DynamicValuesForClients with the following schema:

Column NameData TypeNotes
DynamicValueIduniqueidentifierPrimary key; default value of NEWID()
ClientIduniqueidentifierForeign key to Clients.ClientId
AttributeIduniqueidentifierForeign key to DynamicAttributesForClients.DynamicAttributeId
DynamicValuesql_variantThe value for the specified dynamic attribute for the specified client

Note that the DynamicValue column is of type sql_variant. The sql_variant type allows the column to hold any data type other than text, ntext, nvarchar(max), and other similar BLOB data types. For more on this data type be sure to read The sql_variant Data Type.

The following ER diagram shows the tables involved in storing the dynamic attributes and their values.

An ER diagram of the dynamic portion of the data model.

When creating the foreign key constraint between the DynamicAttributesForClients.DynamicAttributeId and DynamicValuesForClients.AttributeId columns you need to determine whether or not to cascade deletes. Ask yourself this - what do you want to happen when a customer (a user from a law firm) deletes one of the custom attributes they created? Do you automatically want to delete the associated client values? If so, configure the foreign key constraint so that it cascades deletes. If the values associated with the custom attribute may need to be saved then you need to allow a customer to "discontinue" a custom client attribute. Discontinuing an attribute would leave it (and its associated values) in the database, but would hide the attribute from the dynamic, data-driven user interface.

Similarly, when a client is deleted from the Clients table you want the corresponding custom attributes values in DynamicValuesForClients to be deleted as well. While not shown in the above diagram, there is a foreign key constraint between Clients.ClientId and DynamicValuesForClients.ClientId. This constraint cascades deletes so that if a client is deleted its custom attribute values are automatically deleted as well.

An Enhancement to the Data Model
There will certainly be times when a customer no longer needs to capture a particular custom client attribute. When such a scneario arises you could let the user delete the custom attribute outright. Assuming your foreign key constraint is set up to cascade deletes then deleting an attribute would automatically delete its corresponding values. But what if the customer wants to retain those values? In that case, they need to be able to mark the attribute as discontinued, which would leave it in the database but hide it in the dynamic, data-driven user interface.

To allow for this level of flexibility add a Discontinued column to the DynamicAttributesForClients table. The current data model shown in the diagram above (and available for download at the end of this article) does not include a Discontinued column. Therefore, to no longer use a custom attribute a client must delete it and its associated values.

Looking Forward...


At this point we have simply define the data model for our dynamic, data-driven user interface demo/example. If the data model does not yet make complete sense, don't worry. It will become more clear as we work through the subsequent installments of this article series. In Part 2 we will look at building the user interface where a customer defines the dynamic attributes for their clients.

Until then... Happy Programming!

  • By Scott Mitchell


    Attachments


  • Download the code used in this article

    Further Reading


  • Forms-Based Authentication, Membership, and Roles Tutorials (includes VB & C# versions!)
  • Examining ASP.NET 2.0's Membership, Roles, and Profile
  • Techniques for Uniquely Identifying Database Records
  • The sql_variant Data Type
  • Article Information
    Article Title: ASP.NET.Creating a Dynamic, Data-Driven User Interface (Part 1)
    Article Author: Scott Mitchell
    Published Date: August 20, 2008
    Article URL: http://www.4GuysFromRolla.com/articles/082008-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers