MySQL Question regarding database structure.

[skreet]

I'm making a system using PHP and MySQL that will allow me to keep track of the various board games I own and what all of my friends think of them. We're big gamers in that aspect so I want to be able to figure out what games would be best to play with which friends.

Here's my question: I add a new game. Whats the best way to append the data into the database of their opinions.

I've thought of adding new columns as I get new games titled 'opinionXX' where XX is the ID of the particular board game. I think that could result in very slow performance over time.

So then I came option B. I'll create a new table for each game as it's created named gameopinionXX, again where XX is the ID of the game. I would use the fields gamerID, opinion and comments. Where gamerID is a primary key unique so every person can only have one opinion.

Is there any loss to creating tons of tables vs creating very large tables?

Or is there a better approach I haven't thought of yet?

Thanks in advance,

Skreet

 

[Kilrsat]

Keep an Opinion table.


GameID, GamerID, Opinion, Comments


Make the GameID/GamerID pair the unique constraint.

Its a fairly standard many-to-many relationship.

 

[MrChad]

I would approach it this way.

Have one table for games. Give it a few columns such as GameID, Name, Description, Number of Players, etc.

Have another table for gamers. Give it these columns: GamerID, Name, Alias, etc.

Have another table called opinions. Give it these columns: GameID (references the GAMES table), GamerID (references the GAMERS table), Opinion and Comments.

New games are just new entries in the GAMES table. Same goes for gamers in the GAMERS table. If you want to limit gamers to one opinion per game, make GameID and GamerID a composite primary key in the OPINIONS table.

EDIT: Kilrsat beat me to it.

 

[Homerboy]

heh ironic this post came up.
I'm doing kind of the same thing, except creating my home-brew polls for a webstie I run (see sig).
I want each user to only be able to vote for each poll once and I'm having a devil of a tme setting that up

right now I have the tables/fields set up as follows:

poll_info: poll_id, timestamp, question
poll_questions: question_id, poll_id, answer
poll_responses: poll_id, answer_id, team_id

I kind of undertand what you guys are saying here... but please elaborate more if you could...

Edit: I'm using mySQL front as my GUI frontend for DB mangement

Thanks!

 

[Homerboy]

actually I figured out part of the solution... I set poll_id, answer_id and team_d in poll_reponses all as part of the primary key so row 1-1-1 can't be added more than once works fine...
now I'm having a devil of a time creating a query to show ONLY the polls they have not voted in thus far... like if I have 10 polls, they have voted in 4 of them, I can't come up with a working SQL query string to only display those 6 polls remaining...

 

[skreet]

Thanks for the help guys. Very simple solution.

 

[skreet]

Make the GameID/GamerID pair the unique constraint.

Whats the SQL syntax to make a pair unique? Just set both to 'unique key'?

 

[Thyme]

Add
", primary key (GameID, GamerID)"
to the table definition
or "alter table blah add primary key (GameID, GamerID)"

 

[Homerboy]

any help me on my issue? on getting ONLY those where they have not voted to display?

 

[Thyme]

try

select * from poll_info where (select poll_id from poll_responses where team_id ='blah') is null