You are not alone at all, I also prefer singular names for the same reason. I reserve plural names for the rare cases where the single row of a table actually contains information about more than one item, which is usually when I'm doing something denormalized or non-relational e.g. CREATE TABLE user_settings ( user_id INT, settings_data JSON)
Then do as you please without the that if you are dealing with a user or leave it plural if multiple…
And if we’re talking personal preference I really dislike caps in reserved words in sql, even before highlighting was everywhere it still just feels archaic for no good reason
Not 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 people have worked on the project over the years and the columns are a combo of camel, snake, camel_snake, all UPPER / lower. Must look at the table definitions or ERD every time you want to write some non-trivial query. So having a consistent style guide is better than having any one specific style guide. This would be a good starting point and adjust with your team as needed.
> 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
However, using 'table_name.table_name_id' and then having another table with an FK that references it with the same name i.e. 'table_2.table_name_id' allows you to use a shorthand 'USING' clause instead of 'ON' in databases that support it.
I have found that naming ids as <thing>_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.
If the table names are all short one word like that, they are already basically aliases. What do you do when you end up on some legacy project (you did not make the schema) where_the_table_names_look_like_this? Seems some sort of alias might be more appropriate. Also, when you are writing longer / sub queries or using Common Table Expressions it’s impossible to not use an alias.
This is really good advice and the coding style (alignment) matches what I came to without any real guidance when I was learning SQL 20+ years ago. The only thing I slack on, is uppercasing the keywords. I hate switching case so much. But, I will fit the coding style of the codebase I am working on when it comes to that.
IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
join users as user on user….
Then do as you please without the that if you are dealing with a user or leave it plural if multiple…
And if we’re talking personal preference I really dislike caps in reserved words in sql, even before highlighting was everywhere it still just feels archaic for no good reason
2018 (59 points, 16 comments) https://news.ycombinator.com/item?id=17924917
2016 (257 points, 147 comments) https://news.ycombinator.com/item?id=12671667
2015 (16 points, 10 comments) https://news.ycombinator.com/item?id=9941150
Also means you can comment out the first select item, something you can't do with the article's approach.
I do agree it makes joins more verbose.
From this style guide, the aliases section would look like this in my style guide:
Shifts my focus away from the rest of the query.
Just like I don’t like uppercase paragraphs because of the same reason