Crafting recipes: storage and quering

Question

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.

Answer 1

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).

Answer 2

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.