0

I'm building a software that allows users to extract analytics from a data source. A subset of data periodically is extracted from the data source and stored in a separate database for ease of access (querying original database is time consuming due to its size, and only a subset is of interest). The queried data is mapped to a "parameter definition", such that each different parameter (defined by parameter_id in original database) of the queried data has its own "parameter definition" - (defined by definition_id in local database), which only exists in the new database and provides additional context for that parameter. See below for a simplified graphical representation:

enter image description here

I do run into a problem however, since a separate part of this application needs to refer to specific parameters by their definition_id. For example, a website needs to send a request and include a specific definition_id. For the sake of this example, we can say this is the definition that describes "total revenue" of a given user. We know this ID, because we created its Parameter Definition during local database's seeding process.

Question:

How to reference that definition_id from within the website's code? More specifically, what would be the best practice to do this, assuming that there are multiple places from which this value might be referenced? I can of course hard-code it directly, but this seems like a short term fix, rather than a solution that can scale, especially considering that there might be more values I am interested in.

One other solution I came up with was using a CSV for the database seeding and potentially referencing that CSV from other components (e.g. website) when I need to refer to specific parameters. This feels like a more robust approach, but I'm still unsure whether it won't come back to haunt me in the future in a form of technical debt.


UPDATE:

I suppose one of the other solutions would be to store a unique canonical_id (e.g. "total revenue") in Parameter Definitions that could be referenced in code, but my question still stands.

1 Answer 1

3

I would say that a "Natural Key" (as you say, id = "total revenue") is the best solution here.

However you still have to have some way of transmitting this list to consumer apps, in this case you web app, and it worries me that you keep saying "local" database. As if you have manually pulled some data to work on for some excel report or something.

Your website is presumably calling an API and that API can expose an endpoint "ParameterDefintions" which can then list the valid definitions it knows about. I would still use a plain english string id, otherwise you are just going to be adding a unique "name" property or something which serves the same purpose. But you wouldn't have to as any calling code can just make this Get ParamDefs call to get the Ids before running its main function.

You are a bit vague on the seeding process, but I imagine you have some concept of these definitions, "the parameter that refers to the total revenue" which exists outside of any databases. Maybe an enum or set of classes in your code?

This is what I would give the natural key to and what I would return from a "Known Definitions" endpoint to give the full list of all possible definitions whether they had be mapped to a particular dataset or not.

2
  • Thanks! I will admit that the above example is somewhat contrived for brevity. The "local database" was just a term that came to mind; in reality, this is simply the database storing the application data, with a separate schema which periodically ingests a subset of a data from another system (and this extract of data is then used within the application). Commented May 6 at 16:02
  • Regarding your suggestion of "get parameter defs" - I'm not entirely sure if there is a need for it (or even whether it would help). This is because I'm concerned with selecting a specific parameter which is not defined by the user at the request time, rather, it's always the same extact parameter that I can only reference from the seed. Having said that, I do see the value in being able to query the parameters in a similar fashion so your suggestion is still pretty helpful. Thanks! Commented May 6 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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