| 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 1. | 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.
| 2. | 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 SettingsLogical Column Name | Purpose |
|---|
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.
| 3. | 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.
Important  | 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.
|
|
