I'm sure that anyone who has been developing applications for more than a few months has run into the following situation. The users want to have some sort of selection list (most likely represented by a DropDownList or ListBox control) and they also want to maintain that list as part of the application requirements. Having seen this so many times, it must be a pattern. I am attempting to define it here using the same format as the Enterprise Solution Patterns Using Microsoft .NET document.
User-Defined Item List Maintenance Pattern
Problem
How do you structure the representation and functionality for a user-defined list of items and give them the ability to maintain it and have it displayed in the order they choose for selection lists?
Forces
- Since the users are requesting the ability to maintain the item list, it is volatile. To minimize application maintenance, dependencies on the volatile items must be minimized.
- The users are likely to want to sort the item list in a manner that is not readily apparent. Giving the user the ability to control the sort order frees the developer from having to do it and makes the system more robust to the users.
Solution
The User-Defined Item List Maintenance pattern separates the functionality into three attributes and a relationship:
- ItemID - this is a unique value field with no business meaning. AutoNumber or Identity fields are excellent types for this attribute. This attribute should never be shown to the user. Otherwise users will start using it and giving it business meaning, thereby nullifying the advantage of the pattern.
- ItemName - this is a unique text field that contains the value that is actually displayed to the user. The attribute must be unique, otherwise it will not be readily apparent to the user what differentiates two separate items with the same ItemName. The length of this attribute can vary according to user needs, but should remain relatively small since it must be displayed in its entirety in the ItemList.
- OrderBy - this is a unique numeric field that contains the value by which the ItemList is sorted. It is numeric since all users understand conceptually how numbering works; not all users understand the ordering of text (i.e., the text values 1,10,100,11 are properly ordered ascending yet make no sense to the user). This attribute is unique so that users do not try to set the OrderBy value to the same number for several ItemList items.
- Relationship - the ItemList is able to be related to any number of Domain Entities (i.e., other tables) through the ItemID without change to the ItemList.
Example
The following SQL script will create an ItemList table in SQL Server 2000.
if exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[ItemList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ItemList]
GO
CREATE TABLE [dbo].[ItemList] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OrderBy] [int] NOT NULL
) ON [PRIMARY]
GO
Benefits
- The developer is freed from having to work on minor details and instead leave it to the user.
- The user can always setup the list with the correct sort order, regardless of changing business conditions, text of the list items, etc.
Liabilities
- If not careful, the user can add far too many items to the list, resulting in degraded performance and an inability to view all list items correctly.
This pattern only works for simple Item Lists. Advanced or detailed Item Lists will require special consideration.