CODEWITHSUNDEEP
sqlpostgresql
duck_goes_quack
duck_goes_quack

Reputation: 35

How to manage relationships between a main table and a variable number of secondary tables in Postgresql

I am trying to create a postgresql database to store the performance specifications of wind turbines and their characteristics.

The way I have structures this in my head is the following:

  1. A main table with a unique id for each turbine model as well as basic information about them (rotor size, max power, height, manufacturer, model id, design date, etc.)

example structure of the "main" table holding all of the main turbine characteristics

turbine_model rotor_size height max_power etc.
model_x1 200 120 15 etc.
model_b7 250 145 18 etc.
  1. A lookup table for each turbine model storing how much each produces for a given wind speed, with one column for wind speeds and another row for power output. There will be as many of these tables as there are rows in the main table.

example table "model_x1":

wind_speed power_output
1 0.5
2 1.5
3 2.0
4 2.7
5 3.2
6 3.9
7 4.9
8 7.0
9 10.0

However, I am struggling to find a way to implement this as I cannot find a way to build relationships between each row of the "main" table and the lookup tables. I am starting to think this approach is not suited for a relational database.

How would you design a database to solve this problem?

Upvotes: 0

Views: 72

Answers (1)

Josh
Josh

Reputation: 1601

A relational database is perfect for this, but you will want to learn a little bit about normalization to design the layout of the tables.

Basically, you'll want to add a 3rd column to your poweroutput reference table so that each model is just more rows (grow long, not wide).

Here is an example of what I mean, but I even took this to a further extreme where you might want to have a reference for other metrics in addition to windspeed (rpm in this case) so you can see what I mean.

PowerOutput Reference Table
+----------+--------+------------+-------------+
| model_id | metric | metric_val | poweroutput |
+----------+--------+------------+-------------+
| model_x1 | wind   |          1 |         0.5 |
| model_x1 | wind   |          2 |         1.5 |
| model_x1 | wind   |          3 |           3 |
| ...      | ...    |        ... |         ... |
| model_x1 | rpm    |       1250 |         1.5 |
| model_x1 | rpm    |       1350 |         2.5 |
| model_x1 | rpm    |       1450 |         3.5 |
| ...      | ...    |        ... |         ... |
| model_bg | wind   |          1 |         0.7 |
| model_bg | wind   |          2 |         0.9 |
| model_bg | wind   |          3 |         1.2 |
| ...      | ...    |        ... |         ... |
| model_bg | rpm    |       1250 |           1 |
| model_bg | rpm    |       1350 |         1.5 |
| model_bg | rpm    |       1450 |           2 |
+----------+--------+------------+-------------+

Upvotes: 2

Related Questions