Knowing what data would be essential to store and how (Game writing, storage)

[Aventro]

I am not very experienced with database storage as I would like to be.

For example, let's say users have different buildings, i.e. farms, barracks etc. Each building has a level. Now, let's say a user would like to upgrade an building and based on an condition is allowed to upgrade x buildings at a time (upgrading a building would obviously take certain time, like one hour, our two based on what level the building currently is on).

What would I need to store, how would the table be structured? It should also be noted that it may not only be buildings that may be upgrades, let's assume even some other resource/entity could be upgraded such as troops.

Also, how would I store the conditions required (let's say to upgrade Building A to level 2, I need Building B in level 1). ( I am well aware this could be hard-coded, If, let's say I had a class for each building, but is that essential, or is data storing in a database, or just to make it simpler, we don't use classes for each building (which may be the way to go...)

Not much information provided, but let's start off from here, you can make your own assumptions if needed, we're not talking about a current game with a current architecture, but just generally - as I'd like to plan out my game architecture before actually writing it.

[sniko]

• Ensure you normalize your database
  

 

Here's a quick mockup I did

CREATE TABLE IF NOT EXISTS `buildings` (
 `id` MEDIUMINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `name` VARCHAR(25) NOT NULL,
 `upgrade_condition` TEXT(1000),
 `price` MEDIUMINT(5) NOT NULL
);

CREATE TABLE IF NOT EXISTS `user_buildings` (
 `user_id` MEDIUMINT(5) NOT NULL,
 `building_id` MEDIUMINT(5) NOT NULL,
 `level` SMALLINT(3) NOT NULL DEFAULT 1,
 `upgrade_in` INT(10) NOT NULL DEFAULT 0
);

INSERT INTO `buildings` (`name`,`upgrade_condition`,`price`) 
VALUES ('Farm', 'level > 4', 1000),
      ('Barracks', 'location == 1', 5000);

INSERT INTO `user_buildings` (`user_id`,`building_id`,`level`)
VALUES (1,1,1),
      (1,2,1);

 

Let's have a look...

• We've got two tables, one to hold valuable building data, one to hold the data on the buildings a user owns, linked with the foreign key; `building_id` = `id`
  
• I've took the liberty to insert some data
  

Now, let's create an SQL statement to get all the buildings user_id(1) has;

/* 
Get user buildings
* Use your desired language to manipulate this query, and the data it returns
*/
SELECT u.`building_id`,u.`level`,u.`upgrade_in`,
      b.`name`,b.`upgrade_condition`
FROM `user_buildings` u
LEFT JOIN `buildings` b
ON u.`building_id` = b.`id`
WHERE (u.`user_id`=1)

 

SQLFiddle

:)

[Spudinski]

"My Solution" for this problem.

You'll need at least three tables, buildings, a linker table, and a requirements mapping table.

buildings
=============
id
title
level
...

player_buildings
============
player_id
building_id

buildings_mapper
============
id
primary_id
required_id
requirements_id

buildings_mapper_requirements
============
id
level
... (all possible requirements)

 

The first two tables are very basic, the third and forth one is where the magic will happen.

`buildings_mapper` will have records(requirements) that has an ID, a primary_id which will be a building, a required_id which is also something from within this table, and a requirements_id.

The last table and `requirements_id` will go hand in hand, this table will have a one-to-many relationship with buildings_mapper.

(.. I'll write a sample query/code later today, perhaps ..)

As you can see, this will make requirements manageable. What you could do as well to find out more about dependency management, is to read up on popular package managers.

[Aventro]

"My Solution" for this problem.

You'll need at least three tables, buildings, a linker table, and a requirements mapping table.

buildings
=============
id
title
level
...

player_buildings
============
player_id
building_id

buildings_mapper
============
id
primary_id
required_id
requirements_id

buildings_mapper_requirements
============
id
level
... (all possible requirements)

 

The first two tables are very basic, the third and forth one is where the magic will happen.

`buildings_mapper` will have records(requirements) that has an ID, a primary_id which will be a building, a required_id which is also something from within this table, and a requirements_id.

The last table and `requirements_id` will go hand in hand, this table will have a one-to-many relationship with buildings_mapper.

(.. I'll write a sample query/code later today, perhaps ..)

As you can see, this will make requirements manageable. What you could do as well to find out more about dependency management, is to read up on popular package managers.

I can see why requirements_id exists, but why required_it?

And, how would I store the actual fact that the user is actually upgrading to level 2 at this moment (say from 8 to 9 it takes to go to level to in building a)

[Spudinski]

Since I've just gotten home, and have some spare time, I'll elaborate.

The `buildings` table

This table could also be a one-to-many relationship table, for things like building type, defensive strength, population overhead and so forth.

The main goal here is to represent it as the base table from which information about buildings are gathered. Ideally, no specific requirement logic will be kept here.

`player_buildings`

This table isn't really needed needed for a functional state, but is good practice none-the-less as it provides additional flexibility and creates a many-to-many relationship between players and buildings. This table would also be index along with the indexed columns it links to, which provides a very good performance gain over simply linking simple one-to-one relationships.

`buildings_mapper`

This table provides the actual logic behind the dependency management. It will finally map to a building, a requirement and possibly a parent dependency.

The columns explained:

id: This is to allow further performance gains using it an index. But, the main purpose of this column will be to create a parent dependency that children can reference to.
primary_id: this would be the building's id, also indexed. It's used to locate the a dependency from the buildings table.
required_id: As mentioned with the `id` column, it's used to map further dependencies.
requirements_id: This column is used to map to a single "requirement"/dependency as defined in the following table.

 

`buildings_mapper_requirements`

This will be the final requirements, it could possibly also be a linker table to other things like population, gold and so forth: it depends on how complex you want to make it.

Building attributes would be columns here for simplicity, although it may not be as flexible.

 

As for how to actually store the result? Ideally you would have a table defining attributes of each building, which also includes a level.

Or - you can rework the `buildings` table to be a "levels" table and map requirements to levels. My proposed solution does not take into account the unique level, only unique buildings.