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

51–60 of 85 posts

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

#51

Post by nurettin »

irrational wrote: post #24
nurettin wrote: post #17

Earlier quoted context omitted.

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.

A simple example would be "how to select groupwise maximum" where Postgres would respond well to a correlated subquery and Oracle would respond well to a window function and derived table. The engines and optimizations are different.

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

#52

Post by davvolun »

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.

I've been through the relational algebra, I found this to be a nice, quick re-cover. More like a checklist than a full inspection.

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

#53

Post by ysleepy »

daigoba66 wrote: post #23

Also important is adapting to the existing naming conventions of the database, even if you don't like it. (Unless the existing naming conventions cause more trouble that its worth like requiring quoted identifiers or redundant prefixes/suffixes).

I agree, consistency is worth a lot more than using a slightly better convention. It would also create surprises in the sense of "principle of least surprise" which is in general a good guideline while designing APIs schemas and so on.

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

#54

Post by ysleepy »

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

V10R you mean.

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

#55

Post by zzzeek »

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.

I'm sure this SO answer will satisfy you: https://stackoverflow.com/a/4703155/34549

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

#56

Post by always_good »

kbenson wrote: post #35
dspillett wrote: post #26

Earlier quoted context omitted.

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 referri…

Although the alternative allows for a bit of metadata to be encoded in the name. For example, if "id" is always the local item descriptor, you can almost always assume anything ending in "ID" or "_id" is a foreign key, and either the table name or relation is encoded in the prior part of the name. It's a small thing. You'll likely know enough about the tables to be able to know this information anyways. Then again, k…

Agreed. For me, "id" means the PK (or at least just some surrogate key), and "*_id" is an FK.

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

#57

Post by zzzeek »

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 because lot of tooling uses these names to generate UX.

those tools are wrong (and I know roughly which ones those are).

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

it would be: person.id and the foreign key column that refers to it person_thing.person_id. This is much preferable to person.person_id and person_thing.person_person_id.

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

#58

Post by cwbrandsma »

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.

My own view is they should either be all plural or all singular. Just pick one. But I do gravitate to singular because all nouns have naturally consistent singular words. The same cannot be said of plural. e.g. Moose, Cactus, and any other word that ends in an 's'.

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

#59

Post by kbenson »

zzzeek wrote: post #55
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.

I'm sure this SO answer will satisfy you: https://stackoverflow.com/a/4703155/34549

That answer satisfied me initially, but less and less so the more I read. It became very obvious by the end that what is being represented is one "standard", but presented as the only possible correct solution.

It starts off with Yes. Beware of the heathens. Plural in the table names are a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory. I thought the author was being flippant, but it became increasingly obvious that this is a true reflection of their dogmatic view with regard to this topic.

Even if this is the same view I would settle on with all the knowledge, being presented with what is obviously a single perspective with no acknowledgement whatsoever of any positive aspects of alternatives causes me to instinctively distrust quite a bit of the reasoning presented.

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

#60

Post by chias »

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.

That's a fair point, but keep in mind that the author isn't suggesting these are the "correct" ways to write SQL. They're just his ways.

I don't think one standard is necessarily better than another, but the important thing is to have rules. Over the years I've adopted similar rules for myself, and just internal consistency is so much better. I have old projects with table names including: "logs", "log_requests", "log_users", "game_logs", etc., not to mention mixing of plural and singular, to the point where I need to `show tables` before writing any query just to remember what I even called the table I need.

Post reply on HN