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.


Table X:
id name
1   A
2   B
3   C

Table Y:
id name
1   K
2   L
3   M

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


On Fri, Jun 20, 2008 at 8:38 AM, Hussein Jafferjee
> 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

Reply via email to