I am looking for a design pattern that would allow me to capture metadata relating to columns on a table. More specifically, here is my situation.
I have a database with fields in a
field table, crops in a
crop table and prediction models in a
model table. Each
field can grow one
crop and subscribe to multiple
models. Each model offers different kind of predictions for a specific
crop allows a particular set of inputs from the farmer. For instance, all crops will expect a planting date while only some of them will expect a flowering date. Therefore, I create a
generic_input table and crop-specific tables (
soy_input, etc.) to capture that data. Each column of those table is an input that needs to be captured.
Here comes the interesting part. Each
model also specifies a set of required crop inputs. That is, inputs that need to be set in order for that model to be able to compute its predictions. That means I would essentially need to reference particular columns of my input tables which is not really possible.
So far, the only real solution I found would be the Entity-Attribute-Value pattern. However, I've read so many posts warning against its use (example) that I am quite reluctant to go down that road. Another option would be to create an enum or a table listing all my inputs but then if new inputs need to be added or if we add a new crop to the system, we need to update that list as well: that results in some sort of data duplication or poor relational consistency.
Are there any other pattern that could support my use case? What would be an appropriate solution? Is this a case where EVA would actually be a good solution?