Fetching the same information in different ways

Discussion forum for MOD Writers regarding MOD Development.
Locked
User avatar
Gleasonator
Registered User
Posts: 652
Joined: Tue Jun 05, 2007 2:19 pm
Location: Texas
Contact:

Fetching the same information in different ways

Post by Gleasonator »

Hello,

Administrators can create "games" which will add entries to my `phpbb_games` table. I need users to be able to "join" these games, but they should be allowed to join more than one game at a time.

The problem is that I need to be able to quickly fetch which games a user is participating in, as well as which users are participating in any individual game.

So for example: on my modification's main page, I want to list the games that the current user is registered to. The seemingly most efficient way would be to store this information inside the user's row in the `phpbb_users` table. (A side note: is there a better way to do this without using a string type and delimiting with a symbol?)

On the other hand, I want each game to have it's own page, which lists every user that's participating in the game. The seemingly most efficient way to do this is to create a new table called `phpbb_games_users` and add rows for each user that is participating in each game.

Am I thinking too much into this? Using a new table seems cleaner, but I'm afraid it will take a very long time to cycle through each entry just to display which games the current user is added to.

Thanks for any advice!
-Alex
Sorry for my bad Engrish !
gleason|design

Image
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: Fetching the same information in different ways

Post by A_Jelly_Doughnut »

Gleasonator wrote: The problem is that I need to be able to quickly fetch which games a user is participating in, as well as which users are participating in any individual game.
Then what you really want is a simple two- or three-column table for the associations.

| user_game_id (primary key) | user_id (key) | game_id (key) |

Unless you have hundreds of thousands of entries in this table, queries against it will be very fast. There is probably no reason to cache this info in the users table... such systems can become hard to work with.

You have a second table for the actual game data (game title, etc.)
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
Locked

Return to “[3.0.x] MOD Writers Discussion”