I'm having a lot of trouble separating materials, base materials, and other materials into database tables with Mysql. I'm working on a crafting section of my game. Basically materials should come from base materials, and some base materials could make other base materials. It's quite difficult for me to sort out.

Materials Table

id, name, type, classification, armourRating, temperature, applicableItems, marketPrice, lowestMarketPrice, highestMarketPrice, token

classification is for (basic, moderate, mastercraft, legendary), temperature is whether the material is warm or cold (leather, or furs), applicableItems (good for armour, good for weapons, good for clothing), token is unique.

Materials Base

id, name, producer, producerType, productionTools, materialToken

producer (sheep, cedar tree, iron ore) producer type (animal, ore) productionTools (shears, pickaxe, bladed), materialToken (from token field above.)

The confusion comes from when I want to produce several things, like furs from pelt, but it can also make leather.

Can someone help me construct my tables better? Thanks for any advice on this.

Edit:

Something that works like this:

Bird
-> Feathers
-> Bone

Animal
-> Pelt -> fur
pelt with fur -> leather or furs
pelt without fur -> leather
-> Bone

Fur -> yarn

Insect
-> silk

Stone
-> Ore -> Metal

Tree
-> Wood -> planks

I would do it from another angle, ignore materials and focus on recipes.

Recipes Table

id, name, finalproduct, materialToken1, materialQuantity1, materialToken2, materialQuantity2, materialToken3, materialQuantity3

(Hide - any animal skin x1)

(Magic Dust - rare flowers x3, blue crystal x1)

(Warm clothing - hide x5, magic dust x2, ogre's hide x1)

(Warm clothing - hide x3, magic dust x2, dragon's hide x1) (another variant)

As for materials just make them regular items (with arbitrary names).

There are no recipes. Just Resources that produces resources with resources that can come from them based on conditions. Those resources once in their usable state have properties. This makes crafting what it should be. Making what you want with ingenuity. I'm leaving it to my players to name and describe their items. :P

I just need help mapping the tables.

I started working from the top down. Let me know if below looks right.

Creature Table
id, name, type, skillLevel, shearProduct, skinProduct, milkProduct, silkProduct, bone product, combat, magic, vitality, perception, token.

1, sheep, animal, basic, wool, sheep pelt, none, none, sheep bone, 4, 0, 6, 4, FWERGERGERGERG

base materials
id, name, shearProduct, unProcessedByProducts, processedByProducts, tools, creatureToken.

1, sheep pelt, wool, "leather, furs", "leather", "tanners tools" , FWERGERGERGERG
2, iron ore, none, iron ore, "smelt", none

materials
id, name, tools , armourRating, combatModifier, DexterityModifier, temperatureMOdifier, token
1, sheep leather, "tailoring", 1, 0, 0, 2 kdnkndknsnksns
2, iron, 3, 0, 1, snjvdnsdnjvsdnksjdn

crafting would be done with a text command.

<craft name="sheep leather pants" base="sheep leather" tools="tailor tools" description="Some basic sheep leather pants, made of well tanned sheep leather. There's a bit of a scuff along the side of the right leg." />

It will scan to see if they're in the presence of tailor tools, have the materials in procession.

There are no recipes.

Keep in mind that the implementation doesn't have to match 1-1 with what the player sees. You can have "recipes" in the background driving what your crafting system is able to do, without presenting recipes as a game element to your player.

I'd assume that you'd want to start with a table that simply maps out your materials. You could put the base materials in the same table if there's something about the data that identifies them as such - could be a property in the table, or it could be a more informal thing like where each item has a list of sources it could be crafted from, and if it has no sources, it's a base material.

Then the next question is what set of rules define how you want to turn those items into other items- and this is part I don't understand from your original post so far. For the sake of an example, I'll make something up.

Say that you want to be able to craft anything you want, but they have to fit into certain types- each one of those types could have a sort of "recipe" associated with them, in order to identify what combinations of items will result in that type of item. That might be as simple as "if you use x tool, you'll get y item-type, with the properties of z list of materials used to make it". That's not a "recipe" in terms of how the player would see it, but you could consider this a "recipe" from an implementation standpoint. A recipe in this context is just the ruleset that decides what things become what and why.

In my example, you could structure it this way:

- Have a table of types associated with your base materials. This is to give you a 'category', so that items that fit this category can be interchangeable to fulfill the requirement to build a certain type of thing.

- Have a table of item types that you can generate. This forms your "recipes". An item in this table would define what tool you need to use, and which base material types you need to use, and how many, to create something.

- Then have a table of each of the raw material items, categorized by the types in the first table, which includes the properties you want your crafted item to inherit from using this material.

Then, say you want to craft "shoes".

To make shoes, table 2 tells you that you need a "hammer" (cause I can't think of another shoe making tool right now), an adhesive, and any combination of cloths (maybe the wrong category words, you can come up with somethign better). "Adhesive" and "Cloth" are categories taken from table 1.

So, then to make your shoes, you look in table 3 for items that suit that combination. You might have 4-5 different adhesives, and 10-20 different types of cloth to use.

The end result is that you could say "combine hammer (the shoe making tool) + Super Glue (an adhesive) + Leather (a type of cloth)" and you'll receive an item that is a shoe (because that's the type of item a hammer makes) and has the durability and appearance properties inherited from the glue and leather. The player can name this whatever they want, and it still fits the category and function of a shoe. Next time they might combine the shoe hammer + wood glue + silk, and they'll get a pair of shows that falls apart more easily but has bonus points for looking good or something (those properties are all in table 3)

I can only incorporate some of that. I understand having the properties on a final material table, but I'd really like to flesh out the idea I've already started. This would include base materials being able to make other materials, and only being able to make certain materials if they haven't been processed in a certain way.

Say, you have an animal pelt, that you harvest the fur from. Obviously tanner tools aren't going to be able to produce a fur and a leather, so the only option now is a leather. Of course if you didn't shear the animal to get fur material that tanner could give you a fur or a leather.

This is a bit difficult for me to map. Maybe you could help me with actually writing out my tables in a pseudo form? I'm really not looking for another system, just to get this one functional.

So I have a creature table which has data that can be put over in an instantiated_creature table.

Lets use one easy example, a sheep. That has shearable fur, and a pelt.

Creature Table
id, name, type, skillLevelToProcess, shearProduct, skinProduct, milkProduct, silkProduct, bone product, combat, magic, vitality, perception, token.

1, sheep, animal, 3, wool, sheep pelt, none, none, yes, 2, 0, 3, 3, token-xyz

Here's where I get confused. There will be a final table to hold all materials:

materials (contains properties)
id, name, type, armourRating, combatModifier, DexterityModifier, temperatureMOdifier, token

1, wool, fur, 0, 0, 0, 4, token-a

2, sheep leather, leather, 0, 0, 0, 3, token-b

3, sheep fur, leather, 0, 0, 0, 4, token-c

How can I handle base products? The step between getting wool, and deciding if a leather and fur can be made based on that change? I can't grasp the table and column name relationship here. Too slow.

If anyone can help with actual examples of this type of system it would be greatly appreciated.

This sounds like something that would require some logic outside of the database itself. The list of materials in your example of the sheep would need to specify a set of requirements or prerequisites in order for the game to make them available as options to the player. Say you added another item to the end of the material so that it read - id, name, type, armor, combat, dex, temperature, requirements, token. Then, in requirements, you would list something that the game could pick up on.

Say you take the wool from the sheep. Doing so would set the state of the sheep in-game to mark it so that it doesn't have "fur" anymore. Then the next time the game would get from the database the items from your sheep - which would give the two leather-type items. You check the "requirements" value for both, and see that one of them lists "fur" as a requirement. Your game logic checks the state of the sheep to see if it still has its wool, and if not, then that option is excluded.

Outside of that, I don't fully understand the difference between a base material and any other material, so I can't recommend anything there.