SQL style guide

The worst SQL for my eyes is the stuff that has multiple columns, etc. on the same line. Makes diffing stuff 2x as hard and you can't parse as much information. Though maybe I'm just used to that at this point.

Re: SQL style guide

#5

Post by Dowwie » Sun, Oct 09, 2016, 5:27 PM UTC

"Where possible avoid simply using id as the primary identifier for the table."

Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.

Re: SQL style guide

#6

Post by mrgalaxy » Sun, Oct 09, 2016, 5:34 PM UTC

I have only done a little bit of work with SQL. Could someone please go into a bit more detail on a couple of these points for me?

> Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals.

> Where possible avoid simply using id as the primary identifier for the table.

Re: SQL style guide

#7

Post by jawns » Sun, Oct 09, 2016, 5:37 PM UTC

Dowwie wrote: post #5

"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.

I disagree with this style point, and I think the author implicitly acknowledges that `id` should be the standard identifier, because further down he points out that the `_id` suffix should be used for columns that make reference to that identifier.

Re: SQL style guide

#8

Post by andreareina » Sun, Oct 09, 2016, 5:39 PM UTC

Dowwie wrote: post #5

"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.

I think the author was talking about (not) using `id` as the column name, e.g. `user.user_id` instead of `user.id`

Re: SQL style guide

#9

Post by jawns » Sun, Oct 09, 2016, 5:42 PM UTC

The suggestion to right-align the root keywords (e.g. SELECT, FROM, WHERE) and left-align everything else does make the SQL look good.

But I can't think of a single editor that makes this type of alignment formatting easy to do. Can you?

Re: SQL style guide

#10

Post by cema » Sun, Oct 09, 2016, 5:44 PM UTC

An interesting read! I do follow certain rules when writing SQL, and I agree that having them and following them is a good idea. Plenty of what is in the article looks like good advice.

However, these rules do not always appear to be consistent with how the code in (most) other programming languages is written. Consider indentation, for example. The usual approach is to line up those elements of the code which correspond to the same logical level of the flow, whether it is C++, Javascript, or Lisp. So rather than (quoting from the article)

  SELECT file_hash
    FROM file_system
   WHERE file_name = '.vimrc'

I would rather see either

  SELECT file_hash
  FROM file_system
  WHERE file_name = '.vimrc'

or

  SELECT file_hash
      FROM file_system
      WHERE file_name = '.vimrc'

(The difference here is the same as between indented and non-indented braces in C.) I think this is especially useful when we have multiple joins or inner queries.

Many suggested rules are indeed consistent with what I have seen to be accepted as best practices. Such as naming a table in singular (more precisely, giving it the name of what a single row corresponds to), or avoiding the infamous Hungarian notation (pretty much an accepted best practice in most languages that I have seen).

Overall, I think, a good first step towards building best practices.