Re: [PHP-DB] One field or a new table?

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
<[email protected]> 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.
>
>