I’m probably alone in this, but I dislike naming tables in plural. IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
Yes, you are indeed alone in this
[deleted]
SQL style guide by Simon Holywell
21–30 of 76 posts
Re: SQL style guide by Simon Holywell
#22For comparison, here’s Mozilla’s SQL style guide: https://docs.telemetry.mozilla.org/concepts/sql_style
Thanks for sharing this!
It looks so much cleaner in my eyes.
Plus it uses constant-sized indents, which means less futzing about with spaces and all that.
Also means you can comment out the first select item, something you can't do with the article's approach.
Re: SQL style guide by Simon Holywell
#23> Spaces should be used to line up the code so that the root keywords all end on the same character boundary.
SELECT file_hash
FROM file_system
WHERE file_name = '.vimrc';
SELECT
file_hash
FROM
file_system
WHERE
file_name = '.vimrc';Re: SQL style guide by Simon Holywell
#24I’m probably alone in this, but I dislike naming tables in plural. IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
You left out the where.
SELECT employee.name where role = 'developer'
Vs
SELECT staff.name” where role = 'developer'
Then the plural one reads better
Re: SQL style guide by Simon Holywell
#25Not bad advice. The one about “where possible avoid simply using id as the primary identifier for the table” stood out to me. In the past with multiple ORMs (ya, ya, we all hate them) the default was to map to a column named id. Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used. The best is when multiple peop…
I have found that naming ids as _id helps downstream code when trying to figure out which thing's id you are dealing with. It also helps with avoiding renaming fields when a structure contains multiple ids. I do agree it makes joins more verbose.
This isn't a great idea. Maybe it works for you, but you can alias it, too. The main identification column of a table should just be id. Any foreign keys can have a table prefix.
Please don't prefix the main table id with the table name.
Re: SQL style guide by Simon Holywell
#26For comparison, here’s Mozilla’s SQL style guide: https://docs.telemetry.mozilla.org/concepts/sql_style
I think this guide misses the point that “JOIN” is not a root keyword but a modification on “FROM”. It is more akin to logical “AND”, “OR”, etc.
And this stacks much better once you start doing complex joins especially when you can add parentheses to change where you actually join
FROM a JOIN b JOIN c
FROM a JOIN (b JOIN C)
Re: SQL style guide by Simon Holywell
#27I‘m not a fan of upper case keywords especially when there is also syntax highlighting that gives them a unique color. Shifts my focus away from the rest of the query.
SQL keywords have been upper case for decades. I prefer it because it is faster to match visually. Just like I don’t like uppercase paragraphs because of the same reason
typing them is such a pain though.
Re: SQL style guide by Simon Holywell
#28I've stopped using aliases in the SQL I write and it's dramatically increased the clarify. From this style guide, the aliases section would look like this in my style guide: SELECT first_name FROM staff JOIN students ON students.mentor_id = staff.staff_num;
How do you know which table first_name came from?
Also aliasing can be as much for legibility as anything.
SELECT
ZZ.first_name
FROM staff AS ZZ
INNER JOIN students AS Q ON Q.mentor_id = ZZ.staff_num;Re: SQL style guide by Simon Holywell
#29> Spaces should be used to line up the code so that the root keywords all end on the same character boundary. SELECT file_hash FROM file_system WHERE file_name = '.vimrc'; This style is annoying and I wish it gained less traction. It looks neat but it puts so much burden on the query writer, especially when you modify the query and all of the sudden you need to indent multiple lines just to make them all align. You k…
Also, could uppercase go away and never come back? Please?
Re: SQL style guide by Simon Holywell
#30I’m probably alone in this, but I dislike naming tables in plural. IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
Back In The Day at uni in the early 2000s, we were taught that table names should always be singular, and that's mostly what I've seen in the real world since.
I also think the advice around join table naming is a bit silly. Calling a join table between `cars` and `mechanics` `services` (rather than `cars_mechanics`) does not make the relationship clearer, and only works when the relationships maps to a the real world where the relationship has a commonly used name.
The more I read of this guide, the uglier the SQL is.