How can I structure my database?

Posted 2 months ago by JoeDawson


I currently have a table of items that can be combined to create another item.

Here's an example:

  • Potion - 1 Dose
  • Potion - 2 Dose
  • Potion - 3 Dose

An example combination could be combining a 1 and 2 dose together to create a 3 dose.

I would like to be able to create a relation between these items but I'm unsure of the best way to store this info.

Do I create a table for "recipes" that contains a column for "ingredients" which contains ID's to my items.

  • id (incrementing id)
  • ingredients (list of ids)
  • item_id (unsigned int, ref's id on "items")

Or would you create a many to many style table?

  • ingredient_id (unsigned int, ref's id on "items")
  • item_id (unsigned int, refs id on "items") - the end item

I also need to be able to see what combined items will provide the cheapest way to the "end" item, so to speak.

Items tabel contains:

  • id
  • name
  • price

Please sign in or create an account to participate in this conversation.