In a project of mine there are three tables building, research and unit, containing buildings, researches and units respectively

All entries in these tables should be able to depend on (require) some of the others. More precisely, all three tables may depends on building and research, nothing can depend on a unit. For example, some building B3 may not be contructed until building B1, B2 and research R4 is already built/researched.

My initial thought was to 6 tables:

unit_require_building
unit_require_research
building_require_building
building_require_research
research_require_building
research_require_research

as I thought this would be the most normalized. Although, I guess this could be done with only one table which contain a type and an ID (uniquely refering to either a unit, building or research), and a similar require_type and require_ID.

At the moment I'm not sure which way to go and would appreciate some input.

The stored data and the types of data will determine what tables are needed and what normalisation can be done.

Before you start considering a database schema you need know WHAT you are storing.

Hmm, ok. Would you be able to elaborate on that if I gave more information about what tables there are and what kind of data they keep?


I was thinking somewhat like this: Lets say I want to list all buildings, including their requirements. I would then first select all rows from the building table, giving lets say X buildings. Then for the two cases:

If I add the six tables as above, each would basically have just 2 IDs, as in
unit_id + building_id
unit_id + research_id
building_id + building_id
etc.

So I'll run two queries for each building

1) join the building and building_require_building tables,
   to get the name of all required buildings
2) join the building and building_require_research tables,
   to get the name of all required researches

giving a total of 2*X queries

But in the case with just one table, it would have two types and IDs, as in
type + id + requires_type + requires_id

where the types tell if it's a unit, building or research and the id
says witch one

Then it would be a value inside the table that tells me what other table
to join with, so I wouldn't be able to join them(?).
Instead I would have to select the type and ID for all the matching
buildings/researches, and run another query for each of those,
possibly resulting in a whole lot more queries.

Number of queries would be instead be X*Y, where Y is the average
number of requirements for each building.

...

When writing this I realized that the difference probably would be minimal, but I didn't want to delete all the text I wrote I think I'll go for the first case though, as it seems simpler to me.

Ah you're thinking like a programmer there Mattias, in deciding what queries to run before you have any data to run them against.

First layout the data objects and all the object attributes you need to store.

Ha, maybe I am
Although I actually (for once) did think through and drew sketches of the layout and what data I need to store and how they'd need to interact, before I started to actually build the database or write any code. Then I've had to add some more and make changes as needed.

I'll give you the whole story, and if you feel you have enough time to kill maybe you can read through it and see if I'm on the right track or if I'm way off .
I'll highlight things that I've made tables out of.

It's a browser game where, once registered, a user will own a city. In the city there will be some resources (like wood, stone, gold, population, etc.). The user can build buildings, cunduct researches and train units (troops/soldiers), all of which cost resources. Buildings and researches can also apply some effects (like increased resource rates, unit damage or health, etc.) and the city itself can gain bonuses, which will also apply some effects. The user will also be able to send his or her units to war against other users' cities.

So I have thse base tables

city (id, name, user_id)
resource (id, name, increase) - increase sets how fast that resource increases
building (id, name, description)
research (id, name, description)
unit (id, name, description, damage, health)
effect (id, name, target) - target sets what this effect will affect, like "wood_rate" or "damage"
bonus (id, name, description)


Then I have a bunch of tables that link these together

city_resource (city_id, resource_id, value) - what resources a city has and how much
city_building (city_id, building_id, count) - which buildngs and how many a city has
city_research (city_id, research_id, level) - which researches and on which level a city has
city_unit (city_id, unit_id, count) - which units and how many a city has
city_bonus (city_id, bonus_id, expire) - which bonuses a city has and when they expire

building_resource (building_id, resource_id, value) - how much a building costs to build
building_effect (building_id, effect_id, value, unit) - effect applied by building, (value, unit) can be (5, %)
building_require_building (building_id, require_id, count) - as explained in my first post
building_require_research (building_id, research_id, level)

research_resource (research_id, resource_id, value) - research cost
research_effect (research_id, effect_id, value, unit) - effect applied by research, (value, unit) can be (5, %)
research_require_building (research_id, building_id, count) - as explained in my first post
research_require_research (research_id, require_id, level)

unit_resource (research_id, resource_id, value) - unit cost
unit_require_building (unit_id, building_id, count) - as explained in my first post
unit_require_research (unit_id, research_id, level)

bonus_effect (bonus_id, effect_id, value, unit) - effect applied by bonus, (value, unit) can be (5, %)


Then there are a few tables I havn't created yet but which I know I'll need later, like some queue table for when contructing buildings, cunducting researches and training units. And some tables to keep track of a users battles with other users.

As a former Govenor of California once said "I'll be back"!

Originally Posted by chrishirst

As a former Govenor of California once said "I'll be back"!

You need to be a little more formal in your analysis. That said, I wouldn't bother for this size of problem: City contains buildings which contain units immediately says three tables and there should be NO FK constraint from city to unit (can a city contain units directly?). Can a building require more than one other? If no, do "parent" as field in building table, else do as linking table. Similar thinking for unit/research.

Draw a pic - ignore convention, draw whatever shapes you like for tables and do lines for constraints. That tends to show up dodgy and unwanted relationships.

Yes, a city can contain units (not to be confused with resources which I *think* you did). All units (just as buildings and researches) can be trained/built/researched, which will cost resources, and will then be part of (owned by) the city. And yes, units, buildings and researches can all require more than one (or none) building and research.

What do you mean by "researches"

Been pondering this today (inbetween commisioning a two new servers and preparing to move clients).

They say there's no peace for the wicked, I would like somebody to tell me what I did wrong

I guess I can understand that it's not entirely clear. Perhaps a better name would be "technologies". It'll be things that can be researched in order to improve certain properties. For example, if you research metallurgy you'll be able build and train buildings and units that requires metal at a lower cost. Or if you research "training" (or something like that, best I could come up with) you'd be able to train units faster. Or if you research mechanics (again, best I could come up with) and build a workshop you'd be able to build siege wepaons.

Perhaps not the best examples but I think you get the idea.

EDIT: Or, if you research bowery, smithing or crafting your ranged and melee units could do more damage or get more health (due to better armor).

Ah ok, so it's basically investigations into how the available resources (all of them) can be utilised more efficiently or effectively, and/or if properties are suitable for a particular purpose.

Yes, that's pretty much it. Although sometimes they won't actually do anything themselves, just be a requirement for something else. For example, lets say "Weapon technology" wouldn't do anything, but some units still require some level of it before they can be trained.