6 ms·

I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered sce

by ganonm 7y ago

I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better. I think 'learning from how not to do something' is a really powerful pedagogical technique that should be more widespread.

GrumpyNl 7y ago
Free download can be found here http://www.r-5.org/files/books/computers/languages/sql/style/Bill_Karwin-SQL_Antipatterns-EN.pdf http://www.r-5.org/files/books/computers/languages/sql/style...
- ganonm 7y ago
  Please do not share links to pirated copies of resources such as this. I can't stop you from pirating it yourself, but it shouldn't be actively encouraged.
  - alharith 7y ago
    Just out of curiosity, why? Pirating traditional media, journals, and movies and entertainment is actively encouraged on this site, with popular sites and software for doing so frequently referenced, and it is never flagged. Seems like a double standard. (Full disclosure, I agree with your objection and believe piracy is wrong in any case, but seems like we should be consistent with the application of the rules)
    - dspillett 7y ago
      There is a difference between talking generally about piracy and its ramifications, even encouraging it as some would be doing, and directly linking to unlicensed copies of material. Even if an individual did believe that piracy is perfectly acceptable, direct linking still puts the site under risk of moving into the cross-hairs of law enforcement.
      - alharith 7y ago
        I do not agree there is a material difference in that case. Whether or not the site is under the crosshairs is also irrelevant to the point. Besides, as I mentioned in my comment, direct links to hubs that contain pirated material and direct links containing suites of tools specifically used for pirating such as DRM removal on ebooks and video players, and bypasses for news organizations and scientific publications are also frequently directly linked.
        dspillett 7y ago
        I've seen links to hubs taken down like this link to a specific item, so if there is a discrepancy there is isn't consistent. Either a few slip through the cracks and you've seen them or I'm only seeing the other portion for some reason. Mentions of key hubs like pirate bay don't count as linking to them IMO though might effectively be so as the mention might clue some to their existence. Discussing the matter completely without mentioning those places/services would not be practical. Tools for removing DRM have valid uses. Minority uses, sure, but still valid. Whether removing it for your own transcoding & platform shifting purposes, rather than distribution, is morally OK is a complex discussion (I say yes, lawyers will almost certainly say no!), but if you allow that then the valid use list grows. Bypassing news filters is usually a case of exploiting them giving different responses in different cases to improve, for example, their Google rank. This is against the engines policies. You can of course argue that "two wrongs don't make a right" but then equally one wrong doesn't either! I'm not sure if the scientific publications matter is the same or not, I'll have to submit to your expertise/experience there.
  - dillonmckay 7y ago
    Could this be a citizen of Antigua and Barbuda?
- orsenthil 7y ago
  I don't think this is a legal (US laws) copy of the book. It is still under copyright and publisher has not released it as Free download.
leetrout 7y ago
I tried that in a class I taught. The students were very frustrated and considered it a waste of time. I agree it’s a FANTASTIC way to learn. I was very disheartened I didn’t effectively communicate that to the students.
- hnrodey 7y ago
  Just a guess, your students probably didn't have the frame of reference or perspective to appreciate good design nor bad design. Someone who's been in the trenches for several years, they'd probably get A LOT out of this technique as they would have many experiences to pull from.
- dspillett 7y ago
  Beginners often lack the experience to appreciate "critical thinking" based learning. At first they just want (need?) to know the steps to get something right, especially when they are still not particularly fluent in the very basics. Once you've got some experience (which usually means getting things wrong a few times, seeing wrongness promoted to production because there isn't time to refactor, and having to fix the wrongness later once more layers or wrong are piled on top) you appreciate this sort of analysis much more as it helps you get things right first time more often, and/or helps you spot the actual problem (rather than chasing symptoms) in more complex situations.
  - firethief 7y ago
    It's like humans have to learn how to extract features before they can make efficient use of straightforward supervised learning...
  - Adutude 7y ago
    Also the "right" way often changes, in technology, and if you only learned the "right" way, when a better way comes out, you'll miss it because you only ever learned the "right" way and it's all you know.
  - sitkack 7y ago
    > Beginners often lack the experience to appreciate "critical thinking" based learning. I don't think that is it. _Beginners_ being the critical word. Most learning is part of a negative feedback loop, if we only ever succeeded we wouldn't know why we succeeded, failure has such bad connotations in our society that it blinds students from deeply understanding a subject. Maybe replace it with experience? Back to the subject of _Beginners_, we really should be teaching students from a very young age, philosophy, cognitive science, and epistemology. They should embrace experience, it shouldn't be up to the database schema instructor to teach both data modeling and learning by failure. Students should be fully versed in how to care and feed their brains by the time they arrive in the GPs class.
LessDmesg 7y ago
I've started reading, and already the second anti-pattern, "Naive trees", is something I've been dealing with at work recently. Hey, how'd he know?!? Cool book.
dspillett 7y ago
> 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 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 7y ago
  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.
  - madno 7y ago
    Your approach sounds sensible! Do you have some blog posts, pointers which further explains the approach and factors to consider?
  - triangleman 7y ago
    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.
  - jakearmitage 7y ago
    How the hell do you deal with locks properly on files? Or each file is a row?
crtlaltdel 7y ago
THANK YOU!!!! This is a fantastic reference, and helps me put some teeth behind my instincts when it comes to SQL.
robodale 7y ago
Thanks for the SQL Antipatterns book recommendation. Just ordered it.

by ganonm 7y ago

GrumpyNl 7y ago

Free download can be found here http://www.r-5.org/files/books/computers/languages/sql/style/Bill_Karwin-SQL_Antipatterns-EN.pdf http://www.r-5.org/files/books/computers/languages/sql/style...

ganonm 7y ago
Please do not share links to pirated copies of resources such as this. I can't stop you from pirating it yourself, but it shouldn't be actively encouraged.
- alharith 7y ago
  Just out of curiosity, why? Pirating traditional media, journals, and movies and entertainment is actively encouraged on this site, with popular sites and software for doing so frequently referenced, and it is never flagged. Seems like a double standard. (Full disclosure, I agree with your objection and believe piracy is wrong in any case, but seems like we should be consistent with the application of the rules)
  - dspillett 7y ago
    There is a difference between talking generally about piracy and its ramifications, even encouraging it as some would be doing, and directly linking to unlicensed copies of material. Even if an individual did believe that piracy is perfectly acceptable, direct linking still puts the site under risk of moving into the cross-hairs of law enforcement.
    - alharith 7y ago
      I do not agree there is a material difference in that case. Whether or not the site is under the crosshairs is also irrelevant to the point. Besides, as I mentioned in my comment, direct links to hubs that contain pirated material and direct links containing suites of tools specifically used for pirating such as DRM removal on ebooks and video players, and bypasses for news organizations and scientific publications are also frequently directly linked.
      - dspillett 7y ago
        I've seen links to hubs taken down like this link to a specific item, so if there is a discrepancy there is isn't consistent. Either a few slip through the cracks and you've seen them or I'm only seeing the other portion for some reason. Mentions of key hubs like pirate bay don't count as linking to them IMO though might effectively be so as the mention might clue some to their existence. Discussing the matter completely without mentioning those places/services would not be practical. Tools for removing DRM have valid uses. Minority uses, sure, but still valid. Whether removing it for your own transcoding & platform shifting purposes, rather than distribution, is morally OK is a complex discussion (I say yes, lawyers will almost certainly say no!), but if you allow that then the valid use list grows. Bypassing news filters is usually a case of exploiting them giving different responses in different cases to improve, for example, their Google rank. This is against the engines policies. You can of course argue that "two wrongs don't make a right" but then equally one wrong doesn't either! I'm not sure if the scientific publications matter is the same or not, I'll have to submit to your expertise/experience there.
- dillonmckay 7y ago
  Could this be a citizen of Antigua and Barbuda?
orsenthil 7y ago
I don't think this is a legal (US laws) copy of the book. It is still under copyright and publisher has not released it as Free download.

leetrout 7y ago

I tried that in a class I taught. The students were very frustrated and considered it a waste of time. I agree it’s a FANTASTIC way to learn. I was very disheartened I didn’t effectively communicate that to the students.

hnrodey 7y ago
Just a guess, your students probably didn't have the frame of reference or perspective to appreciate good design nor bad design. Someone who's been in the trenches for several years, they'd probably get A LOT out of this technique as they would have many experiences to pull from.
dspillett 7y ago
Beginners often lack the experience to appreciate "critical thinking" based learning. At first they just want (need?) to know the steps to get something right, especially when they are still not particularly fluent in the very basics. Once you've got some experience (which usually means getting things wrong a few times, seeing wrongness promoted to production because there isn't time to refactor, and having to fix the wrongness later once more layers or wrong are piled on top) you appreciate this sort of analysis much more as it helps you get things right first time more often, and/or helps you spot the actual problem (rather than chasing symptoms) in more complex situations.
- firethief 7y ago
  It's like humans have to learn how to extract features before they can make efficient use of straightforward supervised learning...
- Adutude 7y ago
  Also the "right" way often changes, in technology, and if you only learned the "right" way, when a better way comes out, you'll miss it because you only ever learned the "right" way and it's all you know.
- sitkack 7y ago
  > Beginners often lack the experience to appreciate "critical thinking" based learning. I don't think that is it. _Beginners_ being the critical word. Most learning is part of a negative feedback loop, if we only ever succeeded we wouldn't know why we succeeded, failure has such bad connotations in our society that it blinds students from deeply understanding a subject. Maybe replace it with experience? Back to the subject of _Beginners_, we really should be teaching students from a very young age, philosophy, cognitive science, and epistemology. They should embrace experience, it shouldn't be up to the database schema instructor to teach both data modeling and learning by failure. Students should be fully versed in how to care and feed their brains by the time they arrive in the GPs class.

LessDmesg 7y ago

I've started reading, and already the second anti-pattern, "Naive trees", is something I've been dealing with at work recently. Hey, how'd he know?!? Cool book.

dspillett 7y ago

> 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 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 7y ago
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.
- madno 7y ago
  Your approach sounds sensible! Do you have some blog posts, pointers which further explains the approach and factors to consider?
- triangleman 7y ago
  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.
- jakearmitage 7y ago
  How the hell do you deal with locks properly on files? Or each file is a row?

crtlaltdel 7y ago

THANK YOU!!!! This is a fantastic reference, and helps me put some teeth behind my instincts when it comes to SQL.

robodale 7y ago

Thanks for the SQL Antipatterns book recommendation. Just ordered it.