I am creating a web form that includes a checkbox list control (not yet in Mere Mortals). This is so the user can select multiple items. My question is, What is the best way to capture and insert that into the database (SQL Server 2000)? The options I know about are:

1. Create a related intersection table that creates a row for each person/item combination. Iterate through the list and insert a row for each item.

2. Create a large field in the table to store all the selected values that gets populated by a string buffer collection of all items, perhaps separated by some character other than a comma.

3. Create a field in the table that is binary --inserts a 1 if a particular checkbox list is selected, 0 if not. It keeps adding 1 or 0 for each checkbox item-- eg. 1001. I got this idea from another database developer, but I have no idea how to do it.

4. Create a field in the table for every value that could be selected. Iterate through the list and insert as appropriate--or go back to single checkboxes.

5. Something else??

I am leaning towards #2, but I am not sure it is going to serve me well when I need to query the data for particular values in that one large field.

Does someone have a recommended approach?

Sandra