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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
