Any relational database gurus here (schema question)?

Recommended Posts

Lord Devil    124
Hello! I am currently designing a database schema for a somehow turn based online game. The last few days I made myself familiar with the different approaches to database design, normalization etc. ! However I am still not really satisfied with what I came up even though I guess from a technically standpoint with relational databases its correct. But maybe you can give me some advices if this DB design is right or how I could make it better or what did I miss etc. This is the basic layout of my database (I have shorted it now and removed all the different attributes of the tables, to just give an insight on the relations). Database structures PDF To explain this shortly: I have tables for the ItemData (this is just where all the base statistics of the different components are stored - I decided to put them also in the database to have an all in one place to fiddle with item attributes... - basically those will just be loaded upon server start and kept in memory). But my focus now is on the GameData tables, which can be seen in my attached pdf. My question here is: A player can buy different components like Hulls, Primary & Secondary Weapons, Engines, Shield Generatores... those GameData tables will hold all the attributes of players components infos, like damage taken, modifiers to base stats due to upgrades etc. etc. So as you can see I have created a Player Table which holds the: PlayerID AccountID (I don't go into detail here because I am pretty sure, account, subscription data etc. is stored correclty ;) ) UserName, CreationDate... additional attribs... Then there are the different components like Hulls, Primary Weapons, SecondaryWeapons, Engines, ShieldGenerators... as you can see all those tables have the PlayerID so I know which Players owns which Components. If a player now builds a ship the ship needs to reference to the components used. Since we only have 1:1 and 1:N relations here -> Hull is 1:1 because each ship can only be of one type of hull. However each ship can hold more than just 1 primary weapon, engine, secondary weapon etc. So what I basically did is putting the ShipID into the components tables to create a 1:n relation back to the ship. Now exactly thats where I am not really sure... As you see the hull is a special case, I mean I could even put the Hull table with its attribs into the Ships table because well its 1:1 anyway, but I guess its better to store it in a different table like all the other components? Or should I use a ShipID column in the Hulls table too, like in the other components tables to make it more consistent? Since my native language is German, I really hope I could explain to you what my problem is and that you can tell me if the schema is fine as it is or if I am on the wrong way? Thanks in advance, kind regards, LD UPDATE: Oh and just for your info -> in the different component tables the ShipID could also be null, because a player can have components but did not assemble them to a ship yet. In this case I guess ShipID would just be null. [Edited by - Lord Devil on May 4, 2009 6:10:24 AM]

Share on other sites
taliesinnz    231
I would do things a different way to you.

The most easy way I can think of ( not they way I would use)

I would split the database into two distinct sections. First of there would be a Definition (I prefix these with d ) section which are things which are common through every use, and then the Game section where things change.

For anything which the player can only have one of at a time,

dShips
-----
ID int auto increment primary key
Name char(20)
Cargo Space ...
Size ...
Crew ...
Cost ...
...

Hulls
-----
ID int auto increment primary Key
name char(20)
Cost ...
Hull Strength ...

Player
-------
ID int Primary Key auto increment
Ship int Foreign Key ( 1:1 to the dShips table)
Hull int Foreign Key ( 1:1 to the dHulls table)
Current Hull int

For weapons and other stackable items I would have a big table with ALL items,

dItems
------
ID int auto inc primary key
Name Char(20)
Type (1 for weapon, 2 for shield, 3 for commodity etc..)
Price int
< and other things common to every item>

Then have more specific tables for subset of items, for not relevant information don't add an entry (so don't add a entry in the weapon table if the item is a shield)

dItemsWeapon
---------
Id int primary key (1:1 to dItems table)
Max Damage float
Min Damage float
Fire Rate float
Projectile Speed float
Explosive Range float
...
...
...

Then you can have a Items ( User data table)

Items
-----
ItemID long int primary key auto increment
PlayerID int foreign key (N:1 to Players table)
ItemID int foreign key (1:1 to dItems table)
Location int ( 1 for weapon position 1, 2 for shield, 4 for hold, 5 for engine ...)
Damage int how damaged the item is)
.. and other information specific to all items

and for more specific information

ItemsCommodity
--------------
ItemID long int primary key (1:1 to Items table)
CommodityType int foregin key (1:1 to dComidities table)
Ammount float
....

and then you can get items and dItems information in 1 query

SELECT * FROM Items INNER JOIN dItems on Items.ID = dItems.ID WHERE PlayerID=1

and then check the type and then get the additional information

SELECT * FROM ItemsWeapons INNER JOIN dWeapons ON ItemWeapons.Type = dWeapons.ID WHERE ItemsWeapons.ItemID=??

You can also combine the queries together using LEFT JOINS bu this could be faster and slower depending on your data. ( Will full every column out eg. Will have the weapon columns even is the item is a shield)

Share on other sites
Anonymous P    429
It might be easier if you posted the actual SQL statements you're using to create the tables; following the pdf is nasty.

There are 3 fundamental entities you're looking at (players, ships, components).

There are 3 fundamental relationships you have to keep track of:

1) Which player (if any) owns a given ship.

2) Which player (if any) owns a given component.

3) Which ship (if any) a component is installed on.

A component can only have 1 owner, and it can only be installed in 1 ship. A ship can only have 1 owner. Syntax errors aside, that might look something like this:

CREATE TABLE player (	id	integer 	PRIMARY KEY,	name 	varchar);CREATE TABLE ship (	id 	integer 	PRIMARY KEY,	name 	varchar);CREATE TABLE component_type (	type varchar PRIMARY KEY);CREATE TABLE component (	id	integer 	PRIMARY KEY,	type 	varchar		REFERENCES component_type(type));CREATE TABLE player_ships (	player_id 	integer 	REFERENCES player(id),	ship_id		integer		REFERENCES ship(id),	UNIQUE(ship_id)); CREATE TABLE player_components (	player_id 	integer 	REFERENCES player(id),	component_id	integer		REFERENCES component(id),	UNIQUE(component_id)); 	CREATE TABLE ship_components (	ship_id 	integer 	REFERENCES ship(id),	component_id	integer		REFERENCES component(id),	UNIQUE(component_id));

The 1 ship 1 hull constraint looks special, but it's probably not: other components will also need to check constraints (eg weight, whatever) to determine whether they can be fitted to a ship or not, and the 1 hull constraint is no different (except for not varying from ship to ship). So that doesn't justify special casing.

NULLs are generally a bad idea and should be avoided: notice that with the correct schema, the need for the NULLs you were using disappeared.

The temptation to use NULL generally means you should rethink your schema.

Share on other sites
You can have a Ship with a Hull_ID field in it, just reference a default Hull in it when an instance of Ship is created.

To be honest I'd make the player upgrade existing ships so players effectively choose a hull and name it, then cannot change the Hull.

Share on other sites
Lord Devil    124
Wow thanks for the quick answers ;)

Okay so there are definately many more ways to create such a table schema than I thought. And my attempt is lacking a bit I guess.

However one thing to know about my approach -> You have to remember that in my game you can upgrade nearly every attribute of the component. That means I need to hold most attributes of the base component in the player data tables too.

E.g. PrimaryWeapon Attributes: Accuracy, Range, MinDamage, MaxDamage, HeatGeneration etc. etc. - those all can be changed by the players.

All other types like engines, secondary weapons mostly have completly different types of attributes. Thats also why I went the different approach and used each component as a whole identity - and not a item/component identity.

But maybe thats exactly my problem? To be true in my schema I also missed the tables with the players/components relations and ship/components relation.
However I tried to avoid them because in the net they said thats mostly only needed for N:M relations (the 3 tables approach). But since most of my relations are 1:1 or 1:N I thought I would be fine this way.

So basically can anyone explain me why in this case my approach was not so smart?

@Anonymous P: The special casing regarding ships <-> Hulls and Ships <-> Other components was just because well a ship can consist of only 1 hull (1:1) but can have many components (1:n) - thats why I thought they need to be treated differently in a database.

P.S. Sorry for only providing the pdf... Surely I will follow up with the sql create statements!

[Edited by - Lord Devil on May 4, 2009 9:05:05 AM]

Share on other sites
Anonymous P    429
Quote:
 However one thing to know about my approach -> You have to remember that in my game you can upgrade nearly every attribute of the component. That means I need to hold most attributes of the base component in the player data tables too.
No, it means that every component's attributes may vary. It doesn't say anything about WHERE this data should be stored.

A player knows which components he/she owns; the components themselves know which attributes they have.

Quote:
 But maybe thats exactly my problem? To be true in my schema I also missed the tables with the players/components relations and ship/components relation.However I tried to avoid them because in the net they said thats mostly only needed for N:M relations (the 3 tables approach). But since most of my relations are 1:1 or 1:N I thought I would be fine this way.
You're mixing stuff that should be expressed as a relation into the the actual object. A component is the same component no matter on what ship it's installed (or even if it's not installed at all). That's why the nulls were appearing: you need to hack around the fact that a component's owner is not really part of the component, but a relation with stuff external to it.

Quote:
 So basically can anyone explain me why in this case my approach was not so smart?

If a component need not have a relation to a ship, it's not in a 1 to 1 relation any more: that's why including playerid and shipid in a component is a bad idea: a component doesn't NEED to be owned by a ship or player to BE a component.

Encoding this false dependence into the schema means you need NULLs to hack around it.

Share on other sites
Lord Devil    124
Yeah I agree on what you say. Today I got a bit more time and tried experincing with your schema proposals.
At least now I know why I didn't want to go this route...

First of all to give some more details I am using the new EDM Model with an Entity Framework (.NET). I thought I have a big advantage in the way I created the tables because it was very easy get the data in and out from the database -> the entity model and flat tables were very similar to my code objects (classes).

On the otherside I played around with the way you and taliesinnz proposed with a very interesting result. To get the data out via the EF is always easy - regardless if I take your approach or mine. But since I see the benefits of your table schema, I will surely switch to this route.

Still I am not sure if it will be easier to expand later on by doing it this way but since the only challenge I face is to get the data from the DB out and put it into my gaming objects, the job should always get done. But surely a better db model may help in the long run for sure and thats also why I asked for help here and why I though my schema has some problems (nulls etc.).

So thanks alot and I'll provide some new examples of the new schema for you to review :)

Kind regards,
LD

Share on other sites
lightbringer    1070
I think you started your database design on the wrong end (the tech side). That PDF shows that you have trouble with determining the relationships, and no wonder - they are not explicitly accounted for in that tree structure. When designing a relational database, it's always better to start with a model first (since we don't often to data warehousing in game development, this usually means a simple ER diagram). After your ER model accounts for everything it needs to account for, you can decompose it into actual tables.

There are, of course, numerous ways to decompose the problem. My approach (and I'm not claiming it's perfect or anything, but I think it will work ok) would be different from that of Anonymous P. I think the components are going to be different enough and of few enough types that there would be little sense in trying to shoehorn them into one superclass (and it also would make your queries much more complex... suddenly you need to look up the component type then figure out which table you need to look at to get the attributes for that specific component type, and which table holds the modifiers... this is going to be some error prone code).

Like taliesinnz, I would go with two entities per component - one for the archetype (including base stats) and one for the actual instance (including damage taken and stat modifiers due to upgrades). You could encode the instance into a relationship, but it doesn't feel right to me, and they'll both be tables in the end anyway. I would also split the ship up into ShipInstance and ShipArchetype (instance n:1 archetype), the reason being that you will have many ships that will share a lot of data (model, weight, material, description) but they won't share their owner, name, registration number - these are instance specific.

I don't find the null id approach particularly disturbing. A cardinality need not be only one or many, zero is also rather common and, in fact, most ER notations support it explicitly (except for the standard Chen's notation - heh). Of course, in this case it only makes sense if the component is actually an instance and not an archetype, since you can't install an archetype anywhere. Ensuring that your instanced components are only ever installed or in inventory (null the other id) will be the responsibility of your business logic (ship management code).

On the other hand, you could stick to always having one ship id per component and adding a status column where 0 = in inventory, 1 = installed, etc. Then you also won't have a need for a player id in the component - instanced components are only ever associated with a ship. You won't be able to buy back components sold to the market if you do it this way, however, since you will be destroying them once sold.

Share on other sites
Lord Devil    124
Hello and thanks for the reply,

well I am not sure, since you don't see the pdf in its orignial source, I should explain this a bit more. I have already accounted the base stats (or archetype like you name it) in the ItemData Tables (you just dont see them in pdf, they are collapsed).

Those tables are completly flat and hold all of the base attributes for an object.

They are named:

ItemData_Ships_PrimaryWeapons
ItemData_Ships_SecondaryWeapons
ItemData_Ships_Hulls
ItemData_Ships_Engines
...
Each then has an ID, Price, Tech Level, Weight, Durability + their many corresponding special attributes depending on each type.

At the moment those infos are even stored in a differnt sql database (movable sql file db), because the client needs this information too. It stores the base stats for all items as well as their corresponding picture IDs etc. !

You approach with the items seems good too like taliesinnz... however in a later stage I definately want to make a real market where people can trade their components on a market, so the item itself doesnt get destroyed and needs to be "transferable".

I wonder if in that case the approach of the Anonymous P. would be better... so components as well as ships can be completly independent. On the other side with my or your approach, I did not see a problem too, since I could always switch PlayerIDs and ShipIDs etc. - so I could simply add a column where I flag them if they are being sold on a market or not...

Also as you see my ItemData Tables are completly flat so its very easy to work with them. I wonder if I should keep the ItemData as I have them now (flat tables for each components) and just optimize the server side DB schema. Do you think it would make sense to also implement the ItemData Tables in the same Server DB so I could make "true" references between the instanced (GameData Tables) and the archetypes (ItemData Tables)? But I don't think thats necessary. A special ID in the instanced tables should be enough to know where to get the base stats from, so I only keep the modified upgraded, damaged etc. stats in the instanced tables.

Well well... but thats basically why I made this post here, there are so many possibilities and I really want to make sure to go with a good route and not have to change anything major later on. The game should be highly expandable in the future too.

Thanks again for all the feedback, much appreciated.

P.S. BTW Greetings from Austria ;)

[Edited by - Lord Devil on May 6, 2009 5:43:06 AM]

Share on other sites
lightbringer    1070
Quote:
 Original post by Lord Devilwell I am not sure, since you don't see the pdf in its orignial source, I should explain this a bit more. I have already accounted the base stats (or archetype like you name it) in the ItemData Tables (you just dont see them in pdf, they are collapsed).

Sounds good. I'd still start the design with an ERD though :)

Quote:
 Original post by Lord DevilAt the moment those infos are even stored in a differnt sql database (movable sql file db), because the client needs this information too. It stores the base stats for all items as well as their corresponding picture IDs etc. !

As long as the client doesn't do any authoritative calculations with those base stats... you can't trust the client.

Quote:
 Original post by Lord DevilYou approach with the items seems good too like taliesinnz... however in a later stage I definately want to make a real market where people can trade their components on a market, so the item itself doesnt get destroyed and needs to be "transferable".

One way you could go is to define an Entity "Inventory" which could be either a ship's cargo hold or a market's or auction house's warehouse.

Quote:
 Original post by Lord DevilAlso as you see my ItemData Tables are completly flat so its very easy to work with them. I wonder if I should keep the ItemData as I have them now (flat tables for each components) and just optimize the server side DB schema.

Don't keep tables flat for the sake of being flat, though, or you'll just end up with a spreadsheet. I'm not sure what you mean by "optimizing the server side schema". Aren't all of the tables server-side, apart from also having a copy locally for some of the game data?

Quote:
 Original post by Lord DevilDo you think it would make sense to also implement the ItemData Tables in the same Server DB so I could make "true" references between the instanced (GameData Tables) and the archetypes (ItemData Tables)? But I don't think thats necessary. A special ID in the instanced tables should be enough to know where to get the base stats from, so I only keep the modified upgraded, damaged etc. stats in the instanced tables.

I would put everything into the same database, but it's up to you. As long as you can make an inner join between ItemData and GameData, it's probably ok to separate them. I haven't done any client-server database-based games, so I can't make a good recommendation on this.

Quote:
 Original post by Lord DevilP.S. BTW Greetings from Austria ;)

Grüß Gott!

Share on other sites
Lord Devil    124
Quote:

Quote:
 Original post by Lord DevilAt the moment those infos are even stored in a differnt sql database (movable sql file db), because the client needs this information too. It stores the base stats for all items as well as their corresponding picture IDs etc. !

As long as the client doesn't do any authoritative calculations with those base stats... you can't trust the client.

Hehe nono, all calculations etc. are done server side - I know - never trust a client :) ! What I mean with different db is, that the Base Stats of the hulls, components etc. are shared across the client & server and thus I am using a sql file db (which has only flat tables - having all the base stats, picture information etc. in it).

Quote:

Quote:
 Original post by Lord DevilYou approach with the items seems good too like taliesinnz... however in a later stage I definately want to make a real market where people can trade their components on a market, so the item itself doesnt get destroyed and needs to be "transferable".

One way you could go is to define an Entity "Inventory" which could be either a ship's cargo hold or a market's or auction house's warehouse.

What you mean with creating an entity "Inventory"?

Quote:

Quote:
 Original post by Lord DevilAlso as you see my ItemData Tables are completly flat so its very easy to work with them. I wonder if I should keep the ItemData as I have them now (flat tables for each components) and just optimize the server side DB schema.

Don't keep tables flat for the sake of being flat, though, or you'll just end up with a spreadsheet. I'm not sure what you mean by "optimizing the server side schema". Aren't all of the tables server-side, apart from also having a copy locally for some of the game data?

Well not exactly... I am doing a very unusal approach... :)

Serverside:
A SQL Server: this one holds the GameData Table as mentioned above.
Then there is a SQL db file which holds all the ItemData Tables(Base stats for all the components - that are flat tables for each component holding all stats)

Clientside:
The client has the same SQL db file too (with all the base itemdata - archetypes)
Then the client also gets the ongoing actions -> this will be transfered in another SQL db file (keeping the ongoing actions).
So the client action sql db file will be changed depending on the actions on the client and sent back to the server.

This may sound bad if you keep performance in mind, but since my tasks are absolutely not time critial due to the concept of the game, this is no problem - also the size of the db which will be sent back & forth between client/server is very small :)

So in the end we have 3 DBs

1) SQL Server (only on server - keeping all the game related informations of players, ongoing actions etc.)
2) SQL ItemData DB file (on server and client - keeping all the base stats of components)
3) SQL Action DB file (this file will be changed on server and client and sent back&forth with the ongoing client game data)

Surely the server needs to verify the action db of the client upon receiving to ensure it didnt get altered, includes invalid actions etc.

I hope its now a bit more clear ;)

Kind regards,
LD

Share on other sites
lightbringer    1070
Quote:
 Original post by Lord DevilWhat you mean with creating an entity "Inventory"?

An entity in the sense of an entity-relationship model, of course. These usually result in one table in the physical database model.

Quote:
 Original post by Lord DevilSo in the end we have 3 DBs1) SQL Server (only on server - keeping all the game related informations of players, ongoing actions etc.)2) SQL ItemData DB file (on server and client - keeping all the base stats of components)3) SQL Action DB file (this file will be changed on server and client and sent back&forth with the ongoing client game data)Surely the server needs to verify the action db of the client upon receiving to ensure it didnt get altered, includes invalid actions etc.

If you ask me, the whole action db is a patently bad idea. Firstly, there's the performance and security issues you already mentioned. Both client and server performance will obviously suffer since you are making far more SQL calls than necessary.

Security will be a big one since you are essentially saying "here is a copy of the database from last turn, here is a copy of the database from this turn, let's compare them and verify that any changes between them are valid with respect to our rules". This is going to be extremely difficult to implement (and it will slow down your server), and offers no benefits whatsoever. It's much easier to send the actual player input to the server and verify it.

The kind of information you are going to keep in there is unlikely to be nicely representable with a relational database. Use a database for permanent or semi-permanent data only.

Share on other sites
Lord Devil    124
I guess you got me wrong... and yeah you would be right if it would be a normal game and I wouldnt know those issues ;)

But since the whole game concept is a bit weired to explain, it actually makes sense here.

All I can say is this, I don't have much database calls because the client just gets the data out of the db file and stores them in an object and everything is handled from there. Just think of as the "turn" file from the player is a sql db file instead of a serialized object container. So instead of sending an object I send the db file which just includes the actions of the player nothing more.

Regarding the security the db will also have a usual encryption with dynamic keys, so it would be around the same as sending/receiving encrypted serialized object via tcp.

The big advantage is that the turn db file is now a very easy to transform, handle and exptendable container, which does most of the stuff automatically. I can send it/receive it/encrypt it etc. without having to worry about object serliazation/deserialization, extendable objects etc. ! It makes a very easy to manage and a very easy seperation between server/client communication.

Remember the game is mostly turn based (however its dynamic turn based - special actions can come inbetween turns too) - still I only have to send packets with some player action and they are not time critical at all :)

Hard to explain for me in english, but I hope you got about what I wanted to say...

Create an account

Register a new account