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.