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

11–20 of 85 posts

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

#11

Post by cryptonector »

matte_black wrote: post #6
sytelus wrote: post #2

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

> For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql Do not do this in Postgres, it will be a pain in the ass since you will have to use quotes around everything.

Right. PG has appropriated square brackets for array notations, so you really just have to use double-quotes.

At least PG tries really hard to not add new reserved keywords, which means you mostly don't have to worry about your schema element names possibly conflicting with new keywords in future releases.

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

#12

Post by jermaustin1 »

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

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

#13

Post by 11235813213455 »

matthewmacleod wrote: post #8
walshemj wrote: post #5

Earlier quoted context omitted.

Don't use an ORM is the answer just put in the effort to lean SQL

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.

it's better only if you replace "appropriate" with more insights

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

#14

Post by sbov »

sytelus wrote: post #2

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming.

I assume you mean you just use PersonID as the foreign key. This oftentimes introduces ambiguity into what the relationship actually is. I prefer names that describe the actual relationship (e.g. author, owner, approver, etc) rather than letting other people guess what it is.

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

#15

Post by rockostrich »

btilly wrote: post #9
barrkel wrote: post #3

You should follow the conventions that make life easier in the rest of your tooling. The fact is, you're probably going to be issuing more SQL via abstractions like ORMs or querying libraries than raw SQL. If you need to work against the grain of those libraries to map your model, what upside are you getting? If most of your data is queried via ActiveRecord, for example, you should use plural table names.

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 create some very poor queries compared to what would be expected and ended up writing parts of those queries in raw SQL, but those cases are pretty uncommon when most of the logic for the application is simple gets/updates with filter/join conditions.

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

#16

Post by hluska »

matthewmacleod wrote: post #8
walshemj wrote: post #5

Earlier quoted context omitted.

Don't use an ORM is the answer just put in the effort to lean SQL

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 hood if/when everything goes to shit!! :)

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

#17

Post by nurettin »

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…

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

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

#18

Post by mtone »

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'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 time, instead of run-time or analysis-time, thanks to added scoping.

- Works in any schema, no matter what naming conventions are followed.

- Keeps working as the query becomes more complex with multiples table aliases or self-joins, and similar field names appearing in the set.

- Better expresses intent. Sure JOIN defaults to INNER JOIN, but writing "INNER JOIN" shows that you genuinely expect any row not matching your condition to be removed from the result set.

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

#19

Post by hluska »

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".

I don't agree with that. Once you understand the basics of building performant databases/queries, that knowledge will persist across databases. There are small differences in syntax and features, but any remotely competent person should be able to pick those up while they go, provided they understand what happens under the hood when they execute SQL.

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

#20

Post by [deleted] »

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 .

[deleted]
Post reply on HN