-1

TLDR: Looking for a free database option to run locally that lends itself to composition. Object A is composed of B,C,D. Where B,C are of the same type as A. What should I use?

I would like to experiment with some open source database. I am playing a game that has crafting and it is a bit cumbersome to drill down into the objects to figure out what resources I need. This seems like a good problem to solve via a database. I was hoping to explore a NoSQL option as I do not have much experience with them.

To use a simple contrived example:

  • A staff: requires 5 wood
  • A spearhead: requires 2 iron
  • A spear: requires 1 staff, 1 spearhead
  • A trident: requires 1 staff, 3 spearheads, 2 iron

If I wanted to build 2 tridents and 1 spear a query to my database would inform me I need 15 wood, 18 iron.

So each craftable item would require some combination of base resources and/or other crafted items. The question to be put to the database is, given that I already possess some resources, what is remaining for me to collect to build some combination of items?

If I were to attempt this in SQL I would make 4 tables:

  1. A resource table (the raw materials needed for crafting)
  2. An item table (the things I can craft)
  3. A many to many table, mapping items to items
  4. A many to many table, mapping items to resources

What would you recommend I use? An answer might be, there are no NoSQL databases that lend themselves well to your problem set (model and queries).

philipxy
  • 14,867
  • 6
  • 39
  • 83
user3043403
  • 91
  • 1
  • 2
  • 6
  • [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/q/4048151/3404097) [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/q/20215744/3404097) – philipxy Dec 03 '22 at 05:31

2 Answers2

1

Using the Bill of Materials picture I linked to in the comment, you have a Resource table.

Resource
--------
Resource ID
Resource Name

Here are some rows, based on your example. I deliberately added spearhead after spear. The order of the resources doesn't matter.

Resource ID | Resource Name
---------------------------
          1   Wood
          2   Iron
          3   Staff
          4   Spear
          5   Spearhead
          6   Trident

Next, you have a ResourceHiearchy table.

ResourceHiearchy
----------------
ResourceHiearchy ID
Resource ID
Parent Resource ID (FK)
Resource Quantity

Here are some rows, again based on your example.

ResourceHiearchy ID | Resource ID | P Resource ID | Resource Quantity
                  1             6            null                null
                  2             5               6                   3
                  3             3               6                   1
                  4             2               6                   2
                  5             4               3                   1
                  6             4               5                   1
                  7             5               2                   2
                  8             3               1                   5

Admittedly, this is difficult to create by hand. I probably made some errors in my example. You would have a part of your application that allows you to create Resource and ResourceHiearchy rows using the actual resource names.

You have to make several queries to retrieve all of the components for a top-level resource, starting with a null Parent ResourceHiearchy ID and querying your way through the resources. That's the disadvantage to a Bill of Materials.

The advantage of a Bill of Materials is there's no limit to the nesting and you can freely combine items and resources to make more items.

You can identify resources and items with a flag in your Resource table if you wish.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
1

You might want to consider a graph data model, such as JanusGraph, where entitles (nodes) could be members of a set (defined as another node) via a relationship (edge).

That would allow you to have multi-child or multi-parent relationships you are talking about.

Mother == married to == Father

child1, child2, child 3 ... childn

Would all then have a "childOf" relationship to both the mother and separately to the father, and would be "siblingOf" the other members of the set, as labeled along their edges.

Make sense?

Here's more of the types of edge labels and multiplicities you can have:

https://docs.janusgraph.org/basics/schema/

Disclosure: I work for ScyllaDB, and our database is often used as a storage engine under JanusGraph implementations. There are many other types of NoSQL graph databases you can check out. Find the one that's right for your use case & data model.

Edit: JanusGraph is open source, as is Scylla:

Peter Corless
  • 781
  • 3
  • 12