Re: 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 <husseinjafferjee@shaw.ca> 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. > >