table ITEM_TYPE (
        ITEM_TYPE_ID   numeric  --  Primary key.
        ITEM_TYPE_DESC char     --  Description (weapon, armour, ...)
    )
        
    table ITEM (
        ITEM_ID        numeric  --  Primary key.
        ITEM_TYPE_ID   numeric  --  Foreign key into ITEM_TYPE
        DESC           char     --  Description.
        -- More common attributes like weight, value, etc.
    )
        
    table WEAPON_DETAILS (
        ITEM_ID        numeric  --  Primary/Foreign key.
        WEAPON_CLASS   numeric  --  Strength of weapon
        -- More weapon attributes.
    )
        
    table ARMOUR_DETAILS (
        ITEM_ID        numeric  --  Primary/Foreign key.
        ARMOUR_CLASS   numeric  --  Strength of armour
        -- More armour attributes.
    )
     
    table STORE (
        STORE_ID       numeric  --  Primary key
        -- Store attributes.
    )
      
    table STORE_INVENTORY (
        STORE_ID       numeric  --  Primary/foreign key *not unique* 
        ITEM_ID        numeric  --  Foreign key into ITEM.
    );
[download]

Of course, were I to really sit down and take the time to create such a game, I'd probably come up with a totally different idea of how to make the database. ;-)

Abigail

Method 1 is worse (worst?). Your table is denormalized, and you will run into a maintainance nightmare.

It is not unusual to use denormalized tables in a data warehouse today, but in normal database, NO.

Don't worry about large table, as long as it is properly indexed, not a really big deal, how big can it be? The most important thing is to do it right.

Sounds like you have 1-to-many Stores and 1-to-many Items, with a many-to-many cross-product Inventory in-between. Not sure why you distinguish "Items" from "Weapons" from "Armor" as table types... might be simpler just to have an item have a description field or a 1-to-many properties field.

But Randal nobody could ever accuse Class::DBI of being fast. Others he, and myself, have seen speed differences of 10:1 using direct SQL versus using Class::DBI with MySQL as the underlying database.

I would however suggest the writer do some searching, in the SuperSearch on the site here look for 'database', or go and look in the tutorials, there are some excellent documents there.

jdtoronto

Not sure why you distinguish "Items" from "Weapons" from "Armor" as table types . . .

My guess is that (being this is for an RPG), 'weapons' and 'armor' have to store certain modifiers (like "+3 magic defense, -2 speed") which might not make sense for the other categories. Unifying the tables while handling modifiers elegantly probably isn't easy.

Update: Actually, now that I've thought about it a bit longer, the actual attributes of the various item types are probably going to be static, so there's little sense in putting them in the database. Instead, they should probably be in a configuration file, and the data in the file related to the items in a store. This should be easy to do with Class::DBI, since it doesn't say that classes in a relationship with the current class must use a database.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

: () { :|:& };:

Note: All code is untested, unless otherwise stated

Method 2 is better, but can be improved. Split the inventories table into three seperate tables, one for each item type (armor, weapons, and items) and get rid of the item_type field.

Not really a Perl question, but...

Stores have Inventories. Inventories consist of Items (I'm deliberately ignoring weapons and armor). To reduce/eliminate redundant data (in broad brush terms), you'd want a table that holds info about indivdual stores. You'd want another to hold info about individual items. You'd want a third to hold inventories (store_id, item_id, quantity).

Check out some books on Data Modeling and/or Normal Forms, or Google for it. Its a very worthwhile subject to understand if you want to do db work. At least until OO databases start becoming more dominant, if they ever do.

What about using a link table (examples):

table weapons
wid primary key not null
wname
notes

table stores
sid primary key not null
sname
notes

table weapon_inventory
iid primary key not null
sid
wid
qty
[download]

I do become more and more interested in why there is as yet no module that allows you to store items with meta data (like armor bonus, attackt bonus...ect, different attribs for each item) in a database.

I do this all the time, using standard database modules.

The two primary tactics I use are:

• Meta-data goes in a separate table, with columns for base object ID, attribute ID, and value.
• Meta-data goes in a hash that automatically gets packed and unpacked with something like Storable as it's fetched/inserted.

Both work, with some degree of tradeoffs in terms of performance under different types of query regimens.

I have two particular projects that each use one of those tactics. The problem with the first is speed and query building (not hard just has to be done) and the second hides that data away where it can't be easily queried. It would be awesome to have a module that gave you answers in a hash, let you edit it, and then put it back into its prospective parts of tactict 1 (for lack of better nameing). Of course then you have to deal with more complex data structures than a hash, arg, the whole thing quickly grows out of hand. Any ideas? Should this be a new topic or am i all alone here? :)


___________
Eric Hodges

Storing such variable meta-data seems like a job for XML. Unfortunately there is not yet a fast XML-based database available.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

In that case, just make a BLOB field (or whatever the equivilent is for your database) and put XML into it. Though that will make it slow to do searches based on that data.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

: () { :|:& };:

Note: All code is untested, unless otherwise stated