0

I'm creating a PostgreSQL database for datasets, which should store historical and future data of weather forecasts and train timetables. I'm wondering if my ERD is good enough for a database which would perform well and is nicely structured.

My idea is to use Python Data Analysis Library to create dataframes. Dataframes should have all the data from a desired date and show all the train routes and weather from a desired station.

I'm new to SQL and databases overall and I'm feeling like the current design I have is lacking something or is not very well designed for performance.

train traffic and weather forecast erd

So my idea is that I want to get answers to questions like:

  1. What was the rain amount yesterday at station X between 12.00 and 13.00?
  2. What is train Y's route for tomorrow?

I'm wondering if my database with this ERD is capable of storing all this information for all these questions. I hope this clarified my goal with my database.

So, if someone could give me any advice or tips I would appreciate it very much!

Improve this question
1
  • Your database architecture is good, you did it right
    – Francesco Mantovani
    Commented Mar 24, 2022 at 7:55

1 Answer 1

Reset to default
0

It depends on the queries you will run. Primary keys should always be bigint, not integer, and I'd use text rather than varchar(50).

Improve this answer
1
  • 1
    integer is big enough for over 2 billion rows… more than enough for 99.99% of databases.
    – Colin 't Hart
    Commented Mar 24, 2022 at 6:52

Your Answer

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

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