game comparison database design help

I want to create a game comparison database using the data in the table at http://tnlc.com/eep/compare/table.html

What would be the best way to design the database relationally?

Currently, I have a main "compare" table that has: "id", "gameID", "circa", "genreID", "publisherID", "engineID", and "developer" fields.

The "games" table has "gameID" and "game" fields.

No "circa" table since it's just a 4-character year that gets entered (although I suppose I could have a table with all the different years but surely there's an easier way to sort by year without having to have the year in a separate table?).

"genres" table has "genreID" and "genre" fields (do I have to have genres in a separate table like this in order to be able to efficiently sort them?). I want multiple genres to be selected from checkboxes, including a text-entry field for unlisted genres.

"publishers" table with "publisherID" and "publisher" fields (same issue as above)

"engine" table with "engineID" and "engine" fields (same issue as above)

No "developer" table for same reason as no "circa" table.

I'm finding that having all these tables makes it a bitch to do SELECT statements and stuff--but perhaps I'm not doing them right. Argh...please help!

Read through some of the already posted forums related to relational databases, what they are, how they work, etc. Search the internet too. The queries are probably the hardest part, but the forums have many posts with examples of queries in a relational database. For this reason, I'm not going to get too far into this, just a few notes and pointers.

Ordering results in a query is done using ORDER BY column, so this notion that having things in a different table so that you can sort/order more efficiently seems a little skewed.



The use of separate tables for separate information is for storage efficiency. Though not always the case, try to think of table relations as a one-to-many relationship. There are `many` games within `one` genre. `table.genres` has a one-to-many relation to `table.games`. The genres tables holds the name of each genre once and the games table holds the id of that genre. To change the genre's label (let's say you misspelled it), you change it once in table.genres and it's reflected in all references from table.games.

I've read through plenty of relational database websites and forum posts but I'm still unsure.

Shouldn't just the game names be in the "games" table since there can be at least 2 games with the same name if the name is common enough?

I have the game's genre(s) in the main "compare" table which is the compilation table of all the other tables--the 4th order of normalization or whatever it's called.

The reason I have a separate table for genres is because I've seen some tutorials suggest using a table for checkbox values instead of using a "set" datatype. If you know of a better way, please let me know.

I also want the checkboxes to be checked when editing the game's genre (among other things--the form is mostly made up of checkboxes but I want to be able to support same-named text-entry fields for most of the checkboxes).

Any suggestions would be appreciated! Thanks.

First thing: that is one huge form! Maybe a multipage form with one section per page would be better? Just a suggestion.

The form helps me see what kind of informatin you're trying to store and gives me a better idea of how you're trying to use it. That's a lot of information for sure. Is this your first experience in databasing? Seems like a large first project if it is.Just to be clear, does this mean that you want a user to be able to enter text for `something` for which there is no checkbox? If so, do you want to store that newly created `something` in a table so that when the form is created next time, it appears as a checkbox? Thus, effectively allowing users to create checkboxes for your form so that no `something` is left out, and so that future users have that same option?

I think I'd have to see a full printout of your table structures to really be able to help.

Heh, yea, you should see the table!

The form will eventually be split up into separate pages, which is why I've started separating the form categories into separate tables--and some of those categories, particularly engine and level/world design, will most likely be split up into separate tables/pages too, similar to how MobyGames has its game submission set up.

It IS a big first project for a database but I've been wanting it for years and since no one's ever contacted me about helping me create it, I figured I'd just break down and attempt it myself. Unfortunately, I don't enjoy programming but I'm willing to learn at least PHP if it will help me do what I want with all this info.

As for the text-entry fields, most (if not all) will have checkboxes associated with them, as is layed out on the game entry form; the text-entry fields are there to accept more detailed info pertaining to the checked feature. So if someone checks the genres "action" and "adventure", and they believe the game to include something else not listed (like "educational" or a more detailed genre description), they'll be able to enter that info in the text-entry field and it will go in the appropriate "other genre" field in, I guess, either the "genres" table or perhaps the main "compare" (ID compilation) table? I'm not sure.

However, what you mentioned about allowing people to add options (after I validate them, of course) would be convienent, since MobyGames also allows this with developers/publishers anyway--it would be great to take it a step further and have it apply to other attributes too!

Here are the table structures as per phpMyAdmin:

# ID/key compilation and general info

CREATE TABLE compare (
id tinyint(4) NOT NULL auto_increment,
gameID tinyint(4) default NULL,
circa year(4) default NULL,
genreID set('1','2','3','4','5','6') default NULL,
publisherID tinyint(4) default NULL,
engineID tinyint(4) default NULL,
developer varchar(40) default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;


# 3D engine

CREATE TABLE engine (
engineID tinyint(1) NOT NULL auto_increment,
name varchar(40) NOT NULL default '',
modified enum('0','1') default NULL,
url varchar(40) default NULL,
developer varchar(40) default NULL,
type set('polygon','voxel') default NULL,
PRIMARY KEY (engineID)
) TYPE=MyISAM;


# game names

CREATE TABLE games (
gameID tinyint(4) NOT NULL auto_increment,
game varchar(50) NOT NULL default '',
PRIMARY KEY (gameID)
) TYPE=MyISAM;


# genre names

CREATE TABLE genres (
genreID tinyint(1) NOT NULL auto_increment,
genre varchar(20) default NULL,
PRIMARY KEY (genreID)
) TYPE=MyISAM;


# player model/character

CREATE TABLE player (
id tinyint(4) NOT NULL auto_increment,
character varchar(50) default NULL,
footprints varchar(20) default NULL,
footstep_sound varchar(20) default NULL,
actions varchar(20) default NULL,
view varchar(20) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;


# publisher names

CREATE TABLE publisher (
publisherID tinyint(2) NOT NULL auto_increment,
publisher varchar(25) default NULL,
PRIMARY KEY (publisherID)
) TYPE=MyISAM;


# submitter accounts

CREATE TABLE users (
id tinyint(4) NOT NULL auto_increment,
name varchar(20) default NULL,
email varchar(50) default NULL,
password varchar(20) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

They're obviously unfinished as to all the fields they'll contain but they're basically going to be split up like the game form categories--and subcategories where everything is bolded on the left side: engine (type, atmosheric/environmental effects, collision detection, lighting, particle effects, etc)--though I'm not sure what I can leave out of separate tables and what I can just use sets/enums/arrays/whatever for.

Anyway, any advice you can give would be great--thanks again.

Just a note: In the `compare` table you have genreID field with a set. Do you plan on having only 6 genres? What happens if you did what was discussed and allow people to generate genres on the fly in the form? Additionally, the way you describe it, a single game could belong to many genres. So how did you plan on storing mulitple genres in the table?

Also, the genreID is a tinyint(1), which limits you to 1-9. I notice this is done a lot throughout your tables and I understand your intentions, but if you're auto-incrementing you may want to make those a little larger.

Not knowing PHP very well is hendering your creation of the database as well since you don't know how you'll have to, or be able to, work with the data in database easiest with regards to PHP.

Expect to have a fair number of tables in this database. Some of them you might think are pointless, a waste, or would grow too large, but that's what they're for. Let me see if I can give you an example using what you have so far:

table.game
gameID
game

table.genres
genreID
genre

If a game only belonged to one genre then you'd simple add genreID to table.game and it would store the ID from table.genres for the related genre. However, with your plans, you'll need another table:

table.assign_genres
ID
gameID
genreID

Now if you had a game with ID of 1 and it belonged to the genres with IDs of 4 and 5, your table contents would look like this:
ID | gameID | genreID
1 | 1 | 4
2 | 1 | 5

table.assign_genres has the potential to have a lot of records, but keep in mind all we're storing is numbers.

I was just experimenting with the "genreID" set in the "compare" table, but isn't there a way to just add more genres to the set vs. having to create another table to handle the genre-game relationships that can seemingly be handled in the main "compare" table via the "genreID" field?

For example, if a game is "action" and "adventure", I have the "genreID" field as "1,2". The problem I'm having is how to get both numbers out individually and have their appropriate checkboxes appear checked when editing the game's entry. Right now it just adds the numbers together, totalling "3" and checks the "role-playing" box. Doh!

As for the tinyint(1), I'll increase it if more than 9 genres are added. If any fields are added I will approve them first and change their field type accordingly. Right now I have exactly 9 genres: action, adventure, role-playing, simulation (city/empire-building, driving/racing/riding), and strategy (real-time, turn-based). I may split up the simulation sub-genres which would, of course, require tinyint(2) but I just want to get the existing genres working correctly with their checkboxes first.

You would pull that set as the string '1,2' and PHP explode() it into an array so that you had '1' and '2' separately. I've done databases both ways, storing an array as a string, like the way you've done it, and by using additional tables. It really just depends on preference and how you want it done -- though I believe that Bytes are save one way over the other. As I mentioned, not knowing the PHP is hendering you a little I think too.

Well, I'm trying to learn both PHP AND MySQL simultaneously so that, of course, makes it more difficult.

I'll try the explode() on the set; I remember trying implode() for checkboxes from this DevShed tutorial but I forgot about it and didn't think to try it in the reverse.

How do I get the array values separately to appear in $genre and their checkboxes checked?

I have this so far:

$sql = "SELECT
*
FROM
compare,games,genres,publisher,engine
WHERE id=$id
AND compare.gameID=games.gameID
AND compare.genreID=genres.genreID
AND compare.publisherID=publisher.publisherID
AND compare.engineID=engine.engineID";

$result = mysql_query($sql);
$row = mysql_fetch_array($result);

$id = $row["id"];
$game = $row["game"];
$circa = $row["circa"];
$genreID = $row["genreID"];
$genreID = explode($genreID, ",");
$genre = $row["genre"];

and for the checkboxes I have:

<input type=text name=genre[] maxlength=40 value=<?=$genre?>><br>
<input type=checkbox name=genre[] value=action <?if($genreID=='1'){echo checked;}?>>action<br>
<input type=checkbox name=genre[] value=adventure <?if($genreID=='2'){echo checked;}?>>adventure<br>
<input type=checkbox name=genre[] value=role-playing <?if($genreID=='3'){echo checked;}?>>role-playing (RPG)
<br>
<input type=checkbox name=genre[] value=simulation <?if($genreID=='4'){echo checked;}?>>simulation:
<dd><input type=checkbox name=genre[]>city/empire-building
<dd><input type=checkbox name=genre[]>driving/racing/riding
<br>
<input type=checkbox name=genre[] value=strategy <?if($genreID=='5'){echo checked;}?>>strategy
(<input type=checkbox name=genre[]>real-time
<input type=checkbox name=genre[]>turn-based)

The simulation and strategy sub-genre checkboxes aren't set up yet.

You need to switch the arguments for the explode() function in your code:Then you'll need to read through the list of array functions and see if there's one you can apply. Possibly the in_array() function. Or run through the Control Structures documentations looking closely at while() and foreach().

I can't be too specific here b/c of the scope of the project.

Ugh, I don't understand how to do this. The PHP manual shows foreach ($array as $v/$key/$whatever) but I don't know what the hell should go in $v/$key/$whatever and it's not explained anywhere on the "foreach()" page that I can see.

And what am I supposed to do with this anyway? Will it cause $genreID to have a separate number for the checkbox checking to work? If not, how do I get it to do that? :/