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:
- 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. |
- 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?
CodePudding user response：
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 |
---------- -------- ------------ -------------