Thread: How would you handle updating an item and related stuff all at once?
This may be bad design on my part, but... 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? Thanks for any advice. 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.
I'm not sure I understand completely, but here's one idea. in the backend, when the user submits their changes: 1) Pull the current state of AccountEmployeeRelation for the account you're working on 2) Compare the current state to what the user posted, and determine what needs to be added and deleted (I use array_diff in php for this) and obviously anything else should get updated. 3) commit This is nice because even if there's relations, if the user makes 3 changes, the database only makes 3 changes, instead of reloading the entire list. If you're worried about concurrent users changing the same accounts, you'll want to lock the account prior to step 1. something like "Select * from Accounts where AccountID=$1 for update" should do nicely. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rick Schumeyer Sent: Friday, February 16, 2007 11:31 PM To: pgsql-general@postgresql.org Subject: [GENERAL] How would you handle updating an item and related stuff all at once? This may be bad design on my part, but... 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? Thanks for any advice. 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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Rick Schumeyer wrote: > 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. I ran into the same thing. Actually it may have been that a dump, restore caused tables to be created with myisam engine instead of innodb. Regardless, I lost faith in MySQL except for things it good at... fast read only web database.
> 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 do it the same way.. I'm open to suggestions about better ways to do it too but *shrug* this works well. Are you doing this action a lot or is it an occasional thing that will happen? That is - is it worth investing a lot of time in to finding another approach? > 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. Well, it's documented. http://dev.mysql.com/doc/refman/4.1/en/ansi-diff-foreign-keys.html For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. -- Postgresql & php tutorials http://www.designmagick.com/
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
On Feb 20, 2007, at 9:17 AM, Ian Harding wrote: > On 2/17/07, Rick Schumeyer <rschumeyer@ieee.org> wrote: >> 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. Be careful about some of the things Rails tries to push you towards, such as "RI belongs in the application", and "surrogate key fields should be named 'id'" (I *much* prefer the form "object_id", ie: user_id, used *everywhere*, including the user table (in that example)). Fortunately, with rails extensibility it shouldn't be hard to change those default behaviors (in fact there's probably a patch somewhere for the first case...) >> 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. Actually, zapping and re-creating everything *does* take more space, due to how MVCC works in PostgreSQL. But so long as you're not doing that a heck of a lot, it's probably not worth worrying about. It might be worth detecting the case where nothing changes, though (which I suspect could be done with creative use of INTERSECT and it's ilk). >> 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^/ That's one gotcha out of about 100. Google 'mysql gotchas' and hit the first link. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On 22.02.2007, at 15:56, Jim Nasby wrote:
> and "surrogate key fields should be named 'id'" (I *much* prefer
> the form "object_id", ie: user_id, used *everywhere*, including the
> user table (in that example)).
> Fortunately, with rails extensibility it shouldn't be hard to
> change those default behaviors (in fact there's probably a patch
> somewhere for the first case...)
It's really not hard to use the integrated mechanism for that:
class MyTable < ActiveRecord::Base
set_primary_key "mytable_id"
end
There are nevertheless usage glitches as far as I know. But it's no
real problem.
cug