Database Design Questions

WebsiteWill · 2003-09-29T06:07:31

Hello again. I am currently trying to come up with a good database design to be used in storing all of the items in a game. I am currently thinking along these lines. I will have a master list of items. Each item in the list has a unique key and name as well as a weight, and any other stats common to ALL items. Now, some items will have various stats that other items will not have. For instance, weapons will have an damage and speed factor while armor items will have AC factor. Containers will have a capacity attribute. I don''t want to simply include ALL possible attributes into the main table because this would amount to a LOT of wasted space. To accomplish this I am thinking that weak entities could be a way to go. This way if an item has defensive stats and magical stats then there will be a record in the weak entity defensive stats table for it''s defensive stats and a record on the weak entity magical stats table for it''s magical stats. This seems like it may work well for the master items list. However, considering that multiple players can have multiple instances of the same item (playerA has 2 broad swords and playerB has 3 broad swords) and each of the swords may have a different quality or durability value but are otherwise the same. I can''t think of an effective way to store this kind of information into a MySQL database. Players will have an inventory consisting of weight. So the number of items a player can have depends only on the sum of all his items weights. Therefore, a player inventory table needs to be able to adapt in size according to the number of items owned. Likewise, if he owns 2 broad swords one with quality 5 and another of quality 10 then these are obviously distinct entities (they would be even if they had the same quality). But, so far the only identifier I have is for a generic broad sword item. Seeing my troubles here? This is really boggling me. I am currently thinking of a single large player inventory table that holds every item owned in the game and each item is indexed by PLAYER_ID, ITEM_ID, ITEM_INSTANCE So this table might look something like this PLAYER_ID ITEM_ID ITEM_INSTANCE 001 0004 01 001 0004 02 002 0004 01 When a player loses an item then it removes this largest ITEM_INSTANCE first. Now, if all items were exactly the same this would work well. But since each instance might have a different quality (or some other uniqie stat) then I would have to include ITEM_QUALITY (or other stat) into the primary key so that I can be sure to delete the correct item if the player loses it. This also would work except for the many cases where an item won''t have a quality associated with it. Things like bottles of water come to mind here. I don''t want to add extra fields to every item when many of them won''t be needing the fields. So for the master items list I am making a table that includes fields for all stats shared between every single item (ID, NAME, WEIGHT, TYPE are some). Then maybe a weak entity for defensive stats, one for offensive stats, one for magical effects. This way, I will have an offensive stats table that can be queried to get the offensive stats of a broad sword if the broad sword has offensive stats. This seems like it will be efficient on storage space except that I am making defensive stats a table for all stats. So if a breastplate adds 3 to strength then it would create an entri into the defensive stats table that has 3 in strength and 0''s everywhere else. This isn''t a good use of space but the other alternative i can think of would be to make an individual table for every possible stat I will need and is an item has multiple stats then it will be listed in multiple tables... I''m hoping for some ideas of a better way to accomplish this. What would be nice would be to see how other people have done this, EQ, DAoC, AC some to mind but I know that will never happen Any input is appreciated. Thanks, Webby

Networking and Multiplayer Programming

Started by WebsiteWill September 23, 2003 06:31 PM

9 comments, last by WebsiteWill 21 years, 4 months ago

RonHiler

214

September 29, 2003 06:07 AM

quote:
Original post by WebsiteWill
In regards to MySQL nd the GPL, you are 100% correct.

Cool, good to know. I am going to go ahead and use it.

quote:
Also, I use a nice program called Wizflow for playing around with my Er diagrams.

Interesting, I''ll check it out.

quote:

Anyway, we should keep in touch. We can maybe gleam some insight from one another from time to time.

Definately! Although I''m such a database newbie, I may not be such a big help to you

I only *just* discovered that MySQL comes with C headers itself (I know, RTFM, I''m working on it, hehe). This is a big relief, since the more I looked at MySQL++, the more I hated it. The C functions seem pretty straightforward, and I''m quickly wrapping them into a database access class, so I''m looking forward to being able to load item data into my server and start working with it.

Creation is an act of sheer will

Database Design Questions

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Database Design Questions

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines