Any relational database gurus here (schema question)?
Author
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]
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)
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)
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:
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.
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.
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.
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.
Author
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]
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]
Quote:No, it means that every component's attributes may vary. It doesn't say anything about WHERE this data should be stored.
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.
A player knows which components he/she owns; the components themselves know which attributes they have.
Quote: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.
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.
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.
Author
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
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
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.
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.
Author
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]
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]
Quote:Original post by Lord Devil
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).
Sounds good. I'd still start the design with an ERD though :)
Quote:Original post by Lord Devil
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. !
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 Devil
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".
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 Devil
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.
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 Devil
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.
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 Devil
P.S. BTW Greetings from Austria ;)
Grüß Gott!
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
