TalkPHP - Need some advice in DB design

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - Need some advice in DB design (http://www.talkphp.com/mysql-databases/2125-need-some-advice-db-design.html)

Need some advice in DB design

Hello guys, this is my first post on this forum, I've been reading the other posts, tutorials and articles and I think you have some great content here. :-)

I've been programming in PHP for over 2 years, I know a lot about the language and about database design, but I need some advice from more experienced people for the project I have in mind, so I hope you can help me.

Here's the scenario: I am brazilian, I live in Brazil and there's a company here that is developing a space-sim MMORPG called Taikodom (it'll be available in english too).

I'm a beta-tester for this game and I want to create a database (and the PHP-website of course) that will be a big index of everything in the game: zones, npcs, quests, items, weapons etc. Examples of what I want to create are sites like Allakhazam and Wowhead, these are sites for World Of Warcraft, but the idea is the same.

Now we come to the part that is confusing me: if you browse these sites you'll see that pretty much everything is defined as an item. It doesn't matter if its a weapon, armor or a potion, check the URLs and you'll see that they're all like ?item=[item_id]. Although everything is an item, the properties are different: a weapon has damage, while a potion doesn't.

In Taikodom we have the same design: a cannon is an item, and so is an engine; but the cannon has a damage property while the engine has a speed property.

And then comes the big question: how can I transpose this design to a database?

Thinking about it for a while I came down to 3 different solutions:

1. The most stupid thing you've ever seen: create an item table to store all possible items. This table would have ALL properties of ALL items, so if we're adding a cannon to the database we also need to set useless properties like speed.

2. Create different tables for different types of items: there would be a weapons table, an engines table, shields table and so on. Nice.. it could work but I can't have "universal" links like those WoW sites. By "universal" I mean that every link looks like ?item=[item_id] instead of weapons.php?item=[id], engines.php?item=[id] and so on.

3. Last one and possibly the way to go: create a master item table that would have columns such as id, item_name, item_type and other basic properties that all items have (eg: price). Create secondary tables for each type of item, these tables would have an id field that is linked to the id in the master table and also the other fields representing the individual properties of each item type (like the damage for the weapons and the speed for the engines). This method allows me to have the "universal" links, but I also need the item_type field to know which table I have to query to get the other properties.

Ok, I'm tired of writing and you are probably tired of reading. If you survived, please point me in the right direction. I wanna know if solutions 2 and 3 (especially the latter) are a good way to go. Or if you have a better design please tell me! ;-)

Thanks in advance.

I would probably go for option 2 with an additional index-type table. This index table would have 3 columns.

Column 1: id - this would be a unique id for each item
Column 2: item_type - types of item - ie, the table name the details are stored in (weapon, engine, etc)
Column 3: item_uid - this would corospond with the item id in it's table.

So essentially a cross between options 2 and 3. However, no-one has ever confused me with a database design expert so I'm sure one of my fellow TalkPHPers will arrive shortly to give you a better way :-D

Alan

My suggestion: Create the item table like Alan says, and then you could have a different table for each item type containing item properties. Having multiple property tables may help keep the table size down thus keeping query speed up.

Be careful in those property tables. The problem with having a defined number of fields in a table is that...a defined number of fields.

So, for instance, you wish to add or remove a property to an item, you have to change the structure of the whole table, and then update any and all queries that would reference this property.

Something I try to do when confronted with something like this is to have a table set up something like this:
item_id - id of the item (links to the main item table)
item_property - name of the property
property_value - value of the property (such as HP or whatever)

example:

Code:

item_id  item_property   property_value

1244     prop_hp_total   2455

This way you never run out of property names, because you just add one with the item's id num. Queries are easier to write as you can just return all of the results for a given item and display accordingly. Actually maintaining the queries is not an issue anymore, because properties added or removed are not really an issue.

Hope this helps!

As an addition to Bill's post, I can advice you to create a property table as well. It's a sort of category table but then using only the number and the name for display.

You'll have a design like this:

Code:

-- Item ID database

item_id     item_property_id

int(15)     int(15)



-- Property Database

property_id    property_name     property_value

int(15)        varchar(255)      varchar(255)

Linking both databases, you would only retrieve the info you wanted, when you want to. Not the complete database with all your information. It's the way that suits me best at the moment, but it might be a little bit exaggerated.

I think Salathe - the guru - will be better at this than me but who cares. :-) My point of view.

Hey guys, thanks for your suggestions!

I think what Alan said is very close to what I outlined in my solution 3 with the exception that I would put some standard data (like item name and price) in what he called the index-table.

buggabill and ReSpawN, your approach is interesting too but I don't really like the idea of having the data all scattered like that. As buggabill said, if I need to add/remove a property in my design I'd have to add/remove a column to the table and change all my querys, but in your design if I have 30 items and I wanna add/remove a property I also have to insert/delete 30 rows. :-)

Besides that, I was thinking of using an OOP approach for this project, something along these lines:

PHP Code:


		
			
class BasicItem {

    public $id

    public $type

    public $name

    public $size

    public $price

}



class Weapon extends BasicItem {

    public $damage

    public $cadence

}

/* Please ignore the public scope, it's just an example */

Now if I want to load some data from the DB into a Weapon object I guess it's better if I have a "weapons" table with the columns "damage" and "cadence" (and the "id" of course).

Quote:

As buggabill said, if I need to add/remove a property in my design I'd have to add/remove a column to the table and change all my querys, but in your design if I have 30 items and I wanna add/remove a property I also have to insert/delete 30 rows.

Why?

Just delete the row that you need to delete, or add a new row to the table. The item_id column is the important one.

For example, if you wished to get all of the properties for one item using my example:

Code:

SELECT 

           item_property, property_value 

FROM 

        tbl_properties

WHERE

        item_id=1244

It does not matter what order the properties are in the table, just that they all share a common item_id. Just make sure to index the table - at a minimum - on the item_id. Add the properties to the table making sure that the item_id is specified correctly. Also, removing a property from all items would be a heck of a lot easier.

Code:

DELETE FROM 

        tbl_properties 

WHERE 

        item_property="MegaPower HP"

You do not have to have multiple tables for the properties for different item types. That was just something that was mentioned earlier. You're approach of hard-coding the tables with property names will make the database monolithic at best and more like a total pain in the butt to maintain in the future.

Quote:

Originally Posted by buggabill (Post 9903)

Why?

Just delete the row that you need to delete, or add a new row to the table. The item_id column is the important one.

Maybe I wasn't clear enough. What I meant is that the items are all categorized, all cannons share the same properties while all engines also share the same properties. If the developers decide to add a new property to a cannon, they are also adding it to all the others. Now think about the database, suppose I have 30 cannons stored there, if I need a new property then I have to insert 30 new records in the properties database, one for each cannon. In my design all I would need is a new column in the cannons table, and then of course I'd need to set the value of this column for the 30 records.

Quote:

Originally Posted by buggabill (Post 9903)

It does not matter what order the properties are in the table

Yes, for me the order does matter. I'm not just going to output the data to the page in whatever order they came from the query. The profile page for an item will have images and the properties will be displayed in special locations, some may be clickable, others may have a different CSS class and so on.

Also, think about the OOP part of the project. If I do it how you say then I'll have some trouble to load the data into the objects. I would have to load each property one by one:

Code:

damage = SELECT value FROM properties WHERE name = "damage" AND item_id = XXX;

cadence = SELECT value FROM properties WHERE name = "cadence" AND item_id = XXX;

OR I could query only once and then use a switch:

Code:

SELECT name, value FROM properties WHERE item_id = XXX;

foreach(record in result) {

    switch(name) {

      case "damage":

        damage = value;

      case "cadence":

        cadence = value;

    }

}

Don't get me wrong, I really liked your suggestion, it shows a different implementation that solves the problem and it made me think about other parts of the project and about my own implementation too. I haven't decided how I'm gonna do it yet, but right now it seems that your way is gonna bring me some trouble in the OOP field. :-)