06-23-08, 13:24 #1Registered User
- Join Date
- Jun 2008
db template for "continuous state machine"
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.
06-23-08, 19:14 #2SQL Consultant
- Join Date
- Apr 2002
- Toronto, Canada
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.
06-24-08, 02:30 #3Registered User
- Join Date
- Jun 2008
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.
06-24-08, 05:26 #4vaguely human
- Join Date
- Jun 2007
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)?
06-25-08, 17:37 #5Annie's Dog Walker
- Join Date
- Nov 2004
- on the wrong server
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.“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.