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!