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

91–100 of 151 posts

Re: SQL style guide

#91

Post by Treffynnon »

mattmanser wrote: post #30

Try to use only standard SQL? Stopped reading right there. There is no way you can port SQL Server to MySQL to Oracle to PLSQL without rewriting virtually every single query. They're all too different. So why bother? Write idiomatic code that other programmers used to that engine will understand. Not only that but different engines like different things. That blazingly fast nested subquery in MS SQL will become a mas…

This seems to be a simple misunderstanding.

Try - in this context means where it doesn't make sense (for performance, readability, etc reasons) then don't follow the guide. Follow it where possible and be mindful of when you deviate that you're adding to tech debt.

Re: SQL style guide

#92

Post by 6nf »

alistairbayley wrote: post #88
6nf wrote: post #66

Earlier quoted context omitted.

I don't know if you've ever worked with MS SQL Server in production but for us it's been the most amazing piece of technology in our stack for the past 15 years. SQL Server is in the top 3 best things to be published by Microsoft ever. (The other 2 are probably Excel and Flight Simulator)

What's so amazing about it, when compared to the other SQL DBMS's? (e.g. postgresql, oracle, sybase, db2, etc) Especially as it's just a re-badged Sybase...

You still think of MSSQL as 're-badged Sybase'?

You know that was literally DECADES ago right?

Re: SQL style guide

#93

Post by Treffynnon »

comboy wrote: post #17
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.

Basically this is the answer - besides that bit about acquiescing to the demands of an ORM.

Re: SQL style guide

#94

Post by Treffynnon »

dghf wrote: post #14

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?

Thinking multi-column indexes here.

Re: SQL style guide

#95

Post by Treffynnon »

VLM wrote: post #35
mrgalaxy wrote: post #6

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.

The first one is just poorly stated but is correct in spirit. Use the most inclusive and flexible name allowed by the business logic without being overly generic. Some would say thats the natural term, I guess. The first example of using "employee" as a term invokes murphy's law that the company will hire their first contractor or intern the first week the software ships, resulting in much confusion, wait you've got…

I am totally lost when it comes to your comments on the second item. It seems you're trying to say that surrogate keys are easier to find or perhaps guess.

The indexes set out against a table can generally be accessed with a query making it very easy to work out what existing index suits your use case best.

Re: SQL style guide

#96

Post by Treffynnon »

webbore wrote: post #31
jbverschoor wrote: post #23

I really like his indentation. I'm gonna use it. Most of it has been my style for a long time.

If you're writing a lot of ad-hoc queries, I prefer to have keywords on their own line and also put commas at the start of the following line vs end of the previous. It lets you comment stuff out easier as you experiment. In a production query the only hard rule should be, "Please be consistent!"

Putting the comma before doesn't actually make commenting out columns easier - it just moves the problem to the other end of the list. Just try commenting out the first column in your SQL and you'll see what I mean.

Re: SQL style guide

#97

Post by Treffynnon »

mjw_byrne wrote: post #67

Trouble with this kind of thing is that if you make it general enough to apply to most situations you can't really say anything (too many exceptions) and if you get opinionated then your guide doesn't apply to a wide class of use cases. Stuff: 1. Advice like "Use consistent and descriptive identifiers and names" - is general programming advice, not SQL style advice, and is so bland as to go without saying (would anyo…

I think a lot of this comes down to what appears to be a simple misunderstanding.

Avoid - in this context means that where it does not make sense (for performance, readability, etc reasons) then don't follow the guide. Follow it where possible and be mindful of when you deviate that you're adding to tech debt.

Re: SQL style guide

#98

Post by NickNameNick »

walshemj wrote: post #60
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.

Only if they're meaningful.

SQL isn't BASIC, and we aren't limited to x (1,2,4, etc) character variable names. Why not just use the existing meaningful name? They aren't that hard to type, and you might even be able to use autocompletion, depending on environment.

I've spent far too much time re-writing queries to remove obfuscating aliases to accept a blanket statement that they make it easier to get your head around the code.

Re: SQL style guide

#99

Post by NickNameNick »

webbore wrote: post #29
andreareina wrote: post #8

Earlier quoted context omitted.

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

ON second reading I think you are correct. I agree on this point then, as it helps when joining through a denormalized table that references multiple PK ids, ex: I think: user_id = role_id is more readable than the likely alternative: u.id = r.id

it also lets you use the join foo using(foo_id) syntax, which I quite like

Re: SQL style guide

#100

Post by justinlardinois »

NickNameNick wrote: post #98
walshemj wrote: post #60

Earlier quoted context omitted.

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.

Only if they're meaningful. SQL isn't BASIC, and we aren't limited to x (1,2,4, etc) character variable names. Why not just use the existing meaningful name? They aren't that hard to type, and you might even be able to use autocompletion, depending on environment. I've spent far too much time re-writing queries to remove obfuscating aliases to accept a blanket statement that they make it easier to get your head aroun…

In my line of work I often end up working with queries where you'll have a handful of JOINs and need to SELECT a dozen or more columns. In those cases writing out staff.foo or students.bar gets old really fast.

I agree that s1 and s2 are bad though, as I said in another comment.

Post reply on HN