[the1corrupted]

Working on my project, I've learned many things. That PHP applications are almost nightmarish to develop, but that database structure is important as well.

So in the best interest of data management, what would be the best approach if you wanted to store an array into a single field?

Right now, I am developing a game (just for hoo-hahs and to add to my portfolio to make it ridiculously robust), and I've hit a little quandry.

I have one table 'monsters' that holds all important (and permanent) data for all the monsters in the game.
I have a second table 'room_mob' that associates to a numeric monster id along with a level minimum, level maximum, and the locations where they can be found. (The second table would allow for multiple monsters to be in multiple places) but let's say I want these monsters (in 'room_mob') to drop a set of specific items.

Monsters are already randomly selected based on location and the level minimum and maximum. And duplicates (with just one item) might slow this equation down.

1. Would it be better to create a table 'mob_drop', decentralize my information and associate a monster id to an item id and slap a quantity on it? (which I'm hesitant to do as I already have 13 tables with another well on the way)
OR
2. Would it be better to store a small array in the table 'room_mob' with the same information? (Max: 3 items, and I'm also hesitant to do this because I have to unpack the array)

These items will be randomly selected and randomly dropped. Any and all advice is appreciated.

[JackOfAllTrades]

Don't worry about the number of tables in your DB. A properly designed DB will have LOTS of tables and relationships between them.

However, if you really want to store an array in a single field, then look to PHP's serialization.

[the1corrupted]

I'm quite familiar with Serialization because I use it for cookies. Thanks for the insight though.

[Atli]

You really should not put more than one value into a single field. By it's very definition, an array is multiple values.
(Note that some databases, Postgres for example, offer actual array types, in which case this argument gets more complex. But in most cases this is not the case.)

There are numerous downsides to doing so. Foremost, like you pointed out, you would have to store it as a string (or possibly as binary data) and have your front end unpack it, which opens the database up for corruption, usually wastes storage space (numbers and dates are smaller than their string representations), and makes it painful to search the data or use it in any sort of meaningful way in a query.

Not to mention that the database itself is highly efficient when it comes to storing and manipulating data. Why bother writing front-end code do that when you are already working with a database engine that is made for that stuff?


I'd also agree with JackOfAllTrades. The number of tables in your database matters very little. At least not while your counting them in two or three digit numbers. - It's no reason to be serializing stuff into fields.

This post has been edited by Atli: 12 May 2011 - 12:06 PM