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

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).


Two Tables

A table of things, and a table of connections.

Things := (Component ID | usefull stuff to know about the thing itself | etc)
Connections := (Connection Type | From Component | From Port | To Component | To Port)

Throw A Connection ID as a PK on the Connections table if you aren't happy with a full table key.

You can go one further, and have a Ports table that describes useful things about ports and use the Ports ID in the connections table.

The flow on the port is directional in that air/electricity/substance flows from the from port to the to port (unless the connection is directionless, in which case it probably makes sense to dual enter the connection in the other direction to simplify your implementation).

| improve this answer | |
  • 1
    How would we get the concept of the whole unit? Start at the entrypoint unit and go until we run out of new paths? That feels like a lot of db querying (though we can probably shortcut it by having another table that lists all the components in a particular unit). I'm still on the fence about whether or not this is even needed, as in theory we're going node by node to figure out the calculations, but I feel like hitting the db for all relevant components once is better than constantly pinging it during this process. – Marshall Tigerus Jul 7 at 13:54
  • 1
    Then add an extra id to both the components and connections table representing a given model. Once you have that id, allows you to load all connections, and all components in bulk. – Kain0_0 Jul 7 at 23:07
  • That's a good idea.....thanks – Marshall Tigerus Jul 11 at 1:28
  • 1
    Might be handy to find a text book on graphs, and graph algorithms. There are a few that you can get for free around, and old books are good here (they are rarely distracted by this language or that library). Just can't recall the name of the one I use. But they should all take you through different data structures for implementing different graphs. Translating them to a db is fairly simple, struct => table, pointer => key, and if you need to know everything belonging to one graph tag it all with a graph id. – Kain0_0 Jul 12 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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