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

51–60 of 151 posts

Re: SQL style guide

#51

Post by calpaterson »

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've had lots of problems with surrogate primary keys where people don't put enough constraints on the natural key and then duplicates (sometimes slightly different) get in.

Unless you're using innodb, primary key is not special, it's just another index. If you decide to use surrogate keys you also need to enforce the natural key too (if possible).

There are also some nice performance advantages you can get from natural keys with covering composite indices in the case where you only want the key. But in general a table can have multiple keys in all but the highest levels of normalisation.

Re: SQL style guide

#52

Post by Animats »

Further suggestions:

- If a field should never be NULL, put NOT NULL in the field definition. If a field should be unique, put UNIQUE in the field definition. This improves both correctness and index performance.

- A useful format for CREATE TABLE is the one you get back from SHOW CREATE TABLE.

Re: SQL style guide

#53

Post by cyberferret »

Glad to see I conform to the guide in most places, but one thing I have been doing for nearly all 35 years of my programming career is to prefix my SQL views with `vw` so that I can tell immediately in my queries when I am referencing a view that may be made up of other queries.

Re: SQL style guide

#54

Post by exceptione »

throwaway745234 wrote: post #45
exceptione wrote: post #39

Earlier quoted context omitted.

The debate about singular/plural naming for relations comes up frequently. In my opinion, it stems from the fact that English has only 2 forms for all the possible grammatical cases. It has only a different form for the singular and plural cases, and thats what people concentrate on. I propose to take a step back. There are quite some languages that have different forms for the nominative, accussative, dative and gen…

Ian Mackinnon, is that you? ( http://stackoverflow.com/a/3894235/1163893 )

Nope, i am not. But good catch, since his comment on SO made me originally see the light. :)

Re: SQL style guide

#55

Post by no_protocol »

I have often seen commas placed at the start of each line when columns are being listed. I'm a little surprised this guide doesn't follow that convention. It would fit nicely with the established pattern of lining everything up:

  SELECT first_name
       , last_name
       , email_address
    FROM users
Can anyone speculate why it's not done this way in the guide? I think the reasoning behind it was to make later modification easier because adding or removing the last field required modifying two lines.

Re: SQL style guide

#56

Post by ZenoArrow »

Stasis5001 wrote: post #47

Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…

Aliasing tables is very useful. Aside from giving shorter names and helping you to be explicit when selecting fields, they also end up being useful for working with IntelliSense (or the equivalent in the tool you're using). Plus, you can make them memorable enough (e.g. in the first example you shared, could use 'S' for staff and 'ST' for students) for the query you're working on and shortening the query code makes it more readable. For all the reasons above, aliases are one of the first things I'll add when writing a new SQL query.

As for the indentation after FROM, I prefer to put an indent here too. Whilst you're right that INNER JOIN would have the same priority, LEFT JOIN wouldn't, and I wouldn't want to use different indenting styles for different types of joins.

That said, I don't agree with everything in this style guide, especially...

* Do... "Try to use only standard SQL functions instead of vendor specific functions for reasons of portability."

* Avoid... "Descriptive prefixes or Hungarian notation such as sp_ or tbl."

With the standard functions, it strikes me that's optimising the wrong thing. How many times are you likely to switch database engines? Such actions are very rare, especially for teams with good grounding in RDBMS'. What's more important to me is readability of the code, and there's plenty of useful non-standard SQL for improving readability. If we want both we should be pushing for updated standards to be applied across the board.

As for the second point, it's useful to know whether you're looking at a table/view/function/stored procedure/trigger from the name alone. Whilst the tbl prefix seems superfluous, other database objects should have a descriptive prefix in the name IMO.

Re: SQL style guide

#57

Post by jackmaney »

mmgutz wrote: post #15

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.

Agreed. Every time I write SQL with keywords in all caps, I feel like I'm screaming at the query parser.

Re: SQL style guide

#58

Post by ZenoArrow »

no_protocol wrote: post #55

I have often seen commas placed at the start of each line when columns are being listed. I'm a little surprised this guide doesn't follow that convention. It would fit nicely with the established pattern of lining everything up: SELECT first_name , last_name , email_address FROM users Can anyone speculate why it's not done this way in the guide? I think the reasoning behind it was to make later modification easier be…

Yeah, basically it's a good practice to start field name rows with commas as it makes it quicker to comment them out as you're tweaking the SQL code (though there's no harm in putting multiple fields on a single row, especially if they come from related tables).

Re: SQL style guide

#60

Post by walshemj »

Stasis5001 wrote: post #47

Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…

its not renaming its an alias if you have a complex sql statement having aliases just makes it easy to get you head around the code.
Post reply on HN