On Thursday 20 June 2002 21:15, Benjamin Pflugmann wrote: > IMHO, the interesting question is: how could the tables get out of > sync, in a way that foreign keys would be able to prevent? I am not > able to see a way in everyday work (explicitly filling in nonsense > does not count). > > The only thing I can see that could happen in normal use is that you > get an entry for orders but none in orderItems, because a program is > interrupted. That could happen with foreign keys as well. > Well, there are a couple of considerations. When you simply consider something simple like you have a webapp and it manipulates a database, then its probably not too much of a concern that things can get out of sync. The world is rarely that simple though. Over time its quite possible other bits of code will be written for various reasons which access the same data, for purposes of archiving, reporting, order fulfillment, etc. Some of that code is bound to monkey with the database, and all it takes is one bad piece of code to botch things up.
Aside from using key constraints, the other strategy is to use transactions. That does protect you against the problem of an aborted update. The two should be used together really, that way you not only can't create an order for a non-existent item, you also can't end up with HALF an order in the system, which is just as important. While, as the previous poster suggested, you can use some sort of flag, this has several implications and doesn't entirely solve the problem. 1st of all it doesn't prevent an order from never being flagged as complete when it should be. 2nd the code is less certain to behave properly in the case where an insert of one item in an order fails but others don't. A slight error in the code could lead to half an order in this case once again. Given that you don't have transactions or foreign keys available at this time you might want to do as suggested, but I'd upgrade to a version of MySQL that does support them as soon as possible. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
