Joe Celko's SQL for Smarties

What Hacker News says

1. IsinlorSep 2019
   If you put unnecessary abstraction in front of perfectly good solution the you will end up with problems.

   Object oriented programing is about clearly expressing business logic. There is no complex business logic in dumping big tables of data.

   So, it's not about ORMs, it is object oriented programing that is poor fit for doing reporting.

   Relational databases, declarative SQL, functional pure programming are good solutions for reporting.

   And you should certainly learn SQL if you want to do any applications with ORM or without. I recommend Joe Celko's SQL for Smarties: Advanced SQL Programming.

   Object oriented languages shine when there is a need to create a precise language that facilitates fast and robust communication between domain experts and developers. In situations where you have little data, but a lot of intricate logic. This is where relational databases are simply no good.

   Relational algebra and SQL is not a very expressive natural language. SQL limits your vocabulary to 4-5 verbs unless you start writing procedural code in procedures etc. but SQL is not a good procedural language. Relational databases are a solution to specific technical problems of scale, execution speed, atomicity, consistency, isolation, and durability (ACID). They excel at that, not at communicating intention.

   You should use ORMs (preferably Data Mapper) if your goal is to solve problem of expressing complex domain specific logic. You use relational databases in that situation because they just work. Data Mapper allows you to isolate your domain model from tricky technical aspects of data storage like indexing and/or not corrupting files during power outage. ORM works very well as long as you will actually be able to ignore technical aspects of speed etc. in your domain model.

   You can do the data mapping, querying, migrations, and all this technical cruft manually with a handwritten SQL if you want, but SQL certainly will not address very well the goal of creating an expressive domain model that facilitates robust communication between developers and business experts.

   So, given that we addressed the elephant in the room, some other points:

   Dual schema dangers: "I much prefer to keep the data definition in the database and read it into the application."

   That's perfectly good solution, if you have a lot of data and amount of logic related to data is minimal. If you have little data and a lot of logic you will not be able to store data definition exclusively in database whether you are using ORM or not. Even if you just store SQL queries in your code, then you do store schema structure with SQL, just not explicitly, but implicitly.

   Data migrations, rolling release etc. are tricky whatever you do. I use my ORM to dump me SQL that is needed to move structure from point A to point B, since ORM knows the schema it can do that, and then I manually adjust it as needed to massage data etc.

   Identities: Not sure if I follow here. It seems like you have issues with auto incremented ids. Auto incremented ids with ORMs are annoying, indeed. My advice is to use UUID generated in the code, then you will have no need to hit database. Also a side note here: if you use ORMs, do not use anything that has any real world meaning for ids. Use UUID, so that you are sure that no domain expert will want to mess with that.

   Transactions: The same situation as with speed and migrations. Getting technical details of transactions is hard whether you use ORM or not. You can use stored procedures, but I'm curious what will you do e.g. when you will need external REST API to get in sync or to copy user files to assure full transaction from the user perspective. There is no magic bullet, it's just hard.

   Show morethread
2. icc97Apr 2018
   Joe Celko's SQL for Smarties.

   Tufte's Visual Display of Quantitive Information.

   Donald Norman's Design of Everyday Things.

   thread
3. jpitzDec 2016
   That is a great resource.

   Here are some I've used:

   Inside Microsoft SQL Server 2008 T-SQL Querying: T-SQL Querying https://books.google.com/books?id=FZlCAwAAQBAJ&printsec=fron...

   Good resource for designing high performing queries and investigating performance problems.

   PostgreSQL 9.0: High Performance https://books.google.com/books?id=OWOAu0GcsqoC&printsec=fron...

   Greg lays out a great foundation for doing performance analysis that is largely applicable to any rdbms platform.

   Optimizing Oracle Performance https://books.google.com/books?id=D7ElCwAAQBAJ&printsec=fron...

   Another great book that explains the process of approaching and solving performance problems.

   Joe Celko's SQL For Smarties https://books.google.com/books?id=i-19BAAAQBAJ&printsec=fron...

   Everyone writing SQL should read Joe.

   Show morethread
4. sauce71Jul 2016
   Joe Celko's "SQL for Smarties" ... and then the other SQL books by Celko
   thread
5. jacques_chesterFeb 2013
   Well ... really, just read a good pair of textbooks on each side of the spectrum. Date's Databases and Kimball's The Data Warehouse Toolkit are good.

   Edit: actually, maybe not Date. It's up to you. It's good, but it's controversial because he's not a fan of SQL and so he uses his own language.

   The one I used in uni was Ramakrishnan & Gehrke's Database Management. It was OK but there's a certain amount of at-the-time trendy bullshit that to me detracts from a focus on relational databases for their own sake.

   Edit 2: and Joe Celko's SQL for Smarties contains good oil on the relational paradigm.

   Show morethread
6. svennekJan 2013
   (disclamer: I have more than a decade experiences in doing SQL)

   I would say Mr. Joe Celko's "SQL for smarties" (and basically anything he has written (look him up for references)....

   I have had the opportunity to talk to mr. Celko, and he added (as I asked him what he thought was the best database-book) the "Manga Guide to Databases" (seriously!), I am reading it now (but I am only in the second chapter), and it seems very good...

   If you need a good database which honors the standard behaviour closely and has super documentation (and very clean codebase), take a look at Postgresql. The fact that is is free and open source (bsd licensened) does not hurt at all :)

   Show morethread