I am trying to model an HVAC system for simulation purposes, which will consist of a series of components linked together by airflow and electrical flow paths. I'm trying to figure out how to best represent this in the database. Basically each component has to have one or more inputs and one or more outputs (with both indicating whether they are airflow or electrical), and we need some way of tracking what component they are linked to "next" in the chain.
Now, the simple way I can think of is having a table that is more or less:
Component ID | Connection Type | Connection Number | Flow Type | Connected Component 1 | output | 1 | Electrical| 2 1 | output | 2 | Air | 2 1 | output | 3 | Air | 3 2 | input | 1 | Electrical| 1 2 | input | 2 | Air | 1 3 | input | 1 | Air | 1
But this feels like its "wrong" in some way. It seems right in the sense that if I wanted to know all of a component's connections, I could easily get it, but it contains redundant data (I could assume component 2's inputs by looking for rows where the connected component is 2). It also feels hard to get the whole system from this data, as I'd have to step component by component until I built the whole thing, however, I'm not sure that's really an issue.
Anyone deal with something like this?
e.g. the above data represents a simple system like:
1 = 2 \ 3
Where 1 is connected to both 2 and 3, with an air and electrical connection to 2 but only an air connection to 3 (this is largely demonstrative, not an actual use case).