[dspillett]

> SQL Antipatterns

I'd definitely second that recommendation, both for relative beginners and those of us who have been at it long enough to have learned and forgotten these things a few times over...

https://pragprog.com/book/bksqla/sql-antipatterns or your favourite [e]book seller, for those wanting a copy.

> why this is bad, it then shows you the recommended way of doing it and why this way works better

He also takes the time to mention when the anti-pattern might be OK to use.

[contingencies]

SQL Antipatterns

Saw this thread and just acquired and read this. The book's premise is a great one, I just don't like the execution. Years of my life were dedicated to SQL CRUD and schema evolution before dabbled briefly in NoSQL (meh), random caching systems, then had my aha moment and upgraded to files on unix (awesome caching! great compatibility!) and occasional use of SQLite (easy backup and parallelism! no RDBMS master/slave complexity!).

While there are a few core issues raised, and all struck a chord, I was not a huge fan of the book overall because it was needlessly verbose. I found its authoritative tone grating. Ultimately with so many dialects and projects, SQL style is personal, organizational or project level preference. One issue I felt was undertreated was clarity of syntax. For example, I personally absolutely loathe any use of JOIN as needlessly obtuse cognitive baggage. An untreated issue was (over/mis)use of stored procedures.

[jakearmitage]

How the hell do you deal with locks properly on files? Or each file is a row?

[contingencies]

Right tool for the job. If you have a write-heavy, rapidly evolving dataset with short publishing times, critical contention considerations, and absolute referential integrity requirements, or a huge dataset where memory efficient access is an issue, SQL may be your friend. But try SQLite before a full-blown RDBMS. See also https://yourdatafitsinram.net/

In the real world, however, most systems do not have this type of requirement.

[triangleman]

I'm sorry can you help me understand how to stop using Join? Some examples or articles on the subject? I can't wrap my head around such a concept.

[madno]

Your approach sounds sensible! Do you have some blog posts, pointers which further explains the approach and factors to consider?