Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(I tried posting this on SO but went unansered. I'll just post these theory type questions here from now on)

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).

What I'm looking for is a theoretical approach to designing this form while keeping in mind the gridview and reports. 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?

Note: At this point I'm not looking for coding help, I'm just looking for some theory/logic help so I can learn this the right way the first time.

::edit::

First off, 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.

Here's what I'm working 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?

share|improve this question
1  
Are you planning on deleting the question on Stack Overflow? Cross posting questions is frowned upon. –  bluefeet Jun 24 '13 at 16:39
    
Ooops. I'll take care of that. –  Techie Joe Jun 24 '13 at 16:48
    
What have you tried or thought about trying? Do you have existing table layouts and want to improve them, or are you wanting us to design your tables for you? –  Max Vernon Jun 24 '13 at 17:07
    
@maxvernon I just updated the post with more information. –  Techie Joe Jun 24 '13 at 19:03
add comment

closed as off topic by dezso, Max Vernon, Mark Storey-Smith, Jon Seigel, RolandoMySQLDBA Jun 24 '13 at 18:49

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 1 down vote accepted

At a high-level, I'd recommend a table storing all the distribution centres. You will need a second table to store the 1:n relation between an order and the multiple distribution centres. If your order table has the date of the order and the date it shipped, it should be easy to write a query and filter based on date, and join to the orders_distCentres to figure out which distribution centre got what, and when they got it. Generally, the schema could look like this:

orders
------
  order_id (PK)
  shipped_date
  received_date
  ordered_date
  (other fields)

distribution_centres
--------------------
  dist_cntr_id (PK)
  name (such as Montreal, Chicago, Dallas, etc...)

orders_distCentres
------------------
  order_id (FK to orders.order_id)
  dist_cntr_id (FK to distributions_centres.dist_cntr_id)

Also, I'm not sure I'd make "All" a valid choice in the database. It could lead to problems in the future where the definition of "All" changes when you add/remove distribution centres and query on shipments from before the definition of "All" changed. From a UI point of view, I'd think that "All" should be separate from the other checkboxes and when clicked, should activate them all. Don't submit the value of the "All" checkbox to your application.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.