Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL style guide

sqlstyle.guide

Post reply on HN
Print view View original

11–20 of 151 posts

Re: SQL style guide

#11

Post by dotancohen »

Though mostly good advice, I definitely disagree with this:

  DONT:  
  Plurals—use the more natural collective term where possible instead. For
  example staff instead of employees or people instead of individuals.
I like naming my tables as plurals so that foreign keys to the table rows can have a name that relates to the table name. For instance, having column `Orders.employee` as an FK to an `Employees` record makes much more sense than having column `Orders.employee` as an FK to a `Staff` record.

Re: SQL style guide

#12

Post by johnnyb9 »

dotancohen wrote: post #11

Though mostly good advice, I definitely disagree with this: DONT: Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals. I like naming my tables as plurals so that foreign keys to the table rows can have a name that relates to the table name. For instance, having column `Orders.employee` as an FK to an `Employees` record makes much…

Not to mention with an ORM tool you'll have an Employee object, and probably call your collection employees.

Which is better?

foreach (var employee in staff) {

}

or

foreach (var employee in employees) {

}

Re: SQL style guide

#13

Post by rspeele »

jawns wrote: post #9

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?

emacs C-u M-x align-regexp can do this, if you give it a regexp matching the keywords you wish to right-align and enter -1 when prompted for "group to modifiy". It would still be a hassle though.

Re: SQL style guide

#14

Post by dghf »

Can anyone explain what this means?

> 1. The key should be unique to some degree.

If it's talking about primary keys (which it seems to be), it should be unique, full stop.

If it's not, where does uniqueness come in? Is it actually talking about indices, which, if I understand correctly, should have a wide spread of values (without any of them necessarily being unique) if they're to be useful?

Re: SQL style guide

#15

Post by mmgutz »

Still dont get why sql uses all caps for keywords. It's one of the few languages that has it as a best practice. I can read C/JavaScript/go just fine without all caps.

I do it anyway to conform.

Re: SQL style guide

#16

Post by dahdum »

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 too, using table.id is easier to type and consistent when creating joins. It also encourages (forces) to reference the fields with tables prefix.

Re: SQL style guide

#17

Post by comboy »

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.

Maybe what author means is that from the db design standpoint, composite keys are used much less often than they could be. Very often unique table row can be defined by some other column or a pair, on which you will probably put an index anyway.

But that's just playing devil's advocate. In general I think ids are fine because ORMs play nice with them and FKs are simple.

Re: SQL style guide

#18

Post by echelon »

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.

Generated unique tokens are better than auto increment IDs. They can be created in a distributed multi-master setup, and they don't expose how many of a given record type you have to 3rd parties when exposing primary keys or feeds via APIs.

Re: SQL style guide

#19

Post by dahdum »

cema wrote: post #10

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 corres…

It's good advice, but the formatting shown is not conducive to quick editing imo.

Here's your huckleberry:

  SELECT
  	fs.id,
  	fs.file_hash 
  FROM
  	file_system fs,
  	other_table ot 
  WHERE
  	file_name = '.vimrc' AND
  	fs.id = ot.file_system_id

Re: SQL style guide

#20

Post by stymaar »

johnnyb9 wrote: post #12
dotancohen wrote: post #11

Though mostly good advice, I definitely disagree with this: DONT: Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals. I like naming my tables as plurals so that foreign keys to the table rows can have a name that relates to the table name. For instance, having column `Orders.employee` as an FK to an `Employees` record makes much…

Not to mention with an ORM tool you'll have an Employee object, and probably call your collection employees. Which is better? foreach (var employee in staff) { } or foreach (var employee in employees) { }

> and probably call your collection employees.

Really ? IMHO this is a bad practice : it makes the code les readable and breaks the auto-completion ...

Post reply on HN