Database Design – Best Way to Store Material Information and Quantities

database-design

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.

Best Answer

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

Related Solutions

Sql-server – Best way to store units in database

Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.

Mysql – General structure advice for beginner

Your design is sound.

I have a concern about using the image filename as the key. Although academically acceptable there could be performance implications. A long string value like that is not going to make for an efficient index i.e. one with lots of IDs per page.

Secondly, there's nothing certain but change. At some point you will have to re-locate these files onto a different directory/ server/ domain. At that point you will be updating the primary key of a table which, while do-able, is rarely pretty.

I understand the example you've shown only has four tables. I would anticipate you will have more before your system goes live and then these concerns will become more significant. I'd use a surrogate key instead.

Best Answer

Related Solutions

Sql-server – Best way to store units in database

Mysql – General structure advice for beginner

Related Question