database design questions

[oknow]

ok I am pretty new to mysql/php and was going to make a movie based site, but now I am making a video game database for fun/practice, because it is of far greater interest to me

I have some of it sorted out, but I'm not sure how to handle certain parts of it.

I will be having a table for each system, so I will use one example here for the Super Nintendo how I had it before I thought about these problems:

CREATE TABLE supernesgames (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
game VARCHAR(255),
developerid INT,
publisherid INT,
year VARCHAR(4),
regionid INT,
) DEFAULT CHARACTER SET utf8;

will also have a developers table, a publishers table, a genre table, and a regions table obviously, for linking to each game system's table.

but my problem is, I'm not sure how to handle regions, developers, publishers, month of release and genre properly. for each game these things may (or may not) vary between the multiple regions. I'm probably going to just start off with 3 regions 'NA, JP, PAL'. might add more later.

for example the 'A.S.P.: Air Strike Patrol' game was released in NA in January. but it was called 'Desert Fighter' in PAL and had a different publisher.

yet there are many games that are called the same thing in BOTH of those regions. if someone were to set their region to PAL on my site and look for a list of all supernesgames, it would need to show them a list that included not only game titles that are the named the same in all regions, but for SOME of the games it would need to show the alternate titles/release years/alternate publishers instead for the region the user has selected, if applicable.

that is probably worded terribly so I can try and elaborate if necessary..

would love some advice and/or examples on how to handle these things so I can get my database design down and really start learning the php side of things.

[Dan Grossman]

First off, don't create a table for each system. The system is just an attribute of the game. It should be a column in a table, like all the other attributes of the game.

As for the rest of the fields -- release date, publisher, region, etc. -- that's another table. You have many rows in that category that correspond to releases of the same game in the games table. If it's released for two systems, it gets two records, one for each system. If it's released with a different name in different regions, then that game has a different name in two rows in the releases table, but both link to the same game ID so you can tell they're the same game.

CREATE TABLE games (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  grenre_id INT
);
#include in the games table any other attributes that 
#never vary by release/region/system

CREATE TABLE game_releases (
  game_id INT,
  system VARCHAR(25),
  region CHAR(3),
  developer_id INT,
  publisher_id INT,
  published_date DATETIME,
  name VARCHAR(255)
);

CREATE TABLE developers...
CREATE TABLE publishers...

That look about right for your site?

To satisfy your user's search for PAL games on the SNES, you only need to look in the releases table, and get back all rows where system = 'SNES' and region = 'PAL'. It doesn't matter that a game is available as a different name in another region, the release in the region the user searched will be found. And since you have the game_id, you can show them the list of all releases of all the matching games if you want.

[oknow]

thank you that makes a lot of sense but I am still a little lost, I am going to try that out and also ask a couple more questions.

I was trying to avoid multiple entries of the same game, but I guess it's ok to do that? awesome.

so my insert might look like this:

INSERT INTO game_releases (game_id, system, region, name) VALUES
('1', 'SNES', 'US', '3 Ninjas Kick Back'),
('2', 'SNES', 'US', '5-in-1 Super Mario All-Stars/Super Mario World'),
('2', 'SNES', 'EU', '5-in-1 Super Mario All-Stars/Super Mario World'),
('2', 'SNES', 'AU', '5-in-1 Super Mario All-Stars/Super Mario World'),

and it's totally fine to have multiple entries, one for each region, even if only the region differs? or am I supposed to be putting game names in the separate 'games' table, and only alternate names in the game_releases table?

would it be smart to make a systems table too and identify them by an INT?

[Dan Grossman]

You can't put the name in the games table, because that name might only correspond to a specific region. Your INSERT examples are the way to go. It's totally fine to have multiple entries if only the region differs. You should be worried you've produced a bad design when there are NO differences between two rows -- such as your proposed one-table-per-system where you'd have exact duplicate rows in multiple tables for games released on multiple systems.

You would only make a systems table if you have some other data to associate with a system. If you're going to do nothing with it but store the system name, then there's no reason for a separate table.

[oknow]

Thank you so much, it all makes a lot more sense to me now.. I'm excited. I am only confused about one thing, and it's probably a silly newbie thing, but why do I also need a games table if all the game info/ids are going into the game_releases table?

Could I just have one table:

CREATE TABLE games (
game_id INT,
system VARCHAR(25),
region CHAR(3),
genre_id INT,
developer_id INT,
publisher_id INT,
published_date DATETIME,
name VARCHAR(255)
);

[oknow]

wait, that's so that I can link universal things like genre (I think that will be the only universal thing) ONCE to 4 entries instead of having it in 4 entries

ie:

INSERT INTO games (id, genre_id) VALUES
('1', '7')

then that would apply to all 4 (for example) entries of one game, without having to put the genre id on all 4 rows, because the game_id is the same.. right?

[Dan Grossman]

Right. Create a column in the games table, not the releases table, for any attribute that applies to the game and isn't specific to a release of that game. Genre was the only one you listed, but you might come up with more in the future.

[oknow]

ok thank you so much, I get it!

really appreciate your time.

[oknow]

one final thing (I promise) for now, is DATETIME the best thing for my needs if I am only going to be listing month and year for each game's release date in their respective regions ie 'November 1994'

definitely not going to narrow down to day. I have no experience with DATETIME though.

[Dan Grossman]

I would, just because it'll make querying the database easier, since all the date/time functions will be available. You can make it the first of the month or something when you insert the dates. Just only display the month and year in your website.

[oknow]

ok that makes total sense. thank you once again!