Hacker News Reader

Comment by SPBS | Hacker News Reader

SQL's most egregious reserved keyword is, in my opinion, "user". Almost every application will have a users table. I gravitate towards singular table names these days [1], and SQL sitting its fat behind on the word "user" means I always have to defer to "users" just for the users table. There is almost no other keyword that I regularly run into conflict with other than "user". I don't even use the "user" keyword in any SQL queries, what a terrible trade off.

[1] I used to prefer plural names, but now I favor singular names because no naming headaches with the plural intricacies of the English language.

View on HN

SQL doesn't say you can't use than name. All that a reserved word means is that you need to quote it to disambiguate.

  CREATE TABLE "user" (
    id int PRIMARY KEY,
    name varchar(50) NOT NULL
  );

In fact, in this respect, SQL is much better than most languages with their `clazz` and `klass` and `func`.

The only other language like this I can think of is Rust. You can write r#keyword to quote a keyword to be used as an identifier.

This solution was devised as a means to add keywords to new editions of the language without breaking code written in the old edition that named this stuff the same as the keyword (and with full interoperability between code from new editions and old editions)

So if some function in some Rust 2015 library was named async (a new keyword introduced in Rust 2018), you can call it like r#async() in newer versions

To join the other commenters, in Nim you use backticks to allow using Nim keywords - this is important for e.g. nice deserialization where some JSON object has a field like `type` which is a keyword in Nim.

https://nim-lang.org/docs/json.html#overview-unmarshalling

In Julia you can also use var”any string” to use any string as a variable name

I'm sure you know this, but this is actually a special case of a much more general rule; in Julia, prefixed string literals are implemented are passed on to macros: https://docs.julialang.org/en/v1/manual/metaprogramming/#met...

Such macros can be defined by libraries or the end user to provide special behavior.

In C# you can prefix with @ to achieve the same thing.

In Scala it's backticks.

   val `val` = 1

in php all variables are prefixed so you don't have to worry about keyword collisions at all

In kotlin you can use backticks to "unkeywordify" a name

Instead of picking a different name for your table, you can quote the table name in double quotes.

("user" is what got me in the habit of doing this unconditionally, to the point where SQL with bare table/column names looks weird to me now.)

I can't. I forgot which stackoverflow comment or blog post mentioned it, but unquoted lowercase identifiers are the most portable and resilient naming convention that work across all database dialects. You don't have to worry about whether your database preserves case, folds everything to uppercase (Oracle) or folds everything to lowercase (Postgres) if you only stick with unquoted lowercase identifiers, and your SQL queries pretty much look the same between all databases as long as you stick with ANSI SQL.

Surely quoted identifiers are the most portable? If you quote everything you get to skip the entire normalisation issue, as well as the keywords issue.

You would think that right, but quoting an identifier is the start of misery. It means everytime you invoke the identifier, you have to quote it. If you don't quote it, you open yourself to bugs. It's a giant footgun. "But I'm quoting lowercase identifiers, so even if I forget the quotes it's fine". It's not. Only Postgres folds unquoted identifiers to lowercase. Oracle and SQL Server fold unquoted identifiers to uppercase. MySQL does the weird thing where it either folds to uppercase or preserves case sensitivity depending on whether you're running it on Windows or Unix (fun!). So not quoting your identifiers now means its behaviour is dependent on what database configuration you're using. It's not worth it. By using only lowercase unquoted identifiers, you can guarantee it behaves identically across every database, because even if they fold to uppercase or fold to lowercase or preserves case your lowercase unquoted identifiers get normalized accordingly to the database's rules and it all works without a hitch. Even if other people bring their weird database-isms like uppercasing identifiers and lowercasing keywords (like my company, sigh), it all works seamlessly. Say no to quoted identifiers, unless you want to saddle your developers with additional burden everytime they write an SQL query that touches the database.

Oh and yeah, every database brings its own opinion on what [quoting] `should` "look" 'like'.

> It means everytime you invoke the identifier, you have to quote it.

Well yes hence “use quoted identifiers for maximum compatibility”. That does not mean “use quoted identifiers except when you don’t want to”.

I don't know how to reply to that except "experience tells me it is miserable to mandate everyone to quote their identifiers when they touch your database". Do you do that?

Yes.

in MySQL backticks are used as quotes for tables/columns

Iv'e inherited a database with both a USER and an ORDER table, both are central to the app and involed in pretty much every query. I feel your pain.

What word would you pick instead of "USER" for users in the context of DBMS ?

I just name it users and accept the minor inconsistency that every table is singular except for the users table (it has the lowest cognitive overhead IMO)

No, I mean the word USER in a statement like below

  CREATE USER user_name   
    [
      { FOR | FROM } LOGIN login_name   
    ]  
  [ WITH <limited_options_list> [ ,... ] ]   
  [ ; ]

It's a shame that the classic style separation of lexing and parsing means to support the command CREATE USER FOR LOGIN WITH means all of those are now magic keywords, instead of only behaving so in the context of a CREATE command.

As for how to name the "user" table, I've used "account". User is a human not a piece of data, Account is the data I have about their association with my service.

Use Human.

Don’t use human. Many systems have API users, and with subscription cycles, humans can wind up with multiple users.

i use user_accounts for homo sapiens living organisms and service_accounts for apis.

What should I use for the felis catus living organisms? They want root accounts, but those already belong to sequoia sempervirens living organisms.

I probably should have gone with accounts and had a species column.

Use human and robots. And UNION every query.

Just kidding. Then you'd eventually need extraterrestrials, sentient_ai, ghosts, and all kinds of extras.

Reply on news.ycombinator.com