How to update a one-to-many relationship from a CheckBoxList, ListBox, etc?

Given 2 tables开发者_如何学Python:

Person

PersonsFavoriteColors

A person can have one or more favorite colors. These colors are updated with a multi-select control (CheckBoxList, ListBox w/ multi-select enabled).

In the past, if I am updating the person's colors, I'd:

1. Start Transaction
2. Delete all color records for the person
3. Insert records for each selected color
4. Commit Transaction

Is this the standard and best practice for handling multi-select controls that add / update / delete records in "to-many" child tables?

Thanks!

I normally wouldn't delete all the old colours but rather just the ones that were no longer favourites and then I'd only add the ones that were actually new.

If you're not locked in to two tables, a relatively simple way to store values the way you've described is to use an array as the data type of favorite colors instead of a separate table.

http://www.postgresql.org/docs/8.0/interactive/arrays.html

If that is an acceptable option, your updating instructions would simply be:

1. Start Transaction
2. Update Row
3. Commit Transaction