List:General Discussion« Previous MessageNext Message »
From:Foo Ji-Haw Date:March 20 2006 6:55am
Subject:Re: Discussion: the efficiency in using foreign keys
View as plain text  
Hello Keith,

Thanks for responding. I was actually referring to the subject of using 
foreign keys, as opposed to leaving it to the calling application to do 
the necessary checks. In particular issues of performance, efficiency 
etc. IMHO, foreign keys add 'strictness' to the consistency of related 
tables. But for me, I have not bothered to do so, preferring to do the 
checking from the application instead.

Appreciate your detailed example on normalising the database, which I 
agree with you that it is much preferred against a mega table.

mysql@stripped wrote:
> IMHO I think you will find that there is a balance between 
> the speed of opening and reading/writing several related
> smaller tables connected by FK's, rather than one mega-sized 
> gigantic table. 
>
> How do you normalise a table without using FK's.
>
> Your right, MySQL does not currently do any checking for 
> FK's, but this does not mean that you cannot still use them 
> in MyISAM tables.
>
> Eg.
>
> /* table to store quiz questions */
>
> CREATE TABLE `quiz_question` (
>   `ID` mediumint UNSIGNED NOT NULL auto_increment,
>
>   `question_text` text NOT NULL,
>
>   PRIMARY KEY `ID` (`ID`)
> ) TYPE=MyISAM AUTO_INCREMENT=1;
>
>
> /* table to store quiz answers */
>
> CREATE TABLE `quiz_answer` (
>   `ID` mediumint UNSIGNED NOT NULL auto_increment,
>
>   `answer_text` text NOT NULL,
>   `status` enum('wrong', 'right') NOT NULL,
>
>   `questionID` mediumint UNSIGNED NOT NULL default '0', 
>
>   PRIMARY KEY `ID` (`ID`),
>   KEY `questionID` (`questionID`)
>
> ) TYPE=MyISAM AUTO_INCREMENT=1;
>
>
> /* table to track quiz questions with user answers */
>
> CREATE TABLE `quiz_result` (
>   `ID` mediumint UNSIGNED NOT NULL auto_increment,
>
>   `user` char(32) NOT NULL default '',
>
>   `questionID` mediumint UNSIGNED NOT NULL default '0',
>   `answerID` mediumint UNSIGNED NOT NULL default '0', 
>
>   PRIMARY KEY `ID` (`ID`),
>   KEY `questionID` (`questionID`),
>   KEY `answerID` (`answerID`)
>
> ) TYPE=MyISAM AUTO_INCREMENT=1;
>
> So in the quiz_result table above questionID is a 
> column holding the primary key of each question_text column 
> in the quiz_question table. It is a foreign key.
>
> answerID is a foreign key that points to the primary 
> key of the answer submitted by the user doing the quiz.
>
> When the user has finished doing the quiz, the quiz_result 
> table is scanned for the user session ID, 'user', and then 
> the question and the user's chosen answer are picked from 
> the quiz_question and quiz_answer tables, using the foreign 
> keys in the result table.
>
> I find it helps me to think of foreign keys as unique 
> pointers to rows in other related tables.
>
> HTH
>
> Keith
>
> In theory, theory and practice are the same;
> in practice they are not.
>
> On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
>
>   
>> To: mysql@stripped
>> From: Foo Ji-Haw <jhfoo-ml@stripped>
>> Subject: Discussion: the efficiency in using foreign keys
>>
>> Hi all,
>>
>> This is a fundamental concept in RDBMS: the use of foreign keys in
>> database design.
>>
>> I'd just like to poll the community here, on whether it is a best
>> practice, or practically essential to 'link' related tables by use of
>> foreign keys.
>>
>> For myself, I usually do all the validity checking when adding a new
>> record that references a record id from another table. I understand that
>> this may not be efficient because it becomes 2 database calls (and db
>> calls are expensive in high-load environments).
>>
>> What are the advantages/ disadvantages in using foreign keys? In MySQL,
>> this means one cannot use MyISAM. Do you place a lot of triggers as well?
>>
>> Thanks.
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>>     
>
>   

Thread
Discussion: the efficiency in using foreign keysFoo Ji-Haw20 Mar
  • Re: Discussion: the efficiency in using foreign keysmysql20 Mar
    • Re: Discussion: the efficiency in using foreign keysFoo Ji-Haw20 Mar
  • Re: Discussion: the efficiency in using foreign keysMartijn Tonies20 Mar
    • Re: Discussion: the efficiency in using foreign keysmysql20 Mar
      • Re: Discussion: the efficiency in using foreign keysFoo Ji-Haw21 Mar