DB Design for an item "mixing" script

Tak

I would really appreciate some help on this one before I get started because I can easily see this turning into a massive monster. 🙂

There is a game I'm working on that has hundreds upon hundreds of items. The idea of the game is to combine different quantities of these items and hope that they produce some sort of result. So there would be defined "recipes" of sorts.

For example:

1 mud + 1 sand + 5 fire = 1 brick
1 dirt + 1 wood = rotten wood
1 wood + 1 sand = polished wood
1 gold + 1 silver + 8 copper + 6 fire = 10 hepatizon

And so on. What I'm just trying to figure out is how to properly store these recipes in the database so that they can be easily matched and produce the desired outcome if a user happens to pick the correct match.

djjjozsi

build an items table, users table , definitions table , items_store table.

In the definitions table store the MainItem_id as foreign key, the Item_needed field which is the ID from the items table , quantity , and OneUnitquantity.

items table consist of:
itemid , item_name
1 gold
2 silver
3 copper
4 fire
5 hepatizon

then this example looks like:
1 gold + 1 silver + 8 copper + 6 fire = 10 hepatizon

definitions table:
MainItem_id , Item_needed, quantity, OneUnitquantity
5 1 1 10
5 2 1 10
5 3 8 10
5 4 6 10

how to store the user's items?

make an items_store table:

userid , itemid, quantity

when a user wanted to create a new item, you select that item from the definition table, and select the recipes. Then you've got a bunch of itemID and quantities.
You need to decide where you want to calculate the user's abilities.
Create queries with them from the items_store table to know how many new items could he/she make. I know people who can build this query in one big query, but its not a shame to solve it with 2 queries and a little php hardcoding.

You should draw this database model, and watch how it looks like. And wait for other opinions of course 🙂

Tak

Hahaha... ah, this was sort of what I'd been thinking. I guess you just confirmed my fears. 🙂

With the amount of items, this definition table idea would be huge. Currently there's 700 items and, well, a lot of possible combinations. faints

I'm definitely going to be trying to draw up a database model.

djjjozsi

Tak;10915957 wrote:
With the amount of items, this definition table idea would be huge. Currently there's 700 items and, well, a lot of possible combinations. faints

my model has limits:
the item is for example: boat

i can make boat from: 10rope , 2tree, 3paint
but also: 5 bamboo , 3 bast , 2 wax

or you just define them as wood boat and bamboo boat in the items list.