MySQL Relational Database

[AltarofScience]

So I am making a game where there are multiple players and each player has many colonies and each colony can build any buildings a given user has access to. I need a way to store the data on what colony has what buildings. The possible buildings to build can change also.

Apparently using a spreadsheet style is bad programming and I was told to make a relational structure like this:
1. Users:
id pk
username text

2. Blueprints:
bpid pk
bpname text

3. UsersBlueprints:
id fk
bpid fk

4. Colonies
idcol pk
id fk
name varchar

5. Building Types
btid
name text

6. Buildings
bid pk
idcol fk
name text
btid fk
bpid fk

I get how the fk relations work and whatnot, but I don't understand where it stores the number of buildings on each colony.

[Chris]

Sigh, don't let them brainwash you. You should not do a perfect relational DB. That's wrong. Make it so it works, not so it is "proper".

Make each building a field in the colony table (building1, building2, etc). Even if you do 50 buildings total it is still faster (and more convenient) than a spearate table.

[ajacksified]

I'd disagree; it's actually less convenient having 50 columns in your main table. There's a lot of maintenance involved in building sql to, for example, show all of your buildings, or to find the first empty one. It's less performant and less maintainable.

I agree on the point where you don't always have to maintain a perfectly normalized database - but do start with one, and revert where it makes  things too complicated, but do so sparingly.

The number of buildings for each colony is easy to check - just get a count of the buildings where the colonyid is your colony.

(I'd also suggest looking at non-relational databases like MongoDB for extra credit.)

[AltarofScience]

I'd disagree; it's actually less convenient having 50 columns in your main table. There's a lot of maintenance involved in building sql to, for example, show all of your buildings, or to find the first empty one. It's less performant and less maintainable.

I agree on the point where you don't always have to maintain a perfectly normalized database - but do start with one, and revert where it makes  things too complicated, but do so sparingly.

The number of buildings for each colony is easy to check - just get a count of the buildings where the colonyid is your colony.

(I'd also suggest looking at non-relational databases like MongoDB for extra credit.)

No, but there are up to 50 players in a round, and players can get from 0-100 colonies, theoretically you could have more colonies if I made the universe larger. And players create blueprints for things, like ship hulls, ship components, and buildings. Each blueprint is unique. I know how to count the total number of buildings, but I need to be able to store and retrieve the amount of each building a player has built in a colony, 15 of this 27 of that 34 of another, and that is just one building type, say iron mines. So you would need from 0-infinity columns for just iron mines, and there are 10 types of mines, plus farms, and then there are like 8 other buildings, and then there are ship hulls, like 15, and ship components, like 9.

[ajacksified]

No, but there are up to 50 players in a round, and players can get from 0-100 colonies, theoretically you could have more colonies if I made the universe larger. And players create blueprints for things, like ship hulls, ship components, and buildings. Each blueprint is unique. I know how to count the total number of buildings, but I need to be able to store and retrieve the amount of each building a player has built in a colony, 15 of this 27 of that 34 of another, and that is just one building type, say iron mines. So you would need from 0-infinity columns for just iron mines, and there are 10 types of mines, plus farms, and then there are like 8 other buildings, and then there are ship hulls, like 15, and ship components, like 9.

I might be missing something - but why do you need to store / retrieve the amount of building each player has built in a colony? Can't you get a count where the player id = X and the building type = Y?

[BlackScorp]

each player has many colonies and each colony can build any buildings a given user has access to.

users
id (PK)|username|password|email| so on...

colonys
id (PK) | name | positionX|positionY | so on..

buildings
id (PK ) | name |

user_colonys
user_id (FK)| colony_id(FK) | special stuffs which can has only colony and users
---------(PK)-------------   combine Foreign keys to a primary key so on InnoDB engine your users cannot have the same colony twice
colony_buildings
colony_id(fk) | building_id (fk) | other stuffs
-------------(PK)-------------  innoDB dont allow that same building could be build on a colony

with methods like count(building_id) from colony_buildings you know the amount of buildings on a colony. if you want to put a different level to a building so add a row like level to colony_buildings so you can say then :"a colony has a building with this level"

hope i could help you

Greetings blackscorp

able to retrieve the amount of each building a player has built in a colony
lets say user id is 1337

SELECT cb.colony_id as colony, count(cb.building_id) as amount_of_buildings,u.id
FROM users u INNER JOIN user_colonys us ON(u.id = us.user_id)
INNER JOIN colonys c ON (us.colony_id = c.id)
INNER JOIN colony_buildings cb ON (c.id = cb.colony_id)
WHERE u.id = 1337
GROUP BY cb.colony_id

so now you know how many buildings your user has on each colony

[AltarofScience]

that sounds like something for ogame.

the buildings in my colonies do not have levels. their stats are static. still i think i figured it out.

[BlackScorp]

that sounds like something for ogame.

the buildings in my colonies do not have levels. their stats are static. still i think i figured it out.

i just wanted to say that you have to store your stuffs in those table where the informations are needed. you wanted to store the amount of buildings on a colony? i would store it in colony table, because every colony could have another amount. but i would calculate this value(not store in a row)(if it isnt fixed) else you have to update it over cronjob or other stuffs and this is not a good way