What is the best way to handle inserting/updating an object with multiple category checkboxes?

I need some guidance on best practice.

I have two SQL tables, one called Person which c开发者_开发百科ontains PersonId, PersonName and another called Category which contains CategoryId, CategoryName. The Categories can be quite numerous and added to occasionally.

I want a create view which shows a textbox for PersonName and then a single checkbox for each of the categories in the Category table.

The user can enter a name and tick as many of the category checkboxes as they want and then save.

Is the correct approach to insert the PersonName in the Person table and then insert an entry for each person/category combination in a new table called PersonCategory which has fields PersonId and CategoryId?

If I do this how do I handle updates if the categories are changed in the edit view? That is a category may be unchecked against a person. Does the record in the PersonCategory need to be deleted?

Typically, for something like this, I would refrain from UPDATEs. I would delete all entries for a given person from PersonCategory, and then re-insert the ones that are being saved in PersonCategory.

This is how I do it. I will go through the following.

1. View Model
2. ASP.NET MVC View (User Interface)
3. Control Post Action Method
4. Algorithm to Save

View Model

public class PersonViewModel
{
    public int Id { get; set; }

    public string PersonName { get; set; }

    // For rendering Checkboxes in the MVC View
    public IList<SelectListItem> CategoryCheckboxes { get; set; }

    // For data binding on Postback (HTTP POST).
    // <input type="checkbox" /> will have the same `name` attribute as this.
    public IList<int> AssociatedCategories { get; set; }
}

ASP.NET MVC View

<div>Name: <%= Html.EditorFor( m => m.PersonName ) %></div>

<fieldset>
    <legend>Associatied Categories</legend>

    <% foreach (SelectListItem c in Model.CategoryCheckboxes) { %>
        <!-- 'name' attribute is same as the C# View Model property name -->
        <%= Html.CheckBox( "AssociatedCategories", c.Selected, new Dictionary<string, object> { { "value", c.Value } } ); %>
        <%= Html.Encode( c.Text ) %><br />
    <% } %>

</fieldset>

Controller Post Action Method

[HttpPost]
public ActionResult Edit(PersonViewModel viewModel)
{
    Person p = personService.Update( viewModel ); // persist changes to DB
    if (p == null) // person record doesn't exist in DB
        return RedirectToAction("NotFound");

    return RedirectToAction("Edit", new { id = viewModel.Id });
}

Algorithm to Save

// this is in the `PersonService` class.
public void Update(PersonViewModel viewModel)
{
    Person p = personRepository.GetByPrimaryKey( viewModel.Id );
    if (p == null)
        return null;

    p.PersonName = viewModel.PersonName;


    IList<int> existingAssociatedCategories = GetAssociatedCategories( p.PersonId );
    foreach( int i in viewModel.AssociatedCategories )
    {
        if (!existingAssociatedCategories.Contains( i ))
            Associate( p.PersonId, i ); // add NEW association in DB
    }


    foreach( int k in  existingAssociatedCategories)
    {
        if (!viewModel.AssociatedCategories.Contains( k ))
            DeleteAssociation( p.PersonId, k ); // delete existing association
    }

    repository.UpdatePerson( p ); // save changes to DB for Person object

    return p;
}

Let me know if you have any question.

The answers to your questions are yes, add/delete them where added/removed, and yes.

This is a many-to-many relationship, which is represented relationally using a link table.