One field or a new table?

------=_NextPart_000_010B_01C8D265.A5C218F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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.


------=_NextPart_000_010B_01C8D265.A5C218F0--

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-co nstraints.html

Evert

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

Re: One field or a new table?

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-ke ys.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 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-co nstraints.html
>
> Evert
>
> On Fri, Jun 20, 2008 at 8:38 AM, Hussein Jafferjee
> 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

Re: One field or a new table?

I am tempted to say that you can test both by benchmarking them. I
assume that you need this to be fast since many users use it, so try
both ways and see what it is better. Have in mind that the underlying
engine is important when designing something that gets heavy use. I get
the feeling that if the collectibles are few say 10-15 in that case
adding a field tends to be a better idea.
You can benchmark this by counting the time that you select and extract
the collectibles in an array in either way. I assume that you will need
to select from two tables user and collectibles in case you separate
them. This is probably slower if the collectibles are few in number but
I think it gets better when their number increases badly.

Tell us what you think.

--
Thodoris



O/H 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