First, Hello again. It's been a while since my last post. I recently migrated over to a new job where I am now developing a website in ASP.NET using VB.NET codebehind (as opposed to VB5 at the old position). Along with this, I get to do a good bit of work on the website design which involves Illustrator and Photoshop. All in all a great job, and the experience is awsome. Ohh, and my boss and coworkers rock! Anyway, on to the question. I'm jumping back to the design of a database for a game I've been working on for about 2.5 years now. I'm wondering if anyone knows of some good books or links of database design for larger scale databases? I don't need to know things like how to create primary or foreign keys or even how to run a design through the normal forms. What I would really like would be some advice on how to tabletize certain items. The OO programmer in me wants to create a table for everything I can create an object for (within reason). An example of this would be: Character(id, accountid) Money(Gold, Silver) Experience(CraftingExp, CombatExp, CastingExp) Identity(FirstName, LastName, Title) etc Another part of me rationalizes that it may be more efficient to lump some information into a single table. So using the above tables I would instead have a single Character table that has columns for each of the items listed above, ex. Character(id, accountid, Gold, Silver, FirstName, LastName, etc) The prior generates many more tables while the latter generates a much larger table. I see good and bad reasons for both and I am hoping for some good pointers or references to help me make my decisions. All told, my current design is in 3rd normal form and contains around 150 tables, so you can see my need for a systematic approach to table and column creation. Thanks for any advice, Webby

Hi,

You are talking about DB normalization.

Advice, create a table for each IMPORTANT object in your game. Not just any table, just important ones.

Character is a good table. As you said, it may include the gold earned by the player, and the experience. As long as you dont need WHEN THAT PLAYER WON EXACTLY THOSE 50GP its ok, you dont need to keep them apart.

Also, create a table for base ITEMS. In that table you can keep the base items in your game (Sword+2, Ring of Vampiric Regeneration, Vorpal hammer :P ). After that you can create a ITEM_INSTANCE table where you materialize each weapon. That way, if the world only has 2 Vorpal hammer of Laughing... you can track each instance. And maybe you can set the Owner from the character table.

So, try to think the DB in terms of BIG objects and use smaller tables to relate the big tables.

Luck!
Guimo

Thanks. I'm definitely leaning that way.
I's visualizing the database in terms of objects and that is and isn't a good idea. Right now it's working so I am going to go with it and see what happens.

The way I currently see is that having one large table for Character would provide these benefits. Fewer tables to deal with. Less storage space since we don't need to duplicate the character id across all the other tables. From a programming POV this also works to have one large class to define the character. Cn have a single private member for each property instead of introducing a subobject for each table that would have been generated.
Many to many relationships also map to their own objects in code. In this case the Character object would have a SpellManager object that contains an array of all spells that character knows and any relevant information regarding each spell. This approach will save memory and processing time since the properties are predominantly simple datatypes instead of user defined dts (except in the case of many to many relations).

I think thi sapproach works quite well for the database as well as the code.

Going back to work. Will post my findings as soon as I see where this leads. I keep redesigning the database because I'm never quite satisfied with some aspect of it. I primarily want ease of maintenance as well as ease of expandibility as well as the obligatory efficiency. This approach for now seems like it may meet these goals.


Webby