1

I created a database for an app, where I have several networks, which have several nodes and elements and also several scenarios, all related to the networks. [see picture below]

My question is: since the sce_results and sce_paramaters will have result and parameter information of the nodes and elements of a network, should I also build this relationship between the nodes & elements tables and sce_results & sce_paramaters tables via foreign-keys?

or since it gets complicated, the way I created the tables are fine? Maybe more general question, when it is good to build these relationships via the foreign-keys?

So far I though since nodes & elements have their names, I can match them with the name columns in sce_results & sce_paramaters tables, after I download all the necessary data for doing further calculations or creating result plots, etc... Therefore I do not really need this relationship from my point of view. What do you think?

Example:

Network1: Nodes: [Node1, Node2, Node3]
          Elements: [Element1, Element2]
          Scenarios: [ScenarioX]
ScenarioX: Paramater: [Pressure_Node1 = 5, Pressure_Node2 = 10, ...]
           Result: [Flow_Node1 = 100, ...]

# In this case nodes table looks like:
id | net_id | name | ... | x | y
1    1        Node1
2    1        Node2
...      

# In this case sce_parameter table looks like:
id | sce_id | name | extension | value | unit
1    1        Node1  Pressure    5
2    1        Node2  Pressure    10
...

DB-Diagram

Improve this question
4
  • 1
    I am not entirely certain what you mean by "sce_results and sce_paramaters will have result and parameter information of the nodes and elements", but if each sco_results is related to a certain nodes, there should certainly be a foreign key.
    – Laurenz Albe
    Nov 15 at 19:17
  • these table represents the parameter and result information of all nodes and all elements inside a network. Do you think it is necessary also to add the network_id inside the sce_parameters and sce_results?
    – oakca
    Nov 15 at 19:48
  • 1
    I have no idea how these tables represent the parameter information. I guess I would need way more explanation; I am a bit dense when it comes to guessing these things. I see no columns that would indicate a relationship.
    – Laurenz Albe
    Nov 15 at 20:40
  • 1
    @LaurenzAlbe I added an example in the question
    – oakca
    Nov 15 at 22:18

1 Answer 1

Reset to default
0

Storing the name of a nodes row in sce_parameters is a bad idea indeed. Store the primary key of the nodes row instead and add a foreign key constraint. This will guarantee that you cannot end up with inconsistent data.

Improve this answer
2
  • do you think, it is also necessary to add net_id to the sce_parameters or since it has sce_id and connected to networks over scenarios (where scenarios are connected to network via net_id) already accomplish this?
    – oakca
    Nov 16 at 13:23
  • The indirect relationship via nodes is enough. A direct foreign key to networks would be redundant and consequently harmful.
    – Laurenz Albe
    Nov 16 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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