List:General Discussion« Previous MessageNext Message »
From:mysql Date:March 20 2006 6:38am
Subject:Re: Discussion: the efficiency in using foreign keys
View as plain text  
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