[lalbatros]

Hello,

I have delayed a small project by at least 6 months.
Now I think I have an idea about "what is needed".

Colleagues want to track the state of several plants on a daily basis.
This involves tracking quantities essentially (continuous variables).
However, a lot of flexibility is needed in the way things are calculated.
For example, sometimes material movements are calculated, sometimes they are measured.
As another example, sometimes the fluxes are calculated from a simple "perfectly mixed tank" model.
Sometimes several methods are possible at the same time and sometimes this is used to check consistency and drift of some devices.
The main demand is about flexibility.

My first idea was about representing "real facts" in various tables.
For example: a table of movements, another for stocks.
I realized that this would not be flexible enough, would not be opened to the end user, and too much specific.

Actually, I think now they need something extremely simple.
Their database should record the state of the plants, based on states variables.
Therefore, an important field would be the (names of the) state variables, like a stock level.

Building on that, it is clear that some state variable would be measured while some would be calculated from other state variables.
And this can also be represented in a field, without any need for a physical interpretation. The meaning is left to the end-user.
In this table, the calculation formulas could be included as a field and evaluated when needed. The formulas would involve current values or values from the previous day. The flexibility would be extreme since the formulas could even be changed everyday (this would be useful at least from time to time).

Now my question:
Have you ever seen such a database?
I would appreciate suggestions and ideas before I start implementation.

Thanks

[r937]

yes, i created a database about plants before

where is the information about soil quality? how much food and water do you give each plant? what about sunlight and shade?

each of these requires its own separate many-to-many table, i.e. you would need a table for different soils, a table for plant nutrients, a table for hours of sunlight, etc.

[lalbatros]

r937,

Sorry that I was not clear enough.
I am talking about factories, not about a multicellular organism usually called "plant".

Note however, that there might be one point in common.
In our factories, we are also dealing with chemical analysis of our raw material and finished product.
In this case a typical table design contains not much more than 4 columns: the date, the material, the analysis name, the value of the analysis. Doing so makes the chemical analysis database flexible. In addition such a normalised table is very easily used in pivot tables which are very powerful for reporting.

For the database I am thinking of now, I must say that the data are much more heterogeneous than a simple collection of chemical analysis. I cannot describe them better than being "state variables" to represent the state of a factory. The state at a given date is partly measured and partly calculated based on the previous state and the measured data. Moreover, the calculation rule might change slowly as the configuration of the factory might change.

This explain my question, since, altough it is simple, this database seems to me a little bit unusual, and therefore I would like to know about similar things and to have some suggestions.

Thanks

[mike_bike_kite]

Are you going to have lots of changes to the types of parameters you store against a plant i.e. will you be adding new parameters each day/week or will they be pretty fixed once you’ve decided on them? You can ignore the calculations in the above – I understand that these will change constantly.

If the fields will remain moderately fixed then you can just have a table for plants with a simple field for each parameter you’re wanting to store. The calculations could be stored as simple update statements that are run once a day/hour etc and that set each calculated value. If you need to change a calculation’s formula then you just alter the update statement to match. This sounds simple because it is.

If the fields are going to change regularly then you’re moving into EAV territory which tends to get looked down upon on this forum but sometimes there are very few other methods available.

Can you give :

• Some example parameters you want to store against a plant
• An idea of how often changes occur and in what manner
• Some example calculations
• An idea of how often changes occur to these calculations and in what manner
• An idea of what type of plant we’re dealing with – it makes it easier to imagine a solution when you have a better picture of the problem.
• Some examples of heterogeneous data (and some homogeneous data)
• Do you want to know the date time each parameter was set (and what by)?

Mike

[Thrasymachus]

you want a database along the lines of Aspen Watch. I would google Aspen watch or Aspen tech (I think the comapny is called Aspentech). I happen to know first hand that this is what is used by a company that runs many large refineries around the world.

there is a odbc driver for it, and the SQL involved in proprietary but pretty ANSI compliant. they support natural joins, but yeah it is used to do just this such a thing. do not build your own.