If you are a novice or even if just a bit rusty with relational databases I recommend:
- Grab a good book or two. The Art of PostgreSQL is one. There are _many_ others. Take a bit of time to find out what suits you. I typically like reading a more theoretical one and a more practical one (with actionable, concrete advice, actual code in it and so on).
- Get a nice tool/IDE/editor plugin that can interactively work with databases, create them, query them etc. Almost all of the editor utilities that you expect from general purpose programming also applies to programming with SQL.
- PostgreSQL is a very powerful, primarily relational database, but there is very little it cannot do. For example it is perfectly feasible to implement a document (JSON) store with it, and convenient to boot. There are also great extensions such as for temporal tables.
- There is a ton of advice, discussion etc. to find, especially around data modelling. Word of caution: Some of it is religious, at least slightly. The book I mentioned above too to a certain degree, but it is still a very good book. Realize that a relational DB can give you a ton of stuff in a performant, declarative manner. But not everything should live in the DB, nor is everything relational in the strictest sense. Be pragmatic.
Source: I've been diving deeper into this topic since a few years and still am. The more I learn and are able to apply, the more I understand why the relational paradigm is so dominant and powerful.
- Grab a good book or two. The Art of PostgreSQL is one. There are _many_ others. Take a bit of time to find out what suits you. I typically like reading a more theoretical one and a more practical one (with actionable, concrete advice, actual code in it and so on).
- Get a nice tool/IDE/editor plugin that can interactively work with databases, create them, query them etc. Almost all of the editor utilities that you expect from general purpose programming also applies to programming with SQL.
- PostgreSQL is a very powerful, primarily relational database, but there is very little it cannot do. For example it is perfectly feasible to implement a document (JSON) store with it, and convenient to boot. There are also great extensions such as for temporal tables.
- There is a ton of advice, discussion etc. to find, especially around data modelling. Word of caution: Some of it is religious, at least slightly. The book I mentioned above too to a certain degree, but it is still a very good book. Realize that a relational DB can give you a ton of stuff in a performant, declarative manner. But not everything should live in the DB, nor is everything relational in the strictest sense. Be pragmatic.
Source: I've been diving deeper into this topic since a few years and still am. The more I learn and are able to apply, the more I understand why the relational paradigm is so dominant and powerful.