Is my database ERD well designed



  • 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 https://pandas.pydata.org/ 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!



  • 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).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2