How I Write SQL, Part 1: Naming Conventions (2014) • Hacker News Board

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

#61

Post by [deleted] » Mon, Apr 23, 2018, 8:57 PM UTC

always_good wrote: post #39

sbov wrote: post #14

Earlier quoted context omitted.

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

I usually want to reserve the noun like "author" as the embedded record after a join. That way "author_id" is always the key and then "author" is the json_agg joined object that embeds the whole record. Otherwise you're actually introducing ambiguity imo.

[deleted]

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

#62

Post by daphneokeefe » Mon, Apr 23, 2018, 9:01 PM UTC

For naming stored procedures, there were a lot of helpful answers to my question "What is your naming convention for stored procedures?" on StackOverflow a few years ago. https://stackoverflow.com/questions/238267/what-is-your-nami...

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

#63

Post by [deleted] » Mon, Apr 23, 2018, 9:01 PM UTC

always_good wrote: post #39

sbov wrote: post #14

Earlier quoted context omitted.

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

I usually want to reserve the noun like "author" as the embedded record after a join. That way "author_id" is always the key and then "author" is the json_agg joined object that embeds the whole record. Otherwise you're actually introducing ambiguity imo.

[deleted]

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

#64

Post by elchief » Mon, Apr 23, 2018, 9:10 PM UTC

Joe Celko, as well as ISO-11179, tell us to use collective names ("personnel") or plural names ("employees") for tables

As well, fewer keywords are plural, compared to singular, so there's less chance of accidentally using a keyword if you use plurals

Haven't yet seen an "octopus" table in production...

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

#65

Post by qilo » Mon, Apr 23, 2018, 9:12 PM UTC

0xffff2 wrote: post #50

Without a rigorous attempt at justifying each of these rules, I don't find this article particularly useful. For example, can someone link to or provide a formal explanation for why table names should be singular? I actually really wanted to read the full relational algebra rational for that one.

Relation definitions define types. Types have names. Tuple in a relation is an instantiation of the corresponding type. And we use singulars for class/types names in most programming systems (e.g. class Person vs class Persons).

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

#66

Post by [deleted] » Mon, Apr 23, 2018, 9:23 PM UTC

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.

[deleted]

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

#67

Post by swirepe » Mon, Apr 23, 2018, 10:31 PM UTC

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…

Also p13n -> personalization

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

#68

Post by walshemj » Mon, Apr 23, 2018, 10:35 PM UTC

barrkel wrote: post #42

walshemj wrote: post #5

Earlier quoted context omitted.

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

This injunction doesn't really scale across a team. I've spent weeks of my life tuning SQL, to the point of writing SQL generation libraries to effectively override the database optimizer when it consistently makes poor decisions in specific use cases. But I don't expect the rest of the team to know SQL as well as I do. When I'm writing or generating SQL, I don't really care much what the naming convention is. If it'…

I am not talking about the level of a Guru (sample quote "oh Dijkstra was my first boss") - but being able to handle the standard sorts of queries.

maybe we should have a fizbuzz for SQL as a filter :-(

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

#69

Post by jcadam » Mon, Apr 23, 2018, 11:14 PM UTC

I know a reasonable amount of SQL but am by no means a database guru. Naturally, this makes me the local database expert at my current place of employment :/

The other devs' eyes glaze over when I say things like 'stored procedures' and 'trigger functions.' Bah.

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

#70

Post by danso » Tue, Apr 24, 2018, 1:32 AM UTC

elchief wrote: post #64

Joe Celko, as well as ISO-11179, tell us to use collective names ("personnel") or plural names ("employees") for tables As well, fewer keywords are plural, compared to singular, so there's less chance of accidentally using a keyword if you use plurals Haven't yet seen an "octopus" table in production...

I did a Google search for elaboration on this and apparently there is disagreement that ISO-11179 says this at all:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/d5f2f...

> Yes, this is the same version as I found, but the closest thing I could find to addressing table names in the paper itself was an "Object Class name", something like an OOP Class or something you'd find in a UML diagram, but not really the same as a table name, and in any case all the examples were singular.

> Was actually kinda hoping Celko would deign to comment on this himself as he seems to be the chief proponent of the "collective identifiers as specified by ISO 11179" meme.