search

EnergySystemsModellingLab / MUSE_2.0

Welcome to the MUSE 2.0 repository
GNU General Public License v3.0
1 stars 0 forks source link

Create new tables for one-to-many fields #71

Closed tsmbland closed 2 weeks ago

tsmbland commented 2 weeks ago

Description

I've updated the database schema to follow a strict relational database format, where all one-to-many relationships are given their own table. See https://lucid.app/lucidchart/eb846b65-cead-4304-bda3-776a5e505da2/edit?view_items=W_bI~y-atE4U&invitationId=inv_2d9f2ff7-5a95-454e-bc64-172fd3c7c34d.

The main changes are summarised in the issue (#55)

I've also modified the csv files accordingly. In addition, I've changed a couple of the columns titled 'value' to be more descriptive, and changed the discount_rate field in processes.csv from percentages to proportions.

The only remaining thing I was unsure about is in the agents.csv file, where on the first line we have two entries for commodity_name separated by a semicolon. We'd need to create another table if we wanted to support this kind of behaviour in a relational database. @ahawkes do we want to do this?

That said, it's possible that we could allow this sort of thing in the csv files (i.e. lists of items separated by semicolons), and have the program internally generate the appropriate tables for the database. In which case, we wouldn't necessarily need the extra tables I've created here.

I don't know what's better. Probably it's simpler to have the csv files match the database format, which is where we're heading with this PR

Fixes #55

Type of change

Key checklist

Further checks

codecov[bot] commented 2 weeks ago

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 81.81%. Comparing base (247f80e) to head (218b641).

Additional details and impacted files ```diff @@ Coverage Diff @@ ## main #71 +/- ## ======================================= Coverage 81.81% 81.81% ======================================= Files 7 7 Lines 77 77 ======================================= Hits 63 63 Misses 14 14 ```

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

ahawkes commented 2 weeks ago

Thanks @tsmbland – good point! For now, I think we want a 1-to-1 relationship from agents to commodities. So we should change the agents.csv to add another agent and assign it GASNAT Would require change in the assets.csv too, so that the new agent owns the current assets that produce GASNAT. I think that would deal with this for now?

From: Tom Bland @.> Date: Wednesday, 12 June 2024 at 14:04 To: EnergySystemsModellingLab/MUSE_2.0 @.> Cc: Hawkes, Adam D @.>, Mention @.> Subject: [EnergySystemsModellingLab/MUSE_2.0] Create new tables for one-to-many fields (PR #71) This email from @.*** originates from outside Imperial. Do not click on links and attachments unless you recognise the sender. If you trust the sender, add them to your safe senders listhttps://spam.ic.ac.uk/SpamConsole/Senders.aspx to disable email stamping for this address.

Description

I've updated the database schema to follow a strict relational database format, where all one-to-many relationships are given their own table. See https://lucid.app/lucidchart/eb846b65-cead-4304-bda3-776a5e505da2/edit?view_items=W_bI~y-atE4U&invitationId=inv_2d9f2ff7-5a95-454e-bc64-172fd3c7c34d.

I've also modified the csv files accordingly. In addition, I've changed a couple of the columns titled 'value' to be more descriptive.

The only remaining thing I was unsure about is in the agents.csv file, where on the first line we have two entries for commodity_name separated by a semicolon. We'd need to create another table if we wanted to support this kind of behaviour in a relational database. @ahawkeshttps://github.com/ahawkes do we want to do this?

That said, it's possible that we could allow this sort of thing in the csv files (i.e. lists of items separated by semicolons), and have the program internally generate the appropriate tables for the database. In which case, we wouldn't necessarily need the extra tables I've created here.

I don't know what's better. Probably it's simpler to have the csv files match the database format, which is where we're heading with this PR

Fixes #55https://github.com/EnergySystemsModellingLab/MUSE_2.0/issues/55

Type of change

Key checklist

Further checks

You can view, comment on, or merge this pull request online at:

https://github.com/EnergySystemsModellingLab/MUSE_2.0/pull/71

Commit Summary

File Changes

(8 fileshttps://github.com/EnergySystemsModellingLab/MUSE_2.0/pull/71/files)

Patch Links:

— Reply to this email directly, view it on GitHubhttps://github.com/EnergySystemsModellingLab/MUSE_2.0/pull/71, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AC37JLLQPOIMTQX6QW57YVLZHBBMVAVCNFSM6AAAAABJGKLGOCVHI2DSMVQWIX3LMV43ASLTON2WKOZSGM2DQNZSGYZTINI. You are receiving this because you were mentioned.Message ID: @.***>

tsmbland commented 2 weeks ago

@ahawkes Done. I've had to make an educated guess about what to call the new agents, so could you double check this?

alexdewar commented 2 weeks ago

On a somewhat related note, the talk of schemas reminded me of this: https://frictionlessdata.io/

There is actually a table schema specification; you can automatically generate them from CSV files too (possibly with a bit of posthoc fiddling). The nice thing is that frictionless can validate any tabular data against the schema, including SQL tables. Not sure of the immediate use for this, but perhaps something to bear in mind?