# Crafting recipes: storage and quering

Consider the following situation. In a game you can perform crafting: i.e. create new items from existing ones (like in Minecraft). I have following game entities (simplified):

• item: Atomic part.
• recipe: A list of items resulting in other list of items.

What would be best structure to store all the recipes in the game? I consider using SQLite database for storing it. However game is not a general type application, so, would it be better to use XML + in-memory structures for storing it?

A database has some major advantages to me, it's much more "DRYer" in my opinion. You also can query it. But I doubt if direct database performance is sufficient for smooth gameplay. On the other hand you can use say, XML to store all of the recipes and use in-memory storage and query it with LINQ. If finally it all results in in-memory structures then database seems to have much overhead, since it's used just as a storage. Or is it still better to use DB?

Solution:

For now I've decided to stick with XML as storage for recipes. The major advantage of XML for me is that the final solution is more portable to other platforms since XML is more widely available.

• Just for the performance question : IMO a database will be fine for recipe useage as user input is a slow operation compared to query speed. – Jonathan Connell Sep 7 '11 at 8:45
• Well, that is probably true in most cases. – Petr Abdulin Sep 7 '11 at 8:54

I would suggest, if you're looking to have a database structure, then you could employ the following:

2 Tables, Items and Recipes

Items has the following columns: ItemID (int), ItemName(varchar) and optionally IsComplex (bool) [that last column is purely for efficiency later on, not used in this example, but I'm sure you could see how it could save time later on].

For Recipes, use the following column: ResultantItemID (int), IngredientID (int), amountNeeded (int)

In Recipes, ResultantItemID and IngredientID form a composite key.

So, say for example you had a recipe for apple pie that required 5 apples, a pie base and an oven.

Items Table
ItemID, ItemName, IsComplex
1, "Apple", No
2, "Pie Base", No
3, "Apple Pie", Yes
4, "Oven", Yes

Recipe Table
*ResultantItemID, IngredientID, amountNeeded
3, 1, 5
3, 2, 1
3, 4, 1

If you wanted to know the ingredient needed for an apple pie, just query the recipe table where ResultantItemID = 3 (Apple Pie's ID)

You might also want to add an "Is Consumed" column to the recipe table to indicate whether the ingredient is used up (apples, pie base) or not (oven).

• Thanks. Database design is not a problem to me (or at least I think so :-), the question (updated) is to use database at all or not. – Petr Abdulin Sep 7 '11 at 6:19
• In that case, unless you're planning an epic, 100,000 item library, I would imagine keeping everything in an XML format would work out to a much simpler and more workable solution with very minor sacrifices in terms of memory overhead. – Jordaan Mylonas Sep 7 '11 at 6:23
• XML is great if you want to support modding, too. But it is also a pretty verbose structure. If you are doing this just internally, something more like CSV would be easier to maintain. You might also want to look into how Dwarf Fortress stores complex recipes. – DampeS8N Sep 7 '11 at 11:07
• csv has less overhead for sure, but I find it way more difficult to read/write a complex structure to it. – Jordaan Mylonas Sep 7 '11 at 11:32
• @DampeS8N is Dwarf Fortress open source? That surely would be helpful. Maybe a link? – Petr Abdulin Sep 7 '11 at 11:42

Don't use database, unless you need millions of recipes. Maybe not even then.

You don't need to run complex queries against it. You don't need to store gigabytes of recipe data. Most probably, all your recipes will fit in memory 10 times over and still leave enough for all your needs.

Unless you have insanely high number of recipes, or you work on a platform with ridiculously low memory, you'd be better off reading recipes from in-memory storage.

And for long-term storage, I suggest XML - it's very easy to work with in C# and you'll appreciate its extensibility when you need to add that one special recipe that just happens to require something beyond simple ingredients.

• +1 (I'd also suggest json, since I love curly braces and it's less verbose than XML) – Raine Sep 7 '11 at 13:59
• Thanks, I've also come to almost same conclusions. XML is more flexible, indeed, that's a major factor for games especially. – Petr Abdulin Sep 7 '11 at 14:49
• @Raine I usually prefer XML because it's so easy in C#. JSON is a bit better as a format - briefer and more readable - but .NET XML serialization just beats any JSON library right out of the water. – Nevermind Sep 8 '11 at 4:12