Best way to store information about a list of materials and their quantity I want to store some information about a game. Specifically information about certain buildings that can be built in it.
Said buildings require materials to be built. I am trying to come up with a good DB model for storing that information in my database.
For example, one building needs 10 wood to be built. I thought about having a &quot;Buildings&quot; table with columns for ID, name, icon and material cost. However I'm usually used to referencing common values from another table. This is to say I would have a separate table named &quot;Materials&quot; with columns ID, name and icon and the &quot;Buildings&quot; table would have a column &quot;MaterialsRequired&quot; which would hold a reference to the ID of the needed material in the &quot;Materials&quot; table. But I have no idea how to do that (which is a best practice, afaik) AND store the needed amount of said material.
No you would have a intermediate table, so you can have a many to many realtionship.
And a material can be also a product of other materials
Building            Building_Material         Material
---------------------------------------------------------
Building_id         Building_id               Material_id
Name                Material_id               Name 
                    Quantity                  Price