EPANET Network Description (#15)

Hezekiah’s tunnel is a simple network that we can use to see how our modelling proceeds.  The previous sentence described the network we are toying with, and this will be typical for most situations.  EPANET allows networks to have a title, so let’s look at how this would be modelled.

Should a title be mandatory?  EPANET considers it optional.  How would you present networks to a user in a way that would allow them to distinguish one from another?  A title would do this, but if it is optional, an empty value in the middle of a list of empty values would not help.  A file name is available for an input file, and this too can allow the identification of different networks, but if we are wanting to store the data in a database, we will not have file names.  Should we store file names anyway so that we are providing equivalence?  More on this later.

We might expect that a title would be a single line of text, but EPANET allows multiple lines – in fact, an unlimited number of lines.  The only limitation is that each line has a maximum length of 255 characters.

To replicate this exactly, we could simply use a table with a text field having a maximum length of 255 characters.  Multiple lines of text would be represented by multiple rows in the database.  In a file, we would naturally write the lines in order and subsequently read them in order, but in a database, we need to have another column to store an index or order number.  A database will normally give no guarantee of the order in which rows are fetched: if order is required, the query must define the ordering.

Let’s define a table for storing network titles.

Table NETWORK_TITLE

Imagine that for our example we have a three line title:

Hezekiah’s tunnel
=================
A simple example of an EPANET input file

This would result in three rows in our NETWORK_TITLE table:

Table NETWORK_TITLE

Network ID

You may be asking what the NETWORKID column is all about with its value of “1”, so let’s look at that now.  We are trying to model an EPANET input file which describes a network.  To do this, we need a way of tying together all the rows in our database which describe the network, its operation and control.  We could use a network name and store that with each row, but this has the twin drawbacks that duplicate network names cannot be allowed and that our network name field may use more space than necessary.  A unique ID with no network meaning or user input gives us what we need.

However, the NETWORKID has also been listed as a “foreign key”, which pre-supposes that it exists as the key of a row in another table.  This is another “standard solution” to the problem of grouping related rows.  In the case of our network and many similar cases, a network will have information that we wish to model which can be stored as columns in a grouping table.  We would have a NETWORK table, and our row in that table would have a unique ID which we can store as a foreign key in other tables to link other rows describing the network.

At the moment, the only content we are storing in this table is the name of the file which contained our network model, but we will find other information to store in this table later.

Table NETWORK

Imagine that we are trying to replicate, in a database, the information contained in an input file called “Hezekiah.inp”.  A single row in the NETWORK table would act as a sort of “master record” to give this network a unique ID:

Table NETWORK

“1” is our unique ID for the network and would normally be automatically allocated using an automatically incrementing ID column or by fetching a value from a sequence.  The important thing about this value is that there can only be one network with an ID of “1” in the table.  As we model more data in this input file and add new tables to do so, each table will have a NETWORKID column in just the same way as our NETWORK_TITLE table has.  All of the rows in various tables will store this unique ID “1” in a NETWORKID column so that they are all linked correctly to our network.