Where to Store User Settings

Often an application needs to store data about the user's experience. In Web applications, this is typically accomplished using cookies (small text files that store user information), but with increased security on the Web, this can be troublesome. If you use some type of user identification, you can store user settings in a database. This will allow you to manage those settings without relying on client computers. There are a variety of user settings that can be stored. You can store settings as XML data, which gives you maximum flexibility. We'll discuss storing XML data later in this chapter. You can also use standard data methods of tracking user settings. When you use SQL Server to store the user's settings, the user is able to "take" those settings from client to client.

Implementing a User Settings Table


Determine the settings you want to store. While it is possible to store all of the user settings in your application's database, it is important to decide which settings are necessary to make your application function in a particular way and which settings must be available whenever and wherever the user logs on.


Once you have you determined which settings you will store, design the table necessary to store those settings. Table 1-2 gives an example of a table schema for maintaining user settings in your database.

Table 1-2. Sample Table Design to Store User Settings

Logical Column Name


User ID

Stores the unique user ID that will be used to identify and retrive the user settings. The data type of this column will depend on how you implement user IDs in your application.

Date Created

Records the date when the user was created. The first user-setting record is usually created when the user is created.

Date Updated or Date Refreshed

Helps you manage active users and verify how current the settings are. Refresh date is often more important than the created date.

Last Login

Tracks the last time a user logged in. In some cases, this duplicates the Date Refreshed column. Depending on your implementation of the refresh date, you may decide to eliminate this column and use only the Date Refreshed column.

User Setting column or columns

Tracks the settings that need to be stored for the user.

There are a couple of common implementations of the User Setting columns. The first is relational. In a relational implementation, each setting is stored in a separate column in the table. The problem with this solution is that in order to add additional settings, you need to expand the table by adding columns. The second option is to store the user settings in a single column. With the advent of XML, you can use an XML document to store the settings you need either in a TEXT column or in the new XML datatype column that we'll discuss later in this chapter.


Next, design the required data objects to retrieve and maintain the user settings table. You will need the following:

  • An Insert Stored procedure Inserts the initial user settings record. Often this is done when the user is created, so you might combine this with the procedure that creates the user.

  • An Update Stored procedure Updates the user settings record. This procedure will be called often in order to maintain the user settings in the application.


    When tracking highly volatile data in the user settings table, you will need to actively monitor the performance and tune your implementation as necessary. This will be especially true if you are updating user settings for events other than simply logging in and logging out of the application.

  • A Delete Stored procedure Deletes the user settings record. Like the insert operation, this can be a part of the user delete procedure. You might want to keep the two procedures separate, however, if you need to support a concept, such as resetting the user's settings to their default values.

Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net