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.
[EMAIL PROTECTED] 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@lists.mysql.com
From: Foo Ji-Haw <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
