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

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

#41

Post by cryptonector » Mon, Apr 23, 2018, 6:41 PM UTC

dvh wrote: post #37

cryptonector wrote: post #10

Earlier quoted context omitted.

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…

V10N --> Velociraptor T15X --> Tyrannosaurus Rex D11S --> Dilophosaurus B11S --> Brachiosaurus T9S --> Triceratops S9S --> Stegosaurus

I think we have evidence that 12 characters is where should start the shortening.

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

#42

Post by barrkel » Mon, Apr 23, 2018, 6:42 PM UTC

walshemj wrote: post #5

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.

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's consistent, then SQL is easier to write. Consistency is more important than the specifics of any conventions.

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

#43

Post by barrkel » Mon, Apr 23, 2018, 6:47 PM UTC

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.

Hand-constructed SQL, though, can cope with any naming scheme. And I'd argue a consistent naming scheme, using tooling as a forcing function, is better than inconsistencies you'd get with a big team of people writing their own SQL.

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

#44

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

debaserab2 wrote: post #36

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…

Aliasing a query that short just obfuscates intention. It might be necessary for some larger queries, or joining the same table multiple times, but there is a readability cost you are paying for it. For example: JOIN team_member ON team_member.person_id = person.id It's completely self evident what is being joined without the need to trace back to the table aliases. Pretty much all of your advice is premature optimiz…

[deleted]

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

#45

Post by arez » Mon, Apr 23, 2018, 6:53 PM UTC

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.

Person.PersonID stutters, you already know that you query on the person table you don't have to repeat it again. Naming it ID is the same consistency

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

#46

Post by slobotron » Mon, Apr 23, 2018, 6:54 PM UTC

zo1 wrote: post #32

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 think he's referring to using "[Table]ID" as the primary key on [Table]? So now you have to join with the Person table on Person.PersonID from your local column PersonID. I much prefer the other way around: Table.ID with foreign keys being "TableID".

I find "[Table]ID" to be bit more readable in joins. I.e.

    SELECT * FROM table1 INNER JOIN table2 USING (table1_id);

vs

    SELECT * FROM table1 INNER JOIN table2 ON (table2.table1_id = table1.id);

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

#47

Post by iblaine » Mon, Apr 23, 2018, 7:20 PM UTC

> Avoid reserved words

Glad you cleared this up for the rest of us.

FWIW, naming conventions are like opinions. Everyone has them, and they usually differ from person to person. The best naming convention is a consistent naming convention. Also, naming conventions differ greatly by environment. A group of SQL Server engineers are going to have different standards than those of people working on mysql.

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

#48

Post by rodelrod » Mon, Apr 23, 2018, 7:29 PM UTC

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'm with you but in my experience it's a lost fight. Most of the projects I come across these days follow the id convention.

The advantages of using person_id are even more obvious in multiple joins, such as a star schema where you can using(person_id) all the things, reducing both the typing and the cognitive load.

I suspect that if this convention was more pervasive, programmers would be a little bit less afraid of diving into SQL.

Well, at least we're settling with some convention, so it's not all bad.

EDIT: typo

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

#49

Post by default-kramer » Mon, Apr 23, 2018, 7:36 PM UTC

arez wrote: post #45

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.

Person.PersonID stutters, you already know that you query on the person table you don't have to repeat it again. Naming it ID is the same consistency

I like "Person.PersonID" because then "alias.PersonID" will produce an error if "alias" does not have a "PersonID" column. If every table has an "ID" column then "alias.ID" pretty much never fails, even if you typed the wrong alias - you just end up joining on the wrong thing and getting the wrong result set.

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

#50

Post by 0xffff2 » Mon, Apr 23, 2018, 7:55 PM UTC

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.