How can I correctly submit and write checkboxlist in db? (Theory / Logical Reference)

Question

Please note that the database project that I have is completely in sandbox mode. Nothing is completed. Everything (again in sandbox mode) is in the same table. Also, I'm currently looking for a coding job. I am looking for the correct theoretical / logical approach to this puzzle so that I can go and play with the encoding myself. I studied so well. If I need help with coding, I will come back here for further help.

I am creating the first of several CheckBoxList to submit a manually created form. I was looking for several ways to not only create the specified CheckBoxList, but also enter it into the database. However, the problem I am facing (mainly because I have not come across this script before, and I would like to know about it) is that I not only need to worry about the correct entry of the elements in the database, but I will eventually need to issue a report or make a printed form out of these records.

Let's say I have a grill order form for a grill of a certain type, and I will need to send this form to distribution centers across the country. Distribution centers will need to pull out the barbecue if they are highlighted in uniform.

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

  All
 Dallas
 Miami
 Los angeles
 Seattle
 New york
 Chicago
 Phoenix
 Montreal

If a specific city (or all cities) is selected, then the distribution center will pull the barbecue grill to send.

The added part is what I want:

• be able to create a grid from this reporting database to indicate which distribution center has received barbecue orders and

• You can create reports to let you know which distribution center has sent barbecue orders for a specific month (among other reports).

Here is what I'm playing with right now.

On my aspx page, I have a checkboxlist programmed with all the distribution centers entered as listitem, as well as the option for "ALL" (distribution centers).

I also created a dedicated column in this table that contains all the information in the list and programmed sqldataconnection to this table in order to play with the programmability of using the database for this purpose.

When it comes to writing selected ones to the database, I initially created a column for each destination city, including the "All" option. I was going to merge, just putting the selection in one column, but with some information that I read today about standardizing the database, the first options seem to be better than the last. Is this good practice for such situations, especially if I need to think about reporting? Am I putting CheckBoxList data in one cell in a specific column? Create separate columns for each distribution center? Am I even on the right track here?

Answer 1

Depending on the number of cities you want to save, I used bitwise operators to store small amounts of data. In fact, it will save it in a table as follows:

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

and continue driving in base 2 for additional cities.

When your user selects several cities to order, the value that is inserted into the city database is a bitwise calculation. Therefore, if they chose Dallas, New York and Chicago, you will do the following:

 2 OR 8 OR 16 

Which would be equal to 26

Now you can use bitwise AND on the resulting value. Therefore, if you are checking Miami, do the following assessment:

 26 AND 4 = 0 

which indicates that Miami was not selected. Any value that was selected in the evaluation, it will return its identifier as follows:

 26 AND 8 = 8 

Again, I used this only for small subsets of data and for the most compact data storage. Computationally, this can be a trifle more expensive than some other methods, but I'm not 100% sure.

Answer 2

Note. These may not be the best approaches, but I saw how they were used.

1) The presence of one column column with comma-delimited rows. This should work if the parameters do not have identifiers in the database (with a separate reference table).

• You will need to go through the list of checkboxes, get the selected options and combine them using String.Join()
• You need to split the string after getting it from db and use it to check the checkboxes if the text is found in the resulting array
• Problem: you may need the split function in the database, which converts a comma-separated string to strings. There is implemented the implementation of the split function in web / stackoverflow

2) You may have a separate table for locations, for example. xxxxtable_location, where the FK link to the main table. This will be a table with multiple columns.

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