Skip to main content
gamedev.net
🔒 Locked

[web] Database layout (ER-Model)

Started by Basiror 5 replies 1.5k views
Original Post
Basiror
Basiror
Hi I am currently working on the database layout of a realtime-strategy browsergame engine. OpenMMOEngine ER-Model I would like to get some feedback on the layout and possible changes to reduce complexity and to make coding the stored procedures easier. A little description of the ER-Model: player_login: - holds the login data... - account specific data in general player: - holds game specific data - capital, score, ... alliance: - stores alliance specific data alliance_leader: - holds some flags that represent the members rights within the alliance such as chmod <rwx,rwx,rwx> population: - each player is associated with a native race, the first who claims a province will make is race the native race of a province. - if a province is occupied by a another player, the race of this player will be added to the native inhapitants, thus you can have an arbitrary number of races per province province: - stored 5 resource types (the use of these resources is optional, this is hardcoded for the sake of performance, allowing a 1,* relation between provinces and resources would cause a lot of trouble and add some complexity - holds its coordinates(x,y,z) e.g.: <continent,(x,y) coordinates on the map> building_type: - costs of 5 resource types - gatherrate of 5 resource types - time == construction time building: - 1,* relationship of province to building, which means you can have zero or more different buildings in your province where each buildingtype has amount many buildings unit_type: - describes costs and other attributes of units unit_stationed: - holds the number of units of a certain type that is stationed in one provinces and owned by owner - military_status describes their attitude upon attack of the provinces, thus allowing foreign players to support you in defense missions: - describe everything related to a mission - have 1,+ relationship to unit_on_mission unit_on_mission: - hold mission id,strength,unittypeid - (maybe add owner, so you can recover units that may be lost due to bugs?) Thats pretty much the entire database layout I have so far. There are things missing, like the diplomacy system and the communication system..., but those will be pretty similar to what I have above, thus I can add them once this here has been worked out Thx in advance
http://www.8ung.at/basiror/theironcross.html
Cygnus_X
Cygnus_X
Right off the bat, I'd question why you have the building types stored in the database. If I'm reading it correctly, those values will never change, which means it should probably be hard-coded into your code.

The rest of it looks fine. Just make sure you leave yourself an option to ban/suspend user accounts. You'll find that a lot of your database development will spawn out of necessity rather than pre-planning.
Basiror
Basiror
Here is an updated image of the ER-Model

The reason I have put the unit_types and building_types into the database is because we want to allow the admins to add new units on the fly, but you are actually right, one could move this into the code which would certainly speed it up.
http://www.8ung.at/basiror/theironcross.html
Bracket
Bracket
There's nothing wrong with storing what are effectively constants in the database; it makes changing them a lot easier (i.e. not requiring whomever does the change to get into the code). You can eliminate the speed hit by loading them once at start-up and keeping them around in memory. I do this a lot in projects (either database or config files), and it helps out a lot when you quickly need to either add something or alter a value without changing underlying code. It also forces you to write nice and generic handlers for building types, which should improve code readability quite a bit.
Basiror
Basiror
How do you load them into memory at startup?

Since information such as unit_type and building_type is viewed all the time when users browse the construction page, those few kb of data will be in cache anyways, I d say.

On advantage is that you can simply add additional columns here and there and leave them unused (set them to zero) and on demand you integrate it into the game

e.g.: you have several combat types(ground attack/defense, air attack/defense, bombardment .....) you can use one and the same piece of code to handle all unit_types which results in less conditionals(IF ELSEIF ELSE) and should speedup the database

Makes the SQL instructions easier to read too.

http://www.8ung.at/basiror/theironcross.html
Sander
Sander
Quote:
Original post by Basiror
How do you load them into memory at startup?


Instead of doing a dozen or so queries on your constants/configuration table, just load the entire table in a PHP array at the start of your script and use that instead. The table should be quite small anyway. If you're defining hunderds or thousands of constants then it's time to look back at your design and see what's wrong.

Basiror
Basiror
So I normalized some of the tables a little bit and marked the keys.
And you are right the tables that define constants are pretty small in comparsion to the rest of the database

Note:
In the new version of the ER-model is a little mistake regarding the effecttype<-->governmenttype relation, in fact each governmenttype shall have *effecttypes.
each effect a player receives is stored in a table grouped by categories for fast lookup and the effects values are multiplied amount times.
This whole effect system shall make the database customizeable a lot.
Updated ER-Model
http://www.8ung.at/basiror/theironcross.html

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.