Greetings,

This is probably a good topic for a paper but I was hoping that perhaps I could get a little advice or we could start a discussion here.

In asp.net I frequently have the need for a many to many type interface using an intersection table. For this project the tables are structured as follows... simplified for discussion purposes.


FoodItem
-------------
ItemID Integer
Description String C(100)


Category
------------
Catid Integer
Description String C(100)


FoodItemCategory
ItemID
CatID


Naturally this allows foods to fall in multiple categories. Now of course I would like to have an interface where you pick a food and then a series of checkboxes for each of the categories that would apply.

So the question from a high level perspective is how to best accomplish this as far as data access and populating the checkboxes.... then once the user makes changes by unselecting or selecting new categories; what is the best way to save the data back into the database.

Questions come to mind for me such as:

Do you do an outer join to grab the list of categories along with any records from the join table that would match those? Or would it be more simple just to grab the category records to setup the checkboxes and then do a separate query to manually set the selected state of each checkbox?

Would this type of operation be possible with tableadapters or is it better just to use a SQLDataSource and construct the necessary queries by hand in the code?

When saving back the data is there a good practice for how to delete the records that have become unchecked and then checking the correct records? One approach I'm considering is calling a delete query to just delete all associated categories and then insert in the ones I need.

Such as DELETE FROM FoodItemCateogry where Itemid = @TheItem

Seems like a lot of overhead however if you already have 8 categories checked and just add or remove one.

Overall this many to many interface thing seems to come up a lot so it would seem there is a simple framework/best practice that could be applied. I can always find a way to brute for this to work but was hoping there would be people out there with an opinion.

In short I'm looking for:

- Best way to retrieve and present an editor to manage the join table for a given item with checkboxes on the screen
- Best way to punch any changes into the back end data tables


Greg