AB1: Multivalued Columns
Your ability to add and delete options in table AB1 is quite limited. The nature of this table requires at least as many option columns as necessary to hold the item with the most options—and preferably anticipates that you'll need to add some options in the future. For example, if the item with the most options has six, the table might have eight option columns.
This table design also introduces many nulls into the table. Say your leisure suit hasn't been selling well in the small size, so you decide to discontinue that option. To remove it, you can code
UPDATE AB1
SET ItemOption1 = Null WHERE ItemOption1 =
"Small" AND ItemId = 100
The problem with this update is that it assumes you know that Small is entered in ItemOption1 and not ItemOption2 or ItemOption3. But what if you had 10 or 20 option columns and you didn't know which one contained the entry Small? Then, the SQL code becomes very repetitive:
UPDATE AB1
SET ItemOption11 = NULL WHERE ItemOption1
="Small" AND ItemId = 100
UPDATE AB1
SET ItemOption2 = NULL WHERE ItemOption2
="Small" AND ItemId = 100
UPDATE AB1
SET ItemOption3 = NULL WHERE ItemOption3
="Small" AND ItemId = 100
Note that your application must perform a separate update for each column the option could possibly exist in. So if 20 option columns existed, the application would need to issue 20 separate UPDATE statements to delete the option Small.
Adding a new option to an item also poses a problem. If all the columns already have entries and you need another option, your only choice is to add a column to the table. Even if you have available columns, you have to decide which column you should add the new option to. In the preceding example, let's assume that you need to add the size Extra Large. As you do when you delete an option, you need a separate update for each column. However, to ensure that you don't replace all null columns with Extra Large, you need a way to know the first time an update succeeds. For this purpose, you can use the @@rowcount system function, which returns the number of rows affected by each UPDATE statement.
The T-SQL block in Listing 2 attempts to insert 'Extra Large' into ItemOption1 and uses @@rowcount to determine whether the update was successful. If not (i.e., if @@rowcount = 0), the code moves on to ItemOption2, and so on. Querying ItemID 100 after running this code shows that column ItemOption1 contains the new value 'Extra Large'.
So, how has the SQL for populating the drop-down list changed? With the following SELECT statement, you can no longer loop though the ItemOption column from your normalized tables to populate the select box:
SELECT ItemOption1, ItemOption2, ItemOption3 FROM AB1 WHERE ItemId = 100
The ASP code must now contain ItemOptions 1, 2, and 3 in separate <option> tags. Also, you now get a blank line if one of those columns is null, so you need to make sure that the column value is not null before inserting it into the drop-down list. As Listing 3, page 44, shows, this process involves much more work than before. However, you can decrease the amount of work necessary by creating a stored procedure that inserts the option values into a temporary table. The procedure essentially turns the columns into rows, letting you again loop through the ItemOption column in the temporary table. The procedure in Listing 4 creates a temporary table to store the values from AB1's ItemOption1, 2, 3, ...n columns in one column called ItemOption. The procedure then selects only the non-null values from the temporary table into the resultset. Because you're now dealing with rows instead of columns, you can again loop through the resultset and populate the drop-down list, as Listing 5 shows.
Can you search for all items that offer a particular option? Yes, but this process also involves more work than it needs to. Let's say one of your customers is a cheerful person who wants to find all items that come in yellow. You can easily set up a search form, but what does the SQL look like? In the first example with the normalized tables, this search is a fairly simple join:
SELECT I.ItemName FROM Items AS I Inner Join
Options AS O
On I.ItemId = O.ItemId AND O.ItemOption =
"Yellow"
But for table AB1, the SQL mutates into
SELECT ItemName FROM AB1
WHERE
ItemOption1 = "Yellow" OR
ItemOption2 = "Yellow" OR
ItemOption3 = "Yellow"
and so on, up to the highest ItemOptionn column.
As you can see, dealing with AB1 produces some long, repetitive SQL that takes much more time to execute than the joins and simple INSERT and DELETE operations from the normalized example. Also, table AB1 has only three option columns. If it had 10 or 20, for example, the above data manipulation language (DML) operations would grow even larger.
Prev. page
1
[2]
3
4
next page 
