2

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 "Buildings" 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 "Materials" with columns ID, name and icon and the "Buildings" table would have a column "MaterialsRequired" which would hold a reference to the ID of the needed material in the "Materials" table. But I have no idea how to do that (which is a best practice, afaik) AND store the needed amount of said material.

2

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
2
  • Oh, so a separate table to hold sort of pairs of "material_id" - "quantity"? – user85128 Aug 16 '20 at 14:44
  • yes as you can see in the table, so you are flexible and you have to store materials only once and every building can have its own number of sigle materials – nbk Aug 16 '20 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.