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

On Feb 20, 2007, at 9:17 AM, Ian Harding wrote:
> On 2/17/07, Rick Schumeyer <rschumeyer(at)ieee(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)