List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 6 1999 5:47pm
Subject:Re: Normalization w/ MySQL?
View as plain text  
In the last episode (Oct 05), Steve Freitas said:
> Someone please correct me on this if I'm wrong.
> 
> The way I understand MySQL is that foreign keys don't work. What this
> means is that MySQL is incompatible with any database schema that's
> in normalized form. Is this true?

No; it just means that the application has to enforce referential
integrity.  If you're using a front-end program like Access, it usually
handles all this anyway.
 
> And if this is true, what does everybody do? If I want to, say, add
> something to the shopping cart table for someone, I'd have to insert
> their account number along with everything else. And if they changed
> their account number over in their account management screen
> (unlikely but go with me), unless my program went through and
> modified the stored account number in the shopping cart table, it'd
> be lost to them, wouldn't it?

One of the big rules for enforcing table-table relationships is "don't
let the primary key change, *ever*." This means that account number is
not an acceptable primary key if the user can change it.  Usually you
have autonumbers or sequences that generate primary keys for each
table.

As for deleting orphan records (say you remove a customer record and
want all their chopping-cart items removed), you can either remove
child records at the time of deletion, or have a nightly cron job that
goes through and removes all orphan records en masse.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Normalization w/ MySQL?Steve Freitas6 Oct
Re: Normalization w/ MySQL?Thimble Smith6 Oct
Re: Normalization w/ MySQL?Steve Freitas6 Oct
Re: Normalization w/ MySQL?Dan Nelson6 Oct
  • Re: Normalization w/ MySQL?James Manning6 Oct
    • Re: Normalization w/ MySQL?Dan Nelson6 Oct
Re: Normalization w/ MySQL?Steve Freitas6 Oct
Re: Normalization w/ MySQL?Vivek Khera6 Oct
Re: Normalization w/ MySQL?Bob Kline7 Oct