5 ms·
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 thes
by SPBS 3y ago
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.
- reportgunner 3y agoWhat word would you pick instead of "USER" for users in the context of DBMS ?
- SPBS 3y agoI 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)
- reportgunner 3y agoNo, I mean the word USER in a statement like below CREATE USER user_name [ { FOR | FROM } LOGIN login_name ] [ WITH <limited_options_list> [ ,... ] ] [ ; ]
- yencabulator 3y agoIt'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.
- dpifke 3y agoInstead 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.)
- SPBS 3y agoI 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.
- masklinn 3y agoSurely quoted identifiers are the most portable? If you quote everything you get to skip the entire normalisation issue, as well as the keywords issue.
- asddubs 3y agoin MySQL backticks are used as quotes for tables/columns
- SPBS 3y agoYou 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'.
- mirekrusin 3y agoUse Human.
- pyuser583 3y agoDon’t use human. Many systems have API users, and with subscription cycles, humans can wind up with multiple users.
- joshxyz 3y agoi use user_accounts for homo sapiens living organisms and service_accounts for apis.
- timeinput 3y agoWhat 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.
- martinflack 3y agoUse human and robots. And UNION every query. Just kidding. Then you'd eventually need extraterrestrials, sentient_ai, ghosts, and all kinds of extras.
- paulddraper 3y agoSQL 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`.
- nextaccountic 3y agoThe 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
- recursive 3y agoIn C# you can prefix with @ to achieve the same thing.
- paulddraper 3y agoIn Scala it's backticks. val `val` = 1
- saled 3y agoIn kotlin you can use backticks to "unkeywordify" a name
- andyferris 3y agoIn Julia you can also use var”any string” to use any string as a variable name
- hexane360 3y agoI'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/#meta-non-standard-string-literals https://docs.julialang.org/en/v1/manual/metaprogramming/#met... Such macros can be defined by libraries or the end user to provide special behavior.
- progre 3y agoIv'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.