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

On 2/17/07, Rick Schumeyer <rschumeyer@ieee.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