0

I am creating a database to store data related to costs. The cost information can be very varied and I won't be able to fully determine all possible columns currently.

An example piece of data might be the cost of a F-15 jet. So taking this from Wikipedia:

$27.9 million in 1998 dollars.

I will also need to store contextual information, for example:

  • Model Variant
  • Weight
  • Wingspan
  • Length

The model must also be able to store completely different information such as price of a pressurised water reactor used in a nuclear power plant. The cost could be:

€200 million in 2011 euros.

The contextual information for this would be things like:

  • Throughput
  • Max Operating Temperature
  • Weight
  • Country of Origin

From a database design point of view the cost information seems quite easy to capture, all costs will have:

  • Description
  • Economic Conditions
  • Unit
  • Value

The contextual information however can be anything and as such it's hard to design a schema that will easily capture all the relevant data.

The purpose of having this data is to use it to estimate future costs. So all contextual information should be searchable, the user might need all costs from the database that are related to aircrafts or all information on the cost of valves under 100Kg in weight.

So my question is, what is the best way of storing this data? Will I just have to try and create a schema as detailed as possible, will something like Entity-Attribute-Value work best or maybe a NoSQL document store?

Thanks for your help.

  • How do you plan on using the contextual information? Will you be actively searching on it (all rows with a Wingspan attribute of 240-480 feet), or merely retaining it for reference purposes? There are variants of SQL that can handle JSON data (PostgreSQL can, for instance); the EAV model (as you've noted), and then NoSQL. However, without more info as to how you'll be using the data, it'll still be difficult for someone to provide a definitive answer. – RDFozz Jan 2 '18 at 16:07
  • Added more info in question about how data will be used. Thanks for your comment – Domronic Jan 3 '18 at 10:24
0

After much investigation I think that NoSQL is the best way forward. Having unsearchable JSON fields doesn't appeal and I like the flexibility of schema free design.

I have created an example MongoDB document that covers everything I was trying to do:

{
    Cost_Type : "Actuals",
    Cost_Description : "200mm copper steam valve",
    Cost_UOM : "£K",
    Cost_Value : 1000,
    ECs : 2017,
    Source : {
        URL : ,
        Location_In_Source : "pg 203",
        Document : "The cost of valves over time.pdf",
        }
    Physical_Properties : [
        {
            Property : "Height",
            Property_Value : "200",
            Property_UOM : "mm",
            },
        {   
            Property : "Weight",
            Property_Value : "200",
            Property_UOM : "kg"
            }
    ]
    Categories : [
        {
            id: "Valve",
            Path: ",Components,Pumps & Valves,",
            }
        {
            id: "Aircraft",
            Path : ",Industry Sector,Aviation,Aircraft,"
            }
        {
            id: "France"
            Path : ",Europe,France,"
            }
        ]
    Tags : ["Fuel system", "Hot system"],
}   

The initial fields cover everything that is required for each cost.

The source field can either be an URL or an actual binary document.

The physical properties field holds an array of key value fields with an additional unit of measurement field. if I didn't need the UOM it would be simpler as I could just use a standard dictionary.

The categories are a reference to a mongodb graph collection which will be defined by an administrator to avoid bloat.

Finally the tags field will allow for the user to add any other information they like.

I think this should cover everything I need but now I just need to actually implement it in a MongoDB prototype.

  • 1
    Well. There are a few RDBMSes that have JSON column types (PostgreSQL, to name my favourite). If you need ACID behaviour (with costs it might be important), these will provide you with it, while still offering the flexibility of JSON. And, TBH, much of your document seems to be properly normalizable in the relational way, allowing the moving parts to be stored in JSON. – dezso Jan 4 '18 at 10:46
  • Thanks for your comment. The reason I am shying away from the RMDBS is that the physical properties field is a Key Value pair which makes querying it complicated. Unless I'm missing something. – Domronic Jan 5 '18 at 9:50

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.