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
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)
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:
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:
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:
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:
|All times are GMT. The time now is 10:25 PM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0