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]

 



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@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Rick Schumeyer
Sent: Friday, February 16, 2007 11:31 PM
To: pgsql-general@xxxxxxxxxxxxxx
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



[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