Topic: wanted: modifying an item creates a new one
I've got a new app I'm working on where a user may modify something, but instead of updating the record, I want it to create a new, related record. For example, let's say a the site has a listing of colors and a user adds a color (e.g. green) to his profile. Then, later, he edits that color so it's more his style (e.g. olive).
I'd like to still have green in my colors table but then also add a new color called olive to the colors table. The tricky part is that the user would only have olive in his profile now but not green.
I was thinking about setting it up where uwer and color both has and belong to each other. Then if a user modifies the color, I create a new color (with perhaps a parent_id of the orignal color) and modify the join table. Does that sound about right? Is there a better/easier way to tackle it?