database normalisation/design

EL_Loco

I'm putting together a database that mimics the crafting element of a game I play. Completely OTT of course but anyway, I have a few questions for the database gurus.

currently I've 3 tables, ingredients, items and craft

Items are made from ingredients, not too much fuss there so:

The craft table holds items ID, and ingredients ID and quantity required

so if item_ID 1 is a bullet:
and a bullet takes
2 nickel (Ig_id = 1)
5 tin (Ig_id =2)
8 gunpowder (Ig_id=3)

then the craft table looks like:

Craft
-----
cr_id|Item_ID|Ig_ID|quantity|
-----------------------------
1    |   1   |  1  | 2
2    |   1   |  2  | 5
3    |   1   |  3  | 8
-----------------------------

Now for my problem,
it eventually gets to the point where part of the ingredients for an item INCLUDE another item, so the relationship between items only requiring ingredients is broken. Some items needs items and ingredients to make.

I was thinking about putting another field in crafting which would be a "item_required" field and would look to the item table, and would just be empty for anything that didn't require an item. Which would work in theory, but I'm just wondering if anyone with more database experience could recommend anything else. or is that it, just need another field linked to items?

all help appreciated.

Giblet

An ingredient is an item, and an item should have a list of attributes to define it.
So you should have a pair table / link / xref table for crafts.
ItemID | RelatedItemID

If an item has no related items, it's standalone.

EL_Loco

Cheers, I had flirted with that approach alright. I can still have my ingredients table but link the item id to it for the ingredient specific details.

croo

Sounds like the structure you are looking for is that of a Bill-of-Materials - try googlings boms and databases. A BoM is typically represented as a simple tree structure so I'm sure in gaming you are very familiar with that idea.

A bom is usually implemented with two tables; item & craft using your examples. But there are two relationships between them; "made of" which you have and "used in". Then you need some recursive code, if a item is "used in" you explode it. Items & Ingredients sound very similar? Is the only different that fact that an ingredient is used to make something? If so the existence of records on the used in relationship would be the same.. or not? My own background is business apps, but again in these we might flag an item as a Finished Good or Raw Material (or even a Sub Assembly)... the important thing is it's usually a flag on the item rather than a different table.