6 Replies Latest reply on Sep 19, 2006 8:31 AM by tbemcf14

    Best Way To Update Intersection Tables

    tbemcf14 Level 1

      I'm surprised I haven't been able to find anything on this, but I was wondering if I could get some advice as to the most efficient way to update an intersection table, i.e. a M:N resolution table.

      My example...

      Say I have a M:N relationship between tables Cars and Colors. To resolve, I form table Cars_Colors with Car_ID and Color_ID as PFK's from their respective table.

      So on my form, say we're dealing with record "Ford Ranger," I'll have a bunch of checkboxes with colors, in which multiple colors can be selected or de-selected. How should I handle this update?

      Currently, I just delete all the existing colors for the specific car, and then re-add the list of colors from the form... basically a delete then insert on the table... I don't use any update sql. But this is a nightmare when trying to keep audit history on data changes in the table.

      Any advice? Thanks in advance,