Correctly Doing this - Please help

[apw]

Hello

 

  I'm working on a game that players research/upgrade buildings.  So far I've created a new

table in the database called buildings and filled this database with fields such as:

 

id

charname (characters name)

buildingname

currentlevel (current level player has upgraded to)

nextlevel (next level for the building upgrade)

1_level_cottage (how much time takes to upgrade - 1.minute)

2_level_cottagen (these are stamped in the database as datetime

3_level_cottage

4_level_cottage

5_level_cottage

 

What I'm having an issue is with the following:

 

1. Instead of making a table called buildings, would it be better to create individual

tables for EACH building or keep everything into one table?

 

2. I'm not sure how to go about doing the times for each upgrade. For example, for

player to reach level-1 upgrade (after clicking the UPGRADE button) it would take

the player 1.minute of real-time before the upgrade happens.  I know I would need

to stamp the exact time the player clicked the UPGRADE button into the database

but how would I add say 1.minute or 60.minutes or even 3.hours to the exact time

the player clicked the UPGRADE button?

 

Thanks for your help!

[blueman378]

Hi,

Im no expert but heres my suggestion,

 

create your users table,

 

create a table with the general information about ALL the buildings

create a table for each building

when u user builds the building add a row to the third table

UID - users id

BLVL - building level ( set to 0 or 1 by default)

UPCLK - when the user clicked upgrade

 

[ignace]

Before you start building something you need to think things trough. An important part is your database design (atleast if your application is going to use one) if you already fail there then the development will mostly do to as your queries will become or to complex to maintain or to redundant. Unfortunatly their aren't any tutorials out there detailing how you go from concept to realisation and if their are then they most likely are written for those already familiar with the terms ruling you out as you don't understand what they are talking about.

 

I have done the database design for you:

 

# the buildings table holds all buildings in your game (if you use 'races' then add a _races_id)
CREATE TABLE buildings (
  buildings_id INTEGER NOT NULL AUTO_INCREMENT,
  buildings_name VARCHAR(32),
  PRIMARY KEY (buildings_id)
);

INSERT INTO buildings VALUES (1, 'Town Hall'), (2, 'Cottage');

# the buildings_upgrades table holds specifications about the upgrade cost of a building
# and other information like to which level the building is upgraded (it's up to your programming to make sure the user isn't able to upgrade
# from level 1 to level 20 altough 'money' may be a problem to do so)
CREATE TABLE buildings_upgrades (
  buildings_upgrades_id INTEGER NOT NULL AUTO_INCREMENT,
  buildings_upgrades_buildings_id INTEGER,
  buildings_upgrades_to_level INTEGER,
  buildings_upgrades_gold_cost FLOAT,
  buildings_upgrades_lumber_cost FLOAT,
  buildings_upgrades_stone_cost FLOAT,
  ..
  KEY fk_buildings_upgrades_buildings_id (buildings_upgrades_buildings_id),
  PRIMARY KEY (buildings_upgrades_id)
);

INSERT INTO buildings_upgrades VALUES
# it costs 1000 gold, 1000 lumber and 1000 stone to create a level 1 town hall
(1, 1, 1000, 1000, 1000, ..),
# it costs 2000 gold, 500 lumber and 1500 stone to create a level 1 cottage
(2, 2, 2000, 500, 1500, ..);

# the buildings_upgrades_dependencies table is meant for if a upgrade relies on the present of other upgrades
CREATE TABLE buildings_upgrades_dependencies (
  buildings_upgrades_dependencies_buildings_upgrades_id INTEGER NOT NULL, # the upgrade building
  buildings_upgrades_dependencies_buildings_upgrades_depency_id INTEGER NOT NULL, # the upgrade building on which buildings_upgrades_id relies
  PRIMARY KEY (.._buildings_upgrades_id, .._buildings_upgrades_dependency_id)
);

INSERT INTO buildings_upgrades_dependencies VALUES
# to build a town hall level 1 you need a level 1 cottage
(1, 1);

# the players_buildings table holds all buildings and it's current level the player has purchased
CREATE TABLE players_buildings (
  players_buildings_players_id INTEGER NOT NULL,
  players_buildings_buildings_id INTEGER NOT NULL,
  players_buildings_level INTEGER,
  PRIMARY KEY (players_buildings_players_id, players_buildings_buildings_id)
);

INSERT INTO players_buildings VALUES
# player #1 has a cottage and a town hall both at level 1
(1, 2, 1), (1, 1, 1);

# if the user upgrades you'd perform the following query:
#UPDATE players_buildings SET players_buildings_level = $to_level WHERE players_buildings_players_id = $id AND players_buildings_buildings_id = $buildings_id

[ignace]

I have updated it a bit:

 

CREATE TABLE building (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(16),
  PRIMARY KEY (id)
);

CREATE TABLE player (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(16),
  PRIMARY KEY (id)
);

CREATE TABLE research (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(16),
  PRIMARY KEY (id)
);

CREATE TABLE build_specification (
  id INTEGER NOT NULL AUTO_INCREMENT,
  building_id INTEGER NOT NULL, # REFERENCES building (id)
  upgrade_level INTEGER,
  gold_cost INTEGER,
  lumber_cost INTEGER,
  stone_cost INTEGER,
  population_cost INTEGER,
  KEY fk_building_id (building_id),
  PRIMARY KEY (id)
);

CREATE TABLE player_has_build (
  player_id INTEGER NOT NULL, # REFERENCES player (id)
  build_specification_id INTEGER NOT NULL, # REFERENCES build_specification (id)
  PRIMARY KEY (player_id, build_specification_id)
);

CREATE TABLE build_specification_dependency (
  build_specification_id INTEGER NOT NULL, # REFERENCES build_specification (id)
  build_specification_id_dependency INTEGER NOT NULL, # REFERENCES build_specification (id)
  PRIMARY KEY (build_specification_id, build_specification_id_dependency)
);

CREATE TABLE research_specification (
  id INTEGER NOT NULL AUTO_INCREMENT,
  research_id INTEGER NOT NULL, # REFERENCES research (id)
  upgrade_level INTEGER,
  gold_cost INTEGER,
  lumber_cost INTEGER,
  stone_cost INTEGER,
  population_cost INTEGER,
  KEY fk_research_id (research_id),
  PRIMARY KEY (id)
);

CREATE TABLE player_has_research (
  player_id INTEGER NOT NULL, # REFERENCES player (id)
  research_specification_id INTEGER NOT NULL, # REFERENCES research_specification (id)
  PRIMARY KEY (player_id, research_specification_id)
);

CREATE TABLE research_specification_dependency (
  research_specification_id INTEGER NOT NULL, # REFERENCES research_specification (id)
  research_specification_id_dependency INTEGER NOT NULL, # REFERENCES research_specification (id)
  PRIMARY KEY (research_specification_id, research_specification_id_dependency)
);

 

There are 3 main tables: Building, Player and Research, these hold the very specifics of each (name, image, ..). The tables _specification defines the cost on the players different resources. The tables _has_ defines which specifications the player already 'purchased' and the table _dependency defines which spefication holds a dependency on the presence of some other specification (that's why you need to know which specifications the player has already 'purchased').

 

Note: As you'll notice research and building are quite similar you may want to create one table for them and using a type to differentiate between them.

[apw]

 

With the database workings posted, would it still be possible to keep the orginial

idea of using 'real-time' for buildings?

 

For player to reach level-1 cottage status, upon clicking the UPGRADE button

would take 1-minute.30seconds, level-2 would  take 4.minutes etc?

 

Thanks again

[ignace]

Yes