Well said Dan. While foreign keys, cascades and built-in transactions are
convenient, atomicity and referential integrity are readily attained by
proper implementation and the appropriate code.
<soap box>
Setting buffers,dirty flags and commit functions really is the
responsibility of the application programmer. A lack of referential
integrity is not the same as data corruption. More creative coding
and less whining.
</soap box>
No offense intended, I just need an occasional rant.
Pat...
----- Original Message -----
From: "Dan Nelson" <[EMAIL PROTECTED]>
To: "b b" <[EMAIL PROTECTED]>
Cc: "mysqllist" <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 4:59 PM
Subject: Re: Transactions
> In the last episode (Jul 29), b b said:
> > Most web hosting companies run the mysql standard. Which means one
> > can't run transactions. If that is the case, then how do you handle
> > many to many relationships with truely normalized manner without
> > risking data corruption
> >
> > For example: You have an org, ctry tables and a middle table orgCtry.
> > The middle table has the two foriegn keys one from org and one from
> > ctry. This way an org could be operating in many countries and a
> > country could have many organizations.
> >
> > Now to insert an organization "AAA" that operates in USA, Canada, and
> > Argentina one would need typically to do four sql statements:
> > insert into org(name, .....
> > get the newly inserted org ID
> > insert into orgCtry(ID for org, ID for USA) ...
> > insert into orgCtry(ID for org, ID for Canada) ...
> > insert into orgCtry(ID for org, ID for Argentina)
> > ...
> >
> > To do the above securely one has to put it in a transaction. If
>
> You really mean "To do the above atomically" here. You can still do it
> securely, but you have to make the client apppplication smarter. If
> the server crashes after the 2nd insert, you end up with 1 record in
> orgName and 1 in orgCtry. So when the end-user retries the request,
> the client has to realize that AAA already exists and simply insert the
> remaining two records.
>
> When you decide to delete the "AAA" user, make sure you delete
> dependent records first. So remove the orgCtry records before removing
> the parent record in org. Otherwise, if the server crashes, you end up
> with dangling records that you have to clean up in a maintenence script
> (not difficult, but not necessary if you delete in the right order).
>
> Transactions are most important in places where you have to update
> multiple records or tables, and /cannot/ allow a partial update
> (double-entry bookkeeping, etc). Foreign keys (and subqueries) are
> handy to have, but you can always duplicate their functionality with
> extra code in the client.
>
> --
> Dan Nelson
> [EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
