Re: Discussion: the efficiency in using foreign keys
I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 20 Mar 2006, Martijn Tonies wrote: To: mysql@lists.mysql.com From: Martijn Tonies [EMAIL PROTECTED] Subject: Re: Discussion: the efficiency in using foreign keys Hi, 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? When it comes to referential constraints, the answer is simple: ALWAYS put them on the database. Anyway who answers differently either never had to recover a database that was trashed by the lack of integrity constraints or has no ide what he's talking about. Most probably, this statement will get me tons of e-mail again ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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]
Re: Discussion: the efficiency in using foreign keys
I've always been a believer in avoiding sql procedures, for the main reason that I want to be as database-independent as possible. I know it is less efficient, but being able to switch between MySQL, Postgre, and the new freebies from IBM, Oracle, and Microsoft is a strong advantage from the business perspective (of total cost to the customer, and customer preference). Of course, this is a discussion point. I'd love to hear from the community on their experiences. [EMAIL PROTECTED] wrote: I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Discussion: the efficiency in using foreign keys
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]
Re: Discussion: the efficiency in using foreign keys
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]
Re: Discussion: the efficiency in using foreign keys
Hi, 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? When it comes to referential constraints, the answer is simple: ALWAYS put them on the database. Anyway who answers differently either never had to recover a database that was trashed by the lack of integrity constraints or has no ide what he's talking about. Most probably, this statement will get me tons of e-mail again ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
