Use singular nouns for database table names
teamten.com
teamten.com
If you have a "users" table, you either have to name the column "users_id", which is incorrect (because per row it's the id of a single user), or you have to use mental cycles to de-pluralize the name (or even worse, try to remember whether you called it "users_id" or "user_id" on a case by case basis).
You don't need to prefix your table column names with the name of the table.
users.user_id should just be users.id
Etc.
In the "widgets" table, you need to refer to either "the id for the user" or "the id to use in the users table" so both user_id and users_id are perfectly reasonable choices if the other table is called "users".
I honestly did not think about foreign key column names when I was typing, so thanks for pointing it out.
I'm always a fan of making foreign key column names more descriptive than just the table name. Widgets may have a user_id but it probably makes more sense to have an owner_id.
orders.user_id
orders.users_fk?
Then you know exactly what that column is there for. It was intended to be a foreign key for the users table.
I think "orders.user_id" is fine too of course, I'm not going to argue against that. But isn't it just the tiniest bit more ambiguous?
Edit: There's probably also always better names than just the table name with id, too.
For example orders might have more than one fk to the users table, depending on the type of site. Something like ebay might have both buyer_id and seller_id on a single order.
Large queries, many tables, many FKs will make it easier to see why these conventions exist. Patterns like "users JOIN things USING (user_id) JOIN stuff USING (user_id, thing_id) ...".
An id is a different thing from an ID. Look up "id, ego, superego."
Laravel gets this right. Singular models, plural table names. Built in rules to pluralize, or override the defaults and add your own table name. (EDIT: Rails gets this right too)
> Strictly speaking, we’re not naming a table, we’re naming a relation.
I don't think so. We're naming the collection. The relation is the foreign key. that's why you would see `user_id`, not `users_id`. EDIT: I now understand that the author is referring to the relational algebra side of things here. I don't think that changes my skepticism of the argument though, because very rarely is it useful to discuss these things in terms of the mathemetics with your coworkers. Ambiguity around whether you're talking about a User (relation) and User (tuple representing a user) and a User relation(ship to other data) makes this go sideways in my opinion.
The argument here that you would end up with 'addresss' is silly as you can also quickly handle that with a new inflection and it rarely even happens in practice.
Also, to be pedantic, rails handles this just fine by default: irb(main):001:0> "address".pluralize => "addresses"
1. Attribute => Column
2. Tuple => Row
3. Relation => Table
That's what he's talking about. I'm sure a debate about nomenclature will ensue.
For my part, this is not of any significance. I will store a tuple of data representing a user in a `users` table. If I'd stored it in a `user` table, then that wouldn't make my life hard either, but I'd prefer the plural to represent the fact that it's the name of a relation which has multiple tuples (each of which represents data for a singular user).
defmodule User do
use Ecto.Schema
schema "users" do # table name
field :name, :string
field :age, :integer, default: 0
field :password, :string, redact: true
has_many :posts, Post
end
end
In rails if there is a table name prefix involved, you will have no idea
that depends on your interpretation of "strictly speaking". As a (loose) implementation of the Relational Model[1], a table is indeed a Relation. In practical terms however - particularly with an ORM in play - a table stores a collection of entities, with relationships (not relations) implemented as foreign keys as you say.
> that's why you would see `user_id`, not `users_id`
Hmm, not really. Even in the relational interpretation, each row represents a single tuple in the relation. So singular phrasing for the attribute names is appropriate.
In my mind, the ORM object is a representation of a single group and a single user, so GroupUser. The table is a representation of all member users for all groups: groups_users. That goes against Rails conventions so I have to manually define the table name on such models but I also understand it's not exactly easy to detect these irregular inflections. I'll take the trade-off.
Why spend a single cycle of computing time on this? There's nothing objectively necessary about it, singular-only is entirely adequate to convey the important semantics. Every single symbol in any code devoted to this conversion unnecessary surface area / computation time that contributes nothing to the problem domain.
> I now understand that the author is referring to the relational algebra side of things here. I don't think that changes my skepticism of the argument though, because very rarely is it useful to discuss these things in terms of the mathemetics with your coworkers.
You don't even have to go that far. It's the user table. The order table. People know what an order is, and what a table in a database is; the semantics of db-table-ness conveys that it's a collection and the nature of the collection with so much more precision than english plurals that it's more likely obscuring to use those than contributing to understanding... much like a lot of natural language conventions, the emulation of which is really the only reason anybody does this.
But also, when we're talking about databases, relational algebra should no more be weird or inadmissible than boolean logic concepts are to coding.
97% of SQL-92 reserved keywords are also singular (exceptions: constraints, diagnostics, names, references, rows, values)
https://www.postgresql.org/docs/current/sql-keywords-appendi...
It can clash with the plural noun, but ‘References’ is a singular verb conjugation in PostgreSQL, isn’t it?
The further I get in my career, the more often I think it is a mistake when languages encourage the use of ambiguous syntax over safer alternatives.
"It reads well everywhere else in the SQL query:"
You can alias the table names and have it read well in all places, and this mostly only matters if you are actually writing SQL and not using an ORM directly, which the next point seems to imply you would be using.
"The name of the class you’ll store the data into is singular (User). You therefore have a mismatch, and in ORMs (e.g., Rails) they often automatically pluralize, with the predictable result of seeing tables with names like addresss."
Almost any modern "pluralize" implementation would handle this correctly. Rails would call the table `addresses`.
"Some relations are already plural. Say you have a class called UserFacts that store miscellaneous information about a user, like age and favorite color. What will you call the database table?"
You would call the table user_facts... Am I missing something?
I'd probably say that users_facts would be a to-many join table between users and facts, like if you had one row per fact and a multiple facts per user though that example doesn't really make sense here (could just have the FK exist in Fact and not need a join table). If UserFacts were stored in a table with multiple facts in one row about a single user, I would probably call that table user_facts.
Would probably also be fine with running across either in any codebase (or even singular table names, for that matter! as long as it's consistent :D )
users_to_facts if it's many to many
Fwiw, this seems like a pretty contrived example, and I'm struggling to think of a better one. Maybe if you recorded user achievements as a single row for each user, with each achievement being its own column? But in most situations like that it would probably be best to take the extra normalisation step and just have a separate table for all the achievements in the game, that way it's a lot easier to add new ones.
(The right answer, as others have already pointed out, is “whatever is consistent”)
You change the class to singular and the database table is plural.
The class is used to instantiate an object that is one instance of the model. So it's singular.
The corresponding table is a collection of records (corresponding to instances) of the model. So it's plural.
Why is this so hard?
SELECT id, name
FROM users
JOIN countries ON users.countries_id = countries.id
WHERE countries.name = 'Canada';
Just stick to singular, and a whole class of issues disappears.
The fact that the article recommends user over users shows they have no authority naming anything at the database level.
If you are hand writing a DAL then by all means name it whatever you want. To me having a table as singular goes against what a table is.
wiredfool=> select * from user;
user
-----------
wiredfool
(1 row)
test=# create table test (
id serial,
"user" text,
date text,
timestamp text,
name text,
version text);
CREATE TABLE
test=# alter table test add column "select" text;
ALTER TABLE
test=# alter table test add column "*" text;
ALTER TABLE
test=# alter table test add column "from" text;
ALTER TABLE
test=# select "select", "*", "from" from test;
select | * | from
--------+---+------
(0 rows)
test=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+----------------------------------
id | integer | | not null | nextval('test_id_seq'::regclass)
user | text | | |
date | text | | |
timestamp | text | | |
name | text | | |
version | text | | |
select | text | | |
* | text | | |
from | text | | |Or maybe get into the habit of wrapping your table/column names with backticks to tell the query language of choice that you're not referring about the keyword.
You need never wonder if you should write 'people_id' or 'person_id' if you consistently use singular nouns.
Reducing the mental load slightly is reason enough.
In an alternative universe, databases would maybe distinguish between the two like programming languages do. But you rarely have two tables with the same “type”, so there’s a reason why they don’t.
Arguably, table names occur more often in their “type” capacity than in their “collection” capacity. The most convincing argument for me, besides ORM name mappings becoming trivial, is the “<table>.<column>” syntax, where you want to have “employee.salary” rather than “employees.salary”.
Not that long ago, length limits on table names were an additional argument for sticking with the shorter convention.
This topic also reminds me of the convention for Git commit messages to use the imperative mood instead of past tense, which at first feels odd, but then you get used to it rather quickly.
“It has user_factses in its database, precious”
Plural is defined as the Noun plus an S, but is configurable in Django.
User *user = ...;
int users = ...;
for (int i = 0; i < users; i++) {
user[i] = ...;
}
So in this context, it's pointing to a list of users, not just one.
If that's the case, the SQL command would be "CREATE RELATION", not "CREATE TABLE". This seems like an attempt to torture the truth to get the argument the author wanted.
> The last argument above is the strongest, because it only takes one such exception to wreck an entire schema’s consistency. You won’t run into problems with singular, now or later.
If it's valid to have a named User that stores User for the sake of consistency, then it should also be valid to have a table named UsersFact that stores user's facts for the sake of consistency? Doesn't it work both ways?
2 is a fair point, but I'm not sure when was the last time I didn't use table aliases for anything more than a 2 liner.
3 I think that is due to a conceptual mismatch between SQL, which is basically just an implementation of relational algebra, and OOP. I'm not to well versed in this, but an ActiveRecord table class is actually more similar to a messenger or factory producing row objects.
One would wonder how AI guided code / ui generation could cope with naming inconsistencies on the fly and use better reading aliases in the generated output. (much like a human does). Frankly I would be shocked if we don't see systems that behave like this in the future. The age of dumb tooling seems to be close to an end.
Aside from the confusing syntax errors when you forget to quote them, MySQL will let you muck with the builtin user relation.
I hadn’t seen this before, apparently it’s gamer slang for “Good luck, have fun”
https://www.howtogeek.com/509406/what-does-glhf-mean-and-how...
Which is not totally covered on that guide but is how I’ve seen it alternatively used in practice.
Had to look this up too, FAAFO = "Fuck around and find out"
Stop building shit-ass awful databases, please. You don't need to be reading about whether to pluralize your table names or not. You need to be reading about how to even begin to do even a moderately decent job. Go back to your favorite software engineering book and replace all mentions of "class" with "database table" and see if it works. It won't, but why it doesn't will be enormously instructive.
Edit: Just to be clear, I'm not saying it's the right thing to do, at least not in every case. But it's an option; one that's different from the parent's perspective anyhow.
Most databases probably don't have test suites. But, it would be interesting to see if any work has been done on mutating constraints or "dirty data" injection.
This would ideally take the form of some annotated algebra, so that it is db driver agnostic, if possible.
Chapeau!
The last argument:
> Some relations are already plural. Say you have a class called UserFacts that store miscellaneous information about a user, like age and favorite color. What will you call the database table?
I lean towards thinking this is a non-issue. In this specific case I'd actually call the model UserFact and the table users_facts. I'd assume it maps many users to many facts. It otherwise sounds like there's some json or "text" (as in "ADD COLUMN my_column TEXT") data that is storing words as "facts" and the table name is just kinda weird. I don't see the problem and if someone were to insist there's a problem, I'd probably suggest changing the model name.
One of the most frustrating things in software engineering is eyeballing a decision (i.e. naming a table with a plural) and just gut-level knowing it's the wrong decision, but not quite being able to remember specific pitfalls that instilled that feeling earlier in your career.
That fourth point is the kind of thing that just cuts right through the conversation - "doing it this way does not scale and will fail, here's an example of how". Such frission in finding those and getting consensus around them, because you can feel the future pain you've avoided evaporating.
Plural.