List:General Discussion« Previous MessageNext Message »
From:b b Date:July 29 2003 8:08pm
Subject:Transactions
View as plain text  
 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 Mysql standard doesn't support
transactions then the only way to do it is  to have
the org table include a field ctry that is a string of
all the countries invloved or their ID's

 This way one could do an insertion in one sql
statement: insert into org('AAA',
String_of_the_ID's_of_the_countries', ....)

 But this option is not really what a normalized db is
all about. Also, it is certainly susceptable to data
corruption. Suppose we delete Argentina from the ctry
table; then the ID for it that is still in the string
is pointing to nowhere (data corruption).

 So is there a third solution? 

Cheers.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
Thread
Transactionsb b29 Jul
  • Re: TransactionsDan Nelson29 Jul
    • Re: Transactionsb b30 Jul
      • Re: TransactionsPatrick Sherrill30 Jul
        • Re: Transactionsb b30 Jul
        • Re: TransactionsKaarel31 Jul
          • Re: TransactionsStephan Lukits1 Aug
  • Re: TransactionsPatrick30 Jul
  • Re: TransactionsPaul DuBois30 Jul
RE: TransactionsGilbert Wu30 Jul