How can I properly present and write a checkboxlist to a db? (Theory/Logic help)

Question

Please note that the database design I have now is fully in sandbox mode. Nothing is finalized. Everything (again this is in sandbox mode) is in one single table. Also, I'm in now way looking for coding help. I'm looking for the right theoretical/logical approach to this puzzle so I can go in and play with the coding myself. I learn better that way. If I need coding help, I'll come back here for further assistance.

I'm in the process of creating the first of a few CheckBoxList for a manually created form submittal. I've been looking over multiple ways to not only create said CheckBoxList but to enter it into the database. However, the challenge I'm facing (mainly because I haven't encountered this scenario before and I'd like to learn this) is that I not only need to worry about entering the items correctly in the database but I will eventually need to produce a report or make a printable form from these entries.

Let's say I have an order form for a specific type of Barbeque grill and I will need to send this form out to distriution centers across the nation. The distribution centers will need to pull said barbecues if they are highlighted on the form.

Here's what the CheckBoxList for the distibution centers will look like:

All
Dallas
Miami
Los Angeles
Seattle
New York
Chicago
Phoenix
Montreal

If the specific city (or all the cities) are checked, then the distribution center will pull the barbecue grill for shipment.

The added part is that I want to:

1. be able to create a grid view from this database for reporting to note which distribution center got orders for barbecues and

2. be able to create reports to tell what distribution center sent out barbecue orders in a given month (among other reporting).

Here's what I'm playing around with right now.

In my aspx page I have a checkboxlist programmed with all the distribution centers entered as a listitem as well as an option for 'ALL' (of the distribution centers).

I also created a dedicated column in this table that holds all the information in the listitem and programmed a sqldataconnection to this table to play with the programmability of leveraging the database for this purpose.

When it comes to writing the selections to the database, I originally created a column for each destination city including the 'All' option. I was toying around with just putting the selections into one single column but with some of the information I've been reading today about Database Normalization, the former options seems to be a better one than the latter. Is this correct practice for situations such as this especially if I need to think about reporting? Do I put the CheckBoxList data in one cell in a specific column? Do I create seprate columns for each distribution center? Am I even on the right track here?

Gyniatrics

Answer 1

Depending on the amount of cities that you want to store, I've used bitwise operators to store small amounts of data. Effectively, it would store it in the table like this:

CityID   Location
2        Dallas
4        Miami
8        New York
16       Chicago
32       Montreal

and keep going in base 2 for additional cities.

When your user selects multiple cities for the order, the value that gets inserted into the database for cities is a bitwise OR calculation. So if they select Dallas, New York, and Chicago, you would be doing the following:

2 OR 8 OR 16

Which would equal 26

Now, you can use bitwise AND on the resulting value. So if checking for Miami the following is the evaluation:

26 AND 4 = 0

which indicates that Miami was not selected. Any value that was selected in the evaluation, it would return its ID like this:

26 AND 8 = 8

Again, I've only used this for small subsets of data, and to make the data storage as compact as possible. Computationally, it may be a trifle more expensive that some other methods, but I'm not 100% certain.

Wystand

Answer 2

Note: This might not be the best approaches but I have seen them used.

1) Having one column of comma-delimited string This should work well if the options don't have IDs in the database (having a separate referenced table)

• You will need to loop through the checkbox list, obtained the selected options and concatenate them with String.Join()
• You will need to split the string upon receiving it from the db and use it to check the checkboxes if there text is found in the resulting array
• Problem: You might need a split function in the DB that converts the comma-separated string into rows. There split function implementation on the web/stackoverflow

2) You can have a separate table for the locations e.g. xxxxtable_location where the FK to the main table is referenced. This will be a one-many table

ParentID,   Location
1           Dallas
2           Miami
2           New York
2           Chicago
3           Miami

Nada