mysql.com says:
"Foreign key enforcement offers several benefits to database developers:
* Assuming proper design of the relationships, foreign key
constraints make it more difficult for a programmer to introduce an
inconsistency into the database.
* Centralized checking of constraints by the database server
makes it unnecessary to perform these checks on the application side.
This eliminates the possibility that different applications may not
all check the constraints in the same way.
* Using cascading updates and deletes can simplify the
application code.
* Properly designed foreign key rules aid in documenting
relationships between tables.
Do keep in mind that these benefits come at the cost of additional
overhead for the database server to perform the necessary checks.
Additional checking by the server affects performance, which for some
applications may be sufficiently undesirable as to be avoided if
possible. (Some major commercial applications have coded the foreign
key logic at the application level for this reason.)"
(http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html)
Obviously. Regardless, whenever a many to many relationship exists, a
link table is the solution, with or without foreign keys. I don't know
what the overhead of using foreign keys is, but that's something you
can test. ON UPDATE and ON DELETE do make things a lot easier.
Evert
On Fri, Jun 20, 2008 at 2:41 PM, J. Hill <jh@xxxxxxxxxxx> wrote:
> In a number of cases I have used link tables like you are suggesting here,
> but I am curious if using foreign key constraints, in the situation
> described, is the best solution?
>
> I am not an expert (so this is a real question), but I remember some years
> ago a database programmer (I believe he worked for MySQL) advising against
> the use of foreign key constraints when possible, due to the overhead when
> updating. I know avoiding them requires careful programming/scripting, but
> his argument seems logical to me.
>
> If he was wrong, I'd sure like to know, because that would make my
> development work much easier.
>
> Jeff.
>
>
> Evert Lammerts wrote:
>
> Whenever you can say:
>
> "X has zero or more Y" and "Y has zero or more X" you're talking about
> a "Many to Many" relationship between X and Y, and you need a link
> table with foreign key constraints.
>
> Example:
>
> Table X:
> id name
> 1 A
> 2 B
> 3 C
>
> Table Y:
> id name
> 1 K
> 2 L
> 3 M
>
> Linktable:
> X_id Y_id
> 1 1
> 1 2
> 2 3
> 2 1
> 3 1
> 3 2
> 3 3
>
> The link table is the only table that needs to be updated when a
> relationship between an entry of X and an entry of Y starts to exist
> (in your example, a user from X collects an item from Y).
>
> It is wise to use foreign keys when using linktables - but you can
> only do this when using INNODB. Check
> http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
>
> Evert
>
> On Fri, Jun 20, 2008 at 8:38 AM, Hussein Jafferjee
> <husseinjafferjee@xxxxxxx> wrote:
>
>
> Hey Guys,
>
>
>
> I have been developing for a while and never found the best solution yet.
> The question is lets say a game has 10 collectibles you can earn, to keep
> track of the number of collectibles each user has, do you have one field in
> the users table with all the numbers separated via a divider, or do you make
> a new table called collectibles and have each one as a field?
>
>
>
> These are high traffic sites (100,000+ people) and so I was initially
> thinking the solution of creating a separate table is best because the main
> users row is loaded on every page, and on top of that you would need to use
> explode on the field.
>
>
>
> Currently I am having a separate table, but I was wondering if people have
> better solutions.
>
>
>
> Hussein J.
>
>
>
>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
