Re: How would you handle updating an item and related stuff all at once?

On 2/17/07, Rick Schumeyer <rschumeyer(at)ieee(dot)org> wrote:
> This may be bad design on my part, but...
>

Not at all. Very common scenario

> I have three tables of interest...Account, Employee, and
> AccountEmployeeRelation. There is a many-to-many relationship between
> accounts and employees. The join table also contains a column
> indicating what role the employee plays on this account.
>
> My interface is a web app (I'm trying out Ruby on Rails). On the "edit
> account" screen I want to edit account attributes AND be able to
> add/delete employees in one form. The gui part seems to work.
>
> BUT, when I update I'm not sure how to handle updating the
> AccountEmployeeRelation table. During the update, relations may have
> been added or deleted, and existing relations may have been changed. It
> seems to me the easiest thing to do is delete all the relations for the
> account and create all new ones with the data submitted from the form.
> This seems wasteful, but the alternative would be a pain. Or is this
> really the best way?
>

I tried a bunch of cleverness where I checked for existence and
updated if required, etc but came back to just zapping them all and
inserting. As long as it's done in a transaction and there are not
too many, it's fine. It doesn't eat any more space and eats less
cycles than doing it the hard way.

> Thanks for any advice.
>

You're welcome!

> Completely off topic, (but not worth a separate post) I have been forced
> to use a little bit of mysql lately...did you know that if you use
> transaction and foreign key syntax with myisam tables, it does not
> complain...it just silently ignores your requests for transactions and
> foreign key checks. Yikes! I had incorrectly assumed I would get an
> error message indicating that transactions are not supported. Oh well.
>

Sorry about that. Nuff said 8^/

- Ian