Database Table Design for a Browser Game's Unit Storage

[shintei]

I have a database design question. I have a browser game I am making for my own enjoyment, and right now am trying to determine how to save armies for players.

Right now I use a table set up where each column represents a unit type with the user’s identifier as a key. (Ex.  UserID | Bow | Spear | etc... )

With this approach, I have only one row per user which helps keep the table smaller but it does add some other issues to the code. For one, if I wanted to add a new unit type, it would require me to add it manually to this table as a new column.

 

I have also considered using serialization on an array and store the entire armys value into a single column (Ex. UserID | Army ) so that the array will hold the entire army.  If I want this way, I would also need to determine if I save a unit type in the array if that user doesn’t have any.  If they have 0 Bow units, then is it still represented in the array or not. Also how fast is serialization in PHP?

 

I have also thought of using one row per unit for each user. Meaning that the user could have no rows, all the way up to 50 rows as an example (If I have 50 different unit types and that user has at least one of each). The problem I see with that is that if a larger number of people are playing the game, then the size of this table could be large and slow.

 

 

I am leaning towards the second choice above using an array that has been serialized. Though I do want to hear some opinions or perhaps other choices presented by anyone who has the time to share and help.

 

Thanks

 

[kickstart]

Hi

 

I wouldn't use a serialised array in a field for anything that you might want to search on. It would be hideously inefficient to find all the players with X bowmen for example.

 

A row per unit would be OK if you wanted each unit to have something variable. For example say you wanted each unit to have levels of experience.

 

However from what you have said so far I would say have a table with a row per player per unit type.

 

Properly indexed the number of rows shouldn't be a major problem.

 

All the best

 

Keith