0

I'm making a game and so far i have a members table setup with working login/register. The game is going to be a 2 player game where the user picks a country. The database structure will need to keep track of country, states or provinces in that country, population.

What I did was create a database for the game with a members table, table for the lobby/chat, and a table for the countries. Should each country have it's own database because I need to keep track of those things I said (states or provinces in that country, population etc.)

What's the most efficient way to structure the database?

RapsFan1981
  • 1,177
  • 3
  • 21
  • 61

1 Answers1

3

Create a single table for provinces, columns 'country', 'province', 'population'. Then you can query for all provinces in a country (as you'd expect select province where country = "Narnia"), or get a list of countries using a distinct clause on the same table (select distinct country from countries).

Yes, you have duplicate data for each row - ie you'd have 2 rows for ['narnia', 'north'] and ['narnia', 'south'] but that's how a lot of data is structured in DBs.

If you split your table up into 2, one for country names and another for country data, you'll still have the above structure, only instead of the country name, you'll have a country id that refers to the row in the other table. For 'complicated' structures that's the way to go, but for simple stuff like this, just put the country name in the table of all the country data.

gbjbaanb
  • 51,617
  • 12
  • 104
  • 148
  • Thanks that's exactly what I wanted to know. – RapsFan1981 Jul 31 '12 at 19:42
  • I just wanted to add how much this helped. I was thinking about this all wrong where the Country had to be the table. Thanks you for the example of "select province where country = "Narnia"". This makes an abundance of sense to have the provinces as the table then a column with country. I really have to start thinking about databases in a different way. :) – RapsFan1981 Jul 31 '12 at 21:19