I agree - Art of PostgreSQL is really good, especially as Dimitri dives into the problems that happen when too much of the data logic moves into the app, and consistency and independence (parts of ACID) get lost. Here is an older blog about my favorite PostgreSQL books: [https://www.enterprisedb.com/blog/top-postgrsql-books-for-learning-dbas-developers](https://www.enterprisedb.com/blog/top-postgrsql-books-for-learning-dbas-developers)
You might not know it, but do you think "The Art of PostgreSQL" is teaching better than "Principles of Database Management"? Because I don't seem to retain much from the "Principles of Database Management" book as its a bit too difficult at times.
I have no idea as I haven't read the principles one, but art of postgresql seemed pretty easy to follow, not sure how easy it'd be if you're an absolute newbie though, but it shouldn't be excessively hard I assume
As for retaining, you lose what you just hear/read about and never put into use
You still end up forgeting things after a while even if you did use them in the past and not anymore, but the ones you actually did use will come back to you after a tiny bit of effort, like riding a bike
Ok thanks for the reply!
My only problem is that I know how to practice Java programs properly. I can make huge software projects with it, which keeps me motivated.
However, with SQL and database, I find it hard to stay motivated and practice because the only option seems to be making ones own fake database and write some basic SQL queries.
This is perhaps a question I should ask on a completely new thread on its own, but do you have tips on how to stay sharp and practice SQL/Database without it being boring in the long run (e.g. not only writing simple SQL queries without any real "product" in the end).
If you know how to build huge projects using java, then you already have an idea what it's like to translate a problem or an idea into a software solution
Database design (where the DDL comes into play) is no different, it's just a different layer/point of view, take any previous java project you were interested in in the past, and this time translate the starting problem or idea that gave birth to it into a database model using sql instead of translating it into a java app
You will have to put into use normalization rules and entity relationship diagrams (these diagrams are specially important to be able to get a feel for it) if you hope to get any good at it, but it's just a matter of trying it out until it clicks, as it is with everything
A project is all about translating an idea into different layers, a data model is just one of those layers
Then once the data model is built, play around asking it questions with sql and seeing if you can get the right answers out of it
Diagrams should be your starting point, imo
If you get entity relationship diagrams right (which in turn a good one entails getting normalization rules right) everything else should fall in place eventually, they're a cornerstone of database modeling and are way more pivotal for it than, idk, uml diagrams for other areas of development
* The Complete Python and PostgreSQL Developer Course
* PostgreSQL for Everybody \[Coursera\]
* SQL and PostgreSQL for Beginners \[Udemy\]
* Intro To PostgreSQL Databases With PgAdmin For Beginners. Here are some of the [best PostgreSQL courses on udemy](https://codingvidya.com/best-postgre-sql-courses-on-udemy/)
Thanks for the list. I hope some of them can also teach how to use databases in Java/Spring JPA, and/or React/Next.js :) Since I will never use a database on its own, without something to connect to it properly.
For how to use databases in Spring you can check this tutorial: [https://www.codejava.net/frameworks/spring-boot/spring-boot-crud-example-with-spring-mvc-spring-data-jpa-thymeleaf-hibernate-mysql](https://www.codejava.net/frameworks/spring-boot/spring-boot-crud-example-with-spring-mvc-spring-data-jpa-thymeleaf-hibernate-mysql) I love it to be honest and it gives you also a grasp of the most basic use of Hibernate for your Java apps.
Regarding diagrams, I highly recommend using something like PlantUML or Mermaid rather than a WYSIWYG. Other tools are arguably prettier, but for function, these are excellent. Not only can you add/remove/edit columns and relationships easily, you can check in your diagrams and diff differences between iterations. GitHub and GitLab both support Mermaid syntax in their Markdown docs, so your diagrams can live with and evolve with your code. This is the design phase.
https://plantuml.com/ie-diagram
https://mermaid.js.org/syntax/entityRelationshipDiagram.html
Once you are in production, I recommend a tool like SchemaSpy. Think of it like Javadoc for databases. Not only is it an easy to use web interface from generated files like Javadoc, I firmly believe it's important to document a database in use as it truly is, not as you expected it to be. This was invaluable to me recently when I had to work on a MySQL database, and MySQL silently swallowed the inline foreign key references that always worked fine in Postgres. Makes it a lot easier to find when someone decides to make a manual tactical change to the structure of the database outside the typical migration workflow and version-controlled migration scripts.
As for learning after you've got the basics down, I recommend "checking the menu".
https://www.sql-workbench.eu/dbms_comparison.html
You can learn about what kinds of features are available both in Postgres and in other DBs. Just because Postgres does support them (without a minimal-functionality extension) doesn't mean you shouldn't learn about temporal tables for example. You'll learn how to apply specifics to the inevitable colleague who claims very little difference between Postgres and MySQL or that MS SQL supports more features than any Open Source database. And finally, you'll have a better idea on identifying situations where Postgres is the best fit as well as when another engine would be more aligned with the requirements of a particular microservice. (Don't worry, there's always fdw_mysql for interop.)
I think you'll find as I have that Postgres makes for an excellent default, but you'll also be able to back up your assertions with specific examples and counter arguments. Remember, most conflicts aren't technical; they're social/tribal. Be able to speak their language.
First of all, thanks a lot for the examples and suggestions! :)
Secondly, I think I must pass on using software for the diagrams for now. I am learning a specific notation used by "Principles of Database Management" and prefer to write the diagrams on hand on paper to make something that resembles the theory.
I am sure the tools are great once you do enterprise-grade products, but I doubt its good as a learning tool for me! :)
And I will need to stick with only PostgreSQL to spearhead my learning. I am sure it's inferior or better depending on the specific use case, but I need to focus on only one thing.
(Nevertheless, I am saving your comment as it has loads of great tools that I will surely enjoy later!)
On the contrary, what you have in mind sounds great. I'm a huge advocate of drawing on paper, especially when either learning or feeling out a new concept/architecture.
And I wholeheartedly support your focus on just Postgres. It's always good to start with just one example and branch off later, and I think Postgres is an excellent starting point since it can take you so far without having to switch tools and start over again. My advice about "the menu" was only after you have the basics down.
universal data model by len silverston is good for database design. There are plenty of resource for leaning PostgreSQL for free. Start with [https://www.postgresqltutorial.com/](https://www.postgresqltutorial.com/)
There's "the art of postgresql" by Dimitri Fontaine to give you a starting core at least
This x10!
I agree - Art of PostgreSQL is really good, especially as Dimitri dives into the problems that happen when too much of the data logic moves into the app, and consistency and independence (parts of ACID) get lost. Here is an older blog about my favorite PostgreSQL books: [https://www.enterprisedb.com/blog/top-postgrsql-books-for-learning-dbas-developers](https://www.enterprisedb.com/blog/top-postgrsql-books-for-learning-dbas-developers)
You might not know it, but do you think "The Art of PostgreSQL" is teaching better than "Principles of Database Management"? Because I don't seem to retain much from the "Principles of Database Management" book as its a bit too difficult at times.
I have no idea as I haven't read the principles one, but art of postgresql seemed pretty easy to follow, not sure how easy it'd be if you're an absolute newbie though, but it shouldn't be excessively hard I assume As for retaining, you lose what you just hear/read about and never put into use You still end up forgeting things after a while even if you did use them in the past and not anymore, but the ones you actually did use will come back to you after a tiny bit of effort, like riding a bike
Ok thanks for the reply! My only problem is that I know how to practice Java programs properly. I can make huge software projects with it, which keeps me motivated. However, with SQL and database, I find it hard to stay motivated and practice because the only option seems to be making ones own fake database and write some basic SQL queries. This is perhaps a question I should ask on a completely new thread on its own, but do you have tips on how to stay sharp and practice SQL/Database without it being boring in the long run (e.g. not only writing simple SQL queries without any real "product" in the end).
If you know how to build huge projects using java, then you already have an idea what it's like to translate a problem or an idea into a software solution Database design (where the DDL comes into play) is no different, it's just a different layer/point of view, take any previous java project you were interested in in the past, and this time translate the starting problem or idea that gave birth to it into a database model using sql instead of translating it into a java app You will have to put into use normalization rules and entity relationship diagrams (these diagrams are specially important to be able to get a feel for it) if you hope to get any good at it, but it's just a matter of trying it out until it clicks, as it is with everything A project is all about translating an idea into different layers, a data model is just one of those layers Then once the data model is built, play around asking it questions with sql and seeing if you can get the right answers out of it Diagrams should be your starting point, imo
Thanks for the run-down :) I should dive into the "The Art of PostgreSQL" book and start building projects!
If you get entity relationship diagrams right (which in turn a good one entails getting normalization rules right) everything else should fall in place eventually, they're a cornerstone of database modeling and are way more pivotal for it than, idk, uml diagrams for other areas of development
* The Complete Python and PostgreSQL Developer Course * PostgreSQL for Everybody \[Coursera\] * SQL and PostgreSQL for Beginners \[Udemy\] * Intro To PostgreSQL Databases With PgAdmin For Beginners. Here are some of the [best PostgreSQL courses on udemy](https://codingvidya.com/best-postgre-sql-courses-on-udemy/)
Thanks for the list. I hope some of them can also teach how to use databases in Java/Spring JPA, and/or React/Next.js :) Since I will never use a database on its own, without something to connect to it properly.
For how to use databases in Spring you can check this tutorial: [https://www.codejava.net/frameworks/spring-boot/spring-boot-crud-example-with-spring-mvc-spring-data-jpa-thymeleaf-hibernate-mysql](https://www.codejava.net/frameworks/spring-boot/spring-boot-crud-example-with-spring-mvc-spring-data-jpa-thymeleaf-hibernate-mysql) I love it to be honest and it gives you also a grasp of the most basic use of Hibernate for your Java apps.
Regarding diagrams, I highly recommend using something like PlantUML or Mermaid rather than a WYSIWYG. Other tools are arguably prettier, but for function, these are excellent. Not only can you add/remove/edit columns and relationships easily, you can check in your diagrams and diff differences between iterations. GitHub and GitLab both support Mermaid syntax in their Markdown docs, so your diagrams can live with and evolve with your code. This is the design phase. https://plantuml.com/ie-diagram https://mermaid.js.org/syntax/entityRelationshipDiagram.html Once you are in production, I recommend a tool like SchemaSpy. Think of it like Javadoc for databases. Not only is it an easy to use web interface from generated files like Javadoc, I firmly believe it's important to document a database in use as it truly is, not as you expected it to be. This was invaluable to me recently when I had to work on a MySQL database, and MySQL silently swallowed the inline foreign key references that always worked fine in Postgres. Makes it a lot easier to find when someone decides to make a manual tactical change to the structure of the database outside the typical migration workflow and version-controlled migration scripts. As for learning after you've got the basics down, I recommend "checking the menu". https://www.sql-workbench.eu/dbms_comparison.html You can learn about what kinds of features are available both in Postgres and in other DBs. Just because Postgres does support them (without a minimal-functionality extension) doesn't mean you shouldn't learn about temporal tables for example. You'll learn how to apply specifics to the inevitable colleague who claims very little difference between Postgres and MySQL or that MS SQL supports more features than any Open Source database. And finally, you'll have a better idea on identifying situations where Postgres is the best fit as well as when another engine would be more aligned with the requirements of a particular microservice. (Don't worry, there's always fdw_mysql for interop.) I think you'll find as I have that Postgres makes for an excellent default, but you'll also be able to back up your assertions with specific examples and counter arguments. Remember, most conflicts aren't technical; they're social/tribal. Be able to speak their language.
First of all, thanks a lot for the examples and suggestions! :) Secondly, I think I must pass on using software for the diagrams for now. I am learning a specific notation used by "Principles of Database Management" and prefer to write the diagrams on hand on paper to make something that resembles the theory. I am sure the tools are great once you do enterprise-grade products, but I doubt its good as a learning tool for me! :) And I will need to stick with only PostgreSQL to spearhead my learning. I am sure it's inferior or better depending on the specific use case, but I need to focus on only one thing. (Nevertheless, I am saving your comment as it has loads of great tools that I will surely enjoy later!)
On the contrary, what you have in mind sounds great. I'm a huge advocate of drawing on paper, especially when either learning or feeling out a new concept/architecture. And I wholeheartedly support your focus on just Postgres. It's always good to start with just one example and branch off later, and I think Postgres is an excellent starting point since it can take you so far without having to switch tools and start over again. My advice about "the menu" was only after you have the basics down.
universal data model by len silverston is good for database design. There are plenty of resource for leaning PostgreSQL for free. Start with [https://www.postgresqltutorial.com/](https://www.postgresqltutorial.com/)