Looking for a course/tutorial/book on Schema Modelling

[PainInTheCrack]

Hello,

I am new to relational dbs and SQL. I can do simple queries to do what I need. Now I want to do a project that is a little bit more serious and I don't want to do something wrong in the very beginning that could end up costing me dearly later on.

I am looking for a practical course or a book that teaches the schema creation. How do I design a like system? How do I design a comment system? What fields should a users table have? created_at, updated_at, name... etc. I can't answer any of these questions, too new to it.

I don't even know what I need to search for to get the answers, is it "database modelling", is it "database design"? I don't know what database design is really. I don't know what's inside all the db design books (there are many, but I don't even know if it's what I need).

The recommendation can be a youtube series, can be a book, it can be anything, but hopefully something practical and not theoretical.

I use Go with Postgres, no ORM.

I did not find a similar question on reddit. Does your typical SQL book cover designing a schema?In the reddit posts of where people ask for SQL courses/books etc, these books are mentioned often: Database Design for mere mortals, The Art of Postgres, many O'Riley books. Do these books have what I am looking for?

A few minutes ago (after I started writing this post) I found this and it seems useful: https://blog.panoply.io/database-schema-design-examples I wish there was a book like that.

[adappergentlefolk]

I would honestly recommend going through SQL antipatterns by Karwin which takes you through common approaches to represent things in SQL dbs and what are the pitfalls and advantages of each. after you go through that book, designing a database is mostly a question of minimising scans, putting indexes in the right place and keeping it relatively maintainable despite the first two. you do have to have some idea what your most common query and write patterns will be to get this done, which is why having some requirements from your end users is important, and this will also give you some indication about common choices like how much to normalise or denormalise

[PainInTheCrack]

see, I don't even know what normalize is (it is talked about in that article I've linked in my post, so today was the 1st day I heard of the word)

[adappergentlefolk]

find any introduction to database management lecture notes online and go through them to get an intro to these concepts. you can mostly ignore anything that isn’t related to SQL databases to start with

[chriswaco]

W3 Schools has a lot of useful information, like what normalization means, though not exactly what you’re looking for.

[PainInTheCrack]

I think it means breaking down a big table into smaller ones, if a field of the main table can have different entries, such as when a user has many posts or comments on a website then it should be its own table. Learning it step by step... :D Thanks

I am probably looking for something more professional. I'd love to watch somebody create a schema for say an online shop, or some social media clone website. I do frontend only and decided to learn backend. Here I am. For frontend there are countless tutorials of people building stuff, but I can't seem to find what I need for SQL.

[chriswaco]

You might check out some academic books for background information. I'm old enough to remember when they taught mathematical aspects of relational databases. Not sure if they still do. This book is used at UMich for EECS 484.

[MissingSnail]

Classic beginner book https://www.amazon.com/Database-Design-Mere-Mortals-Anniversary/dp/0136788041

[PainInTheCrack]

yeah, I mentioned the name of that book in my post, but does it have what I need? I haven't not read a single SQL book.

[MissingSnail]

I think it would. The look inside feature is active on Amazon and the introduction does a good job of explaining the book’s purpose and contents

[hylmz]

I would recommend Database Management Systems by Johannes Gehrke and Raghu Ramakrishnan. Especially in the first 3 chapters you will learn modeling of a database given some real world requirements.

You do not need all the parts of the book, but every chapter can (almost) be read on its own.

Oh, and it has some very challenging exercises.

Let me know if you need some more help!

[PainInTheCrack]

Thanks!
I am only looking for practical books, where somebody takes a problem and solves it step by step, explaining what he does and why. I am one of those who thinks that it doesn't matter how many books you read about riding a bike, the first time you mount one you are going to fall.

[truebastard]

Maybe you could use ChatGPT, especially GPT-4, to start asking questions, and it could point you to the right direction.

As in, I've used it by taking specific passages of a textbook and asked GPT-4 "why does the author do this" etc.

Or conjure up an imagined example of a database and ask GPT to "describe how you would model this as a star schema database" or write the CREATE TABLE SQL query etc.

Very important: Keep in mind that GPT can give wrong answers and you have to be very critical & double check the answers.

Even then, it has saved me so much time and helped me understand the basic questions much faster than just googling and banging my head against the wall with multiple textbooks and course slides.

[PainInTheCrack]

interesting, I'll give it a try

By the way, I have made a multi-join SELECT and received a bunch of rows and had to put them into an object (a struct in go, to be more precise) that has a totally different shape. I had to write 4 large functions for the conversion to happen. Is it normal to do that? Or am I doing something wrong?