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

How I Write SQL, Part 1: Naming Conventions (2014)

launchbylunch.com

Post reply on HN
Print view View original

21–30 of 85 posts

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#21

Post by btilly »

rockostrich wrote: post #15
btilly wrote: post #9

Earlier quoted context omitted.

It is true that most of my SQL goes through ORMs. However my most complicated SQL is always constructed by hand. Furthermore note the point about how often applications get rewritten against the same database. You should not assume that future code will use the same ORM that you are using now.

It depends on the situation. If the queries you're writing deal are heavily integrated with application models and logic, then using the ORM is probably the way to go (especially with the ORM does some client side caching and other optimizations). Of course, if you start to see that the ORM's queries underperform compared to raw SQL then you should check to see what SQL the ORM is spitting out. I've seen SQLAlchemy c…

It does depend on the situation.

My experience has been that transactional code is usually best written with an ORM, and complex reporting code winds up better with SQL.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#22

Post by pbreit »

mtone wrote: post #18
jermaustin1 wrote: post #12

Earlier quoted context omitted.

I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id

I'd bring it a little further and would write: SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id I have: - Aliased each table and prefixed every field names with their table alias in my join conditions. - Explicited the JOIN type. The above: - Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL. - Increases the likelihood of getting an error at parse tim…

As a newbie, this makes more sense to me:

  SELECT * FROM person, team WHERE person.team_id=team.id
(no unnecessary aliasing, no weird JOIN phrasing)

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#24

Post by irrational »

nurettin wrote: post #17
hluska wrote: post #16

Earlier quoted context omitted.

I'm north of 40 now, so while I both love and agree with your advice, I'd like to add a caveat. Even if you are lucky enough to be able to use an ORM for your entire career, you will still be well served by learning and truly understanding SQL. In that sense, I'd argue that an ORM is only appropriate if your understanding of SQL and database design are strong enough that you can understand what's happening under the…

Solving problems with Postgres is wildly different than solving them with mssql informix or Oracle. So I would say "there is no SQL".

In what way are they wildly different? I spent 15 years working in Oracle and the last 2 years working in Postgres. They are slightly different (Postgres adheres to the standard much more closely than Oracle), but 95% of my knowledge of SQL transferred from one to the other.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#25

Post by irrational »

walshemj wrote: post #4

How come I have never in a multi decade career come across "i18n". I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables. Also never really brought into adding the type as part of a name - your type is already defined in your schema.

You've worked on English only applications?

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#26

Post by dspillett »

jermaustin1 wrote: post #12
meritt wrote: post #7

I personally prefer person_id to be the primary key name (instead of id ) in both the person table and any table which has it as a foreign key. One reason is for join syntax: select * from person join team_member using (person_id) The other reason is person_id now unambiguously refers to the same field regardless if we're looking at the PK or a FK. It's always person_id .

I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id

I prefer to keep ID column names descriptive even if it does lead to repetition like Person.PersonID. That way columns that identify a person always carry the same name and you are never left guessing what a more anonymous "ID" refers to or fall into one of a couple of traps where the parser disambiguate one in a way you were not expecting (though this is also caught by consistently using two+ part names when referring to columns, which I also prefer to do). It is particularly useful if the same entity is joined into a query multiple times with different aliases.

There are cons, of course. This is a matter that divides people and when working with other people's projects you have to ignore your own preference and follow the "local" convention.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#27

Post by mtone »

pbreit wrote: post #22
mtone wrote: post #18

Earlier quoted context omitted.

I'd bring it a little further and would write: SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id I have: - Aliased each table and prefixed every field names with their table alias in my join conditions. - Explicited the JOIN type. The above: - Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL. - Increases the likelihood of getting an error at parse tim…

As a newbie, this makes more sense to me: SELECT * FROM person, team WHERE person.team_id=team.id (no unnecessary aliasing, no weird JOIN phrasing)

I can understand not using aliasing in a simple query, but I'd recommend against using these non-ANSI joins (deprecated syntax). What you're writing can be interpreted as either:

SELECT * from person CROSS JOIN Team WHERE person.team_id=team.id

SELECT * from person INNER JOIN Team ON person.team_id=team.id

That they happen produce the same result in a query is practically just luck. Changing the join from INNER to LEFT OUTER is also much easier than managing (+)'s in the WHERE clause, once you're used to it.

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#28

Post by matthewmacleod »

hluska wrote: post #16
matthewmacleod wrote: post #8

Earlier quoted context omitted.

That is a weird middlebrow dismissal of a response with essentially no value to anybody. Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. This is much better advice.

I'm north of 40 now, so while I both love and agree with your advice, I'd like to add a caveat. Even if you are lucky enough to be able to use an ORM for your entire career, you will still be well served by learning and truly understanding SQL. In that sense, I'd argue that an ORM is only appropriate if your understanding of SQL and database design are strong enough that you can understand what's happening under the…

I absolutely agree with that!

An ORM is an abstraction of a concrete system. For very simple use cases, that’s fine. For anything even slightly complex, the ORM becomes a tool that you can use to enhance code readability, or reliability, or modularity. But it’s essential to know what is going on underneath before using that abstraction, much like most other systems. There are too many times I’ve witnessed a less experienced developer build a shockingly expensive n+1 query using an ORM to doubt that :)

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#29

Post by kbenson »

cryptonector wrote: post #10
walshemj wrote: post #4

How come I have never in a multi decade career come across "i18n". I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables. Also never really brought into adding the type as part of a name - your type is already defined in your schema.

Really? I have. In some circles (e.g., the IETF), i18n is an ancient acronym. People who've worked on operating systems (e.g., OS X, Solaris, RHEL, whatever) have to deal with L10N (localization). G11N (globalization) is I18N + L10N. And then there's a11y: accessibility. This is all about making user interfaces accessible for people with low or no vision, low or no hearing, difficulty typing, and so on. There are gen…

> G11N -> I18N and L10N.

That's a new one to me, but makes sense. I've been lucky enough to have heard of i19n and l10n for years (almost decades, and this point) but not had to deal with it much beyond tracking down a string in some open source webapp I was patching before deploying.

> can't we all just use ISO-8601?!

Preach on. I sometimes find myself filling out date fields in paper forms in YYYY-MM-DD without thinking. The elementary school my kids attend probably thinks I'm a weirdo. I know my wife does...

Re: How I Write SQL, Part 1: Naming Conventions (2014)

#30

Post by jermaustin1 »

mtone wrote: post #18
jermaustin1 wrote: post #12

Earlier quoted context omitted.

I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id

I'd bring it a little further and would write: SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id I have: - Aliased each table and prefixed every field names with their table alias in my join conditions. - Explicited the JOIN type. The above: - Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL. - Increases the likelihood of getting an error at parse tim…

I'm not a fan of short aliases. They obscure what you are attempting to do.

Personally I write very little SQL anymore (the ORM does that for me unless I need performance), but the only time I use aliasing is when the same table is joined multiple times.

Like:

SELECT Mother.Name, Father.Name, Child.* FROM Person Child JOIN Person Mother ON Mother.Id = Child.MotherId JOIN Person Father ON Father.Id = Child.FatherId

That said, this naming convention also obscures what Person.FatherId points to without looking at the FKeys. So Take that with a grain of salt.

Post reply on HN