Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux