8 ms·

Use singular nouns for database table names

• fafzv 3y ago
  [flagged]
  • dang 3y ago
    Not I, but we did put that one in the second chance pool! https://news.ycombinator.com/pool https://news.ycombinator.com/pool https://news.ycombinator.com/item?id=26998308 https://news.ycombinator.com/item?id=26998308
• nosefrog 3y ago
  Every database schema I've worked with uses plurals. Seems worth using plurals for that reason alone.
  • ahmedfromtunis 3y ago
    In Django, the norm is to use singular nouns for models/table names.
    • bartkappenburg 3y ago
      “The User model” sounds so much better than “The Users model”. Plural is defined as the Noun plus an S, but is configurable in Django.
    • clawlor 3y ago
      Django also prepends the app name, giving you e.g. `auth_user` instead of `user`, side-stepping the likely collisions with reserved SQL keywords.
• NegativeLatency 3y ago
  GLHF if you have tables named like ‘group’ or ‘user’ Aside from the confusing syntax errors when you forget to quote them, MySQL will let you muck with the builtin user relation.
  • pimlottc 3y ago
    > GLHF 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-do-you-use-it/ https://www.howtogeek.com/509406/what-does-glhf-mean-and-how...
    • NegativeLatency 3y ago
      Here I used it in the sense of: You’re going to have a bad time and I will have no part of it Which is not totally covered on that guide but is how I’ve seen it alternatively used in practice.
    • riwsky 3y ago
      GLHF = PG13 “FAAFO”
      • pimlottc 3y ago
        > FAAFO Had to look this up too, FAAFO = "Fuck around and find out" https://www.urbandictionary.com/define.php?term=FAAFO https://www.urbandictionary.com/define.php?term=FAAFO
  • wiredfool 3y ago
    Unless you're never going to ever, ever, touch the database without using an ORM, don't use table names that need to be double quoted.
• koboll 3y ago
  That fourth point is a great one. 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.
  • veonik 3y ago
    I wrote a top level comment but I really don't understand this argument. Just because the model is "multiple facts about a user" does not mean you have to jump through hoops to name the database table. It would just be user_facts, wouldn't it? That seems intuitive and straightforward to me, even if the class name is also plural.
• veonik 3y ago
  Some of these arguments are a little questionable imo. "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?
  • RobAley 3y ago
    I'd call it users_facts. But that's just me.
    • veonik 3y ago
      There's definitely some ambiguity there, that's a good point. 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 )
      • zepolen 3y ago
        user_facts if it's one to many users_to_facts if it's many to many
    • deleted 3y ago
      [deleted]
  • 3y ago
• mtklein 3y ago
  I've started to do the same for C arrays, leaving the plural as the name for its paired count: User *user = ...; int users = ...; for (int i = 0; i < users; i++) { user[i] = ...; } This also used to be the Google convention for naming repeated fields in Protocol Buffers, so that the generated code would read like `add_user()` and not `add_users()`, though it seems like that has changed?
  • theonething 3y ago
    That doesn't make sense to me. The array is a collection of users, so `users` makes more sense to me. And what you are calling users is `totalUsers` or a variant of that.
    • Ferret7446 3y ago
      Well, it's technically just a pointer to a user in C...
      • theonething 3y ago
        An array in C is a pointer to a block of memory consisting of a contiguous sequence of memory chunks of the same type. So in this context, it's pointing to a list of users, not just one.
• chomp 3y ago
  > 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. 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.
  • jwcooper 3y ago
    Rails seemingly handles it the same way as Laravel and it all makes sense very quickly. 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"
    • stanislavb 3y ago
      I'd say Laravel handles this the same way as Rails as it was inspired by Rails. (nitpicking)
  • renewiltord 3y ago
    Aligning database theory and a modern RDBMS, you get: 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).
• TehShrike 3y ago
  For me one of the most practical benefits of singular table names is: not having to think about the primary/foreign key names, because you can always just assume that it's "[table name]_id". 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).
  • bluefirebrand 3y ago
    It really should just be "id" 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.
    • heleninboodler 3y ago
      I believe GP is talking about how you refer to this column in another table, where "id" is not an option. 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".
      • bluefirebrand 3y ago
        That's fair. 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.
        • henriquez 3y ago
          It’s still a good practice to refer explicitly to the foreign reference in the column name, so in your example owner_user_id would be preferable to owner_id if the foreign key refers to a record in the user table.
    • 3y ago
• sdfghswe 3y ago
  > 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? > 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?
• wiredfool 3y ago
  In psql: wiredfool=> select * from user; user ----------- wiredfool (1 row) Rule 0, Don't name tables with reserved words.
  • jstx1 3y ago
    There's many keywords that make good column names though - user, id, name, date, version, timestamp.
    • wiredfool 3y ago
      Of those, user is the only one that must be quoted in postgresql. It doesn't show up that way in psql through, so it's a bit inconsistent. test=# create table test ( id serial, "user" text, date text, timestamp text, name text, version text); CREATE TABLE Then again, lots of things are actually valid column identifiers, if they're quoted. 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 | | |
  • noelbautista91 3y ago
    This is why one should go with `$APP_User` or append `$APP` to generic sounding classes/tables. It's a slight inconvenience. 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.
• teeray 3y ago
  > 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? “It has user_factses in its database, precious”
• deleted 3y ago
  [deleted]
• jeffrallen 3y ago
  See also the article "Use blue for painting bikesheds".
  • heleninboodler 3y ago
    Or "Don't Waste Time Debating Things That Don't Matter And Have No Definitively Correct Answer"
• drivingmenuts 3y ago
  No.
• bhawks 3y ago
  Reading between the arguments it feels like the most meaty point is how plurals in table names play poorly with consistency in the context of auto-generated code and UIs. 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.
• elchief 3y ago
  99% of PostgreSQL reserved keywords are singular (except `references`), so if you use plural table names, you're more likely to avoid using a reserved keyword (such as `user`) 97% of SQL-92 reserved keywords are also singular (exceptions: constraints, diagnostics, names, references, rows, values) https://www.postgresql.org/docs/current/sql-keywords-appendix.html https://www.postgresql.org/docs/current/sql-keywords-appendi...
  • alerighi 3y ago
    This. While naming tables with singular names is the formally correct choices, since the name must be seen as the name of the relation and not of a collection (that is the same name that in a programming language you would choose for a class), in practice there are too many common keywords that are singular, thus you have the choice of choosing plural and still have meaningful names, add a prefix/suffix to all tables, or remembering to quote every time you use a name. I prefer the first, since the second one adds inconvenience and the third one adds possibility of mistakes when you forget to quote it.
  • Someone 3y ago
    > 99% of PostgreSQL reserved keywords are singular (except `references`) It can clash with the plural noun, but ‘References’ is a singular verb conjugation in PostgreSQL, isn’t it?
    • elchief 3y ago
      Can you make a table called `references`? No, you cannot
      • saltcured 3y ago
        CREATE TABLE "references" ("user" serial8 PRIMARY KEY, ... ); 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.
• remus 3y ago
  I don't find the arguments either way particularly convincing. IMO just be consistent with the existing schema (if there is one), or use whatever you prefer.
  • heleninboodler 3y ago
    Yes, this is bikeshedding at its worst. Just pick one. Preferably one that doesn't involve "clever" code that changes the names of things behind your back based on the English language's weird pluralization edge cases.
    • wwweston 3y ago
      While there's a variation in how clever/complex pluralization code can be, there's only one class-table correspondence convention that doesn't end with codepaths trying some level of automatically changing the names of things behind your back... and it's singular table names.
• trollied 3y ago
  A table naming thread reminds me that it has been a while since I’ve seen a schema with tables all named “tbl_whatever”. The last one I saw even had a view called tblUserGrants. I LOLed.
• tarkin2 3y ago
  I was against this previously. But it simplifies things. 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.
• orthecreedence 3y ago
  So I should put this article into the suggestion_i_will_ignore table instead of suggestions_i_will_ignore.
  • muti 3y ago
    Have you considered ignored_suggestion?
• gabereiser 3y ago
  In the example, the code for user is broken, because user is a reserved keyword. This is the whole reason we pluralize. That, and we are naming a collection of relations and not naming a relation (that’s FK). The schema of the row is the object. The table is just a collection of rows with the same schema. The fact that the article recommends user over users shows they have no authority naming anything at the database level.
  • edgyquant 3y ago
    Pretty trivial to get around this with Postgres, just put quotes around the table name. That’s what I’ve always done anyway
    • gabereiser 3y ago
      Yes but it’s non-standard. Some engines use quotes, some back ticks. If someone uses an orm then it’s gets tricky if the orm doesn’t automatically escape and you can manually specify table name. 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.
    • elchief 3y ago
      Or use plural table names hehe
• chinaman425 3y ago
  [dead]
• theonething 3y ago
  > 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 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?
• lcnPylGDnU4H9OF 3y ago
  > The last argument above is the strongest, because it only takes one such exception to wreck an entire schema’s consistency. 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.
• wizofaus 3y ago
  The argument about the name of the class representing a single entity not matching doesn't hold for me - why would I want that to match anyway? What I want to match is the name of any variable/property that represents the table as a collection of entities, and I nearly always want that to be plural (though I'd be fine with something called "UserTable" - just haven't seen that convention used). Honestly the only system I don't want to work with is one that uses a mixture of plural and singular names, or that uses incorrect pluralisations. But in 25+ years of working with various database schemas I've yet to come across a table named "addresss" or even "childs".
• jaywalk 3y ago
  If you have a file cabinet with a drawer full of info on your customers, would you label it "Customer" or "Customers"? Plural.
  • heleninboodler 3y ago
    "Customer Records"
• stronglikedan 3y ago
  Our MIS does this in Postgres, and it's very frustrating at times. Especially when you have to remember to quote all your table names in generated queries, so as not to run up against reserved words.
• layer8 3y ago
  The conflict arises because the table name serves both to name a collection and to name a type. For types, the general convention is for the name to correspond to what a single instance of the type represents, so usually singular. 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.
• xupybd 3y ago
  It's all good until you need an order table. Oh no, reserved keyword. Solved with Orders.
• jacknews 3y ago
  I agree, and the example could make the problematic aesthetics of plural names even more obvious: SELECT id, name FROM users JOIN countries ON users.countries_id = countries.id WHERE countries.name = 'Canada'; But the real problem is that in the ORMs and frameworks, entities are related through english-language pluralization rules, magically applied. That can be confusing to newcomers (and non-native speakers), and even experienced users, and sometimes gets badly in the way when the pluralization logic fails. Just stick to singular, and a whole class of issues disappears.
• feoren 3y ago
  Real talk: y'all build databases like shit. The database is your persistence layer, not your application layer. You give lip service to software engineering concepts like abstraction, DRY, SOLID, or whatever was popular right as you got out of college, but then you throw it all in the trash when you design databases. Your databases are awful. Don't feel bad: literally everyone is doing it. But also do feel bad: you could be doing so much better. It starts by realizing that software engineering principles apply to everything, including (perhaps especially) the database layer. All other database-design advice is secondary to that. 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.
  • slindsey 3y ago
    Just to take a different perspective for anyone reading this, your database is more than _just_ your persistence layer. It can be where rules about the business live. Rather than rely on 12 different applications or code in 27 different files to maintain the proper rules, you can enforce those rules right where the data lives, guaranteeing that no matter what moron is writing code, the data will be pristine because the database has checked it before putting it in. 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.
    • turtleyacht 3y ago
      Interesting. Mutation testing on database tables. Instead of testing application code, business rules--and constraints--are mutated against a test suite-- 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.
  • riwsky 3y ago
• jeroenhd 3y ago
  As this is clearly something people have preferences for, the only clear solution I can see is to stick to mass nouns for table names. Refactoring everything into words like "sand", "air", "rice", "bread", and similar words may be a challenge, but you'll never have to decide on singular or plural nouns!
  • cubefox 3y ago
    Finally a voice of reason.
• kelnos 3y ago
  > Strictly speaking, we’re not naming a table, we’re naming a relation. 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.
• gweinberg 3y ago
  It seems to me more natural to use a plural name for a collection, and a singular name for an instance. There isn't a fundamental difference between a sql/mongo table and a list/array in this sense. Occasionally you'll have a collection of collections so you have to give it a double-plural name like "hobitses", but I have it on good authority that that is totally a legit word.
• michaelhoney 3y ago
  I agree. Also, that page looks very good, as do the ones where you have a banner image (https://www.teamten.com/lawrence/writings/homeschooling.html https://www.teamten.com/lawrence/writings/homeschooling.html) Chapeau!
• bakuninsbart 3y ago
  1 seems questionable, since in practice we both refer to the definition of the relation as well as the collection of elements fulfilling the relation as the table. 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.
• PinkMilkshake 3y ago
  This feels like some Sapir–Whorf type thing. The software can do anything, yet you can’t help but act as if there is some constraint because it doesn’t cleanly map to language.