Hi.
On Thu 2002-06-20 at 14:50:07 -0700, [EMAIL PROTECTED] wrote:
> Friends,
>
> I am designing a MySql DB schema that will use foreign
> keys similar to:
>
> create table orders(id int auto_increment primary key,
> amount double(7,2) );
>
> create table orderItems(id int, item char(20), price
> double(5,2), quantity int, key(id), foreign key id
> references orders (id));
>
> I am currently not using a version that supports
> foreign keys but hope to be in the future. In the
> meantime I'll need to implement the foreign key
> constraints through my application. My concern is
> that if either of the tables get corrupted or out of
> sync I won't be able to match up the order items with
> the orders.
>
> Is there anything that I might do to ensure that I'll
> be able to recover if the tables get out of sync?
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.
A method to guard against this is to insert orders with a column set
to "incomplete", insert orderItems, then update orders to "complete".
Write your programs to ignore rows without status=complete (best if
this is capsulated into an own class which all programs have to use).
> I've thought of adding timestamp columns to each table
> as an aid in matching up the records but am not sure
> how helpful this would be or if it would be worth the
> extra storage space.
Good idea. I do that to all tables. I cannot count how often it
enabled me to reproduce what strange strings others did. In fact, I
add to timestamp columns, one as creation time and one as last-changed
time, the latter beeing automatically updated by MySQL every time.
Regarding storage: I only leave them away for tables which will change
very seldom (e.g. a country list) or where I really know, that the
space difference will matter (you can always delete them later, you
know).
> Any comments about your experience using foreign keys and MySql
> would be appreciated.
Regards,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
