Table Naming Dilemma: Singular vs. Plural Names

Question

Academia has it that table names should be the singular of the entity that they store attributes of.

I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table to the singular, forever sentencing those using the table to sometimes have to use brackets.

My gut feel is that it is more correct to stay with the singular, but my gut feel is also that brackets indicate undesirables like column names with spaces in them etc.

Should I stay, or should I go?

Dup of the earlier [Database tables naming, plural or singular](http://stackoverflow.com/q/3254141/90527), but this one got more attention. — outis, Mar 02 '12 at 01:37
English is not my mother tongue, can somebody please give me example name of `Singular` and `Plural`? — GusDeCooL, Nov 08 '12 at 17:58
http://www.codeproject.com/Articles/309304/Should-we-use-Singular-or-Plural-Database-Table-Na — Mr Spark, Nov 11 '12 at 14:42
I'm surprised that more people aren't saying: it's determined by what single row represents. In a single database I may have a table who's rows represent single a single widget, and another who's one-to-many relationship to that table means rows represent many widgets. Not doing this looses expressiveness. — andygavin, Sep 21 '15 at 22:31
@andygavin well, I work in a system where singular and plural is mixed (perhaps like you suggest, I might check it up, but I suspect it was just too many cooks), it took a while before you got it right (intellisense is always too slow for me...) — Erk, Mar 31 '16 at 21:31
One way to solve the keyword conundrum would be to add a short prefix, eg "t" for tables (tUser[s]), "v" for views, etc, however not recommended for attributes (don't want an "aGroup" or similar!) — Erk, Mar 31 '16 at 21:35
Must be Plural! List names ==> StringList of names, UserCollection of users, Collection of users, UserTable of users, AppleBag of redApples... ring any bells? class name will be 'User', 'Apple' etc. — sojin, Apr 29 '16 at 13:04
Both of them: example I have a table called Users, and it is ok, then I have a table called User_Transactions not Users_Transactions. Then consider you have this table: user_categories, that just hold categories, then you have a linked table called users_categories, since the user can be in more category... I hope this gives the idea — albanx, Aug 12 '16 at 12:48
I just want to add that in all of these discussions, please note that a table in no way shape or form the same as a class. A table is a collection of elements of a specific type that can be sorted, queried, etc. on individual properties. A class is the framework to describe the properties and behaviour OF a specific type. In OO coding terms the closes representation to a table is a collection of objects.(no matter what ORM you may be using). This is by far the highest ranking google answer on this subject, so although the question is closed, the page still has value. — , Jan 27 '17 at 15:49
I would go for the common practice of the ecosystem you are working in. For example: In Node.js ORMs like Bookshelf.js or Objection.js are mainly based on "Knex.js". And in the "Knex.js" documentation you will find table names in plural. So I would go for plural in that domain. Source: http://knexjs.org/#Schema-createTable — Benny Neugebauer, Sep 07 '17 at 20:58
**Reopen.** This question concerns programming efficiency and reliability. While opinions do abound, the top voted answers are full of **facts, references and specific expertise**. — Bob Stein, Nov 03 '17 at 21:20
All of our tables are Singular (each row is thought of independently) but our ORM pluralizes it in our application (when you're querying, you're generally interested in a collection) — Zac Faragher, Dec 19 '17 at 00:45
INSERT INTO customers (name) VALUES ("Alice"). Not: INSERT A customer (name) WITH VALUES ("Alice"). SELECT FROM customers WHERE name = "Alice". Not: SELECT A customer WITH name = "Alice". DELETE FROM customers WHERE id = 1. Not: DELETE A customer WITH id = 1. UPDATE customers SET email = "alice@example.com" WHERE name = "Alice". Not: UPDATE THE customer WITH name = "Alice" SET email = "alice@example.com". — Toxiro, Feb 21 '18 at 11:49
Yes I agree. It makes sense to have a table of users and call it "AppUser" at the same time it also makes sense to have a table of rules applicable to a particular type of user and call it "UserRules" — Arindam, Dec 26 '18 at 06:02
@Arindam "UserRule" or "UsersRule" definitely doesn't sound right as a name for a list of user-related rules. Now that is a strong argument against always using the singular form! — bitoolean, Apr 24 '19 at 16:31
From a practical standpoint: When I'm using EF and the entities created by it, it makes more sense to use `new Order` than `new Orders` for example, when creating a new order record. — Kai Hartmann, Nov 10 '20 at 09:32
This may differ from DB type. If you're interested only in RDBMS I will stay with singular as I also do with code class names. But for non-relational like Azure Data Explorer I'd use plural as Microsoft does in Application Insights logs (stored in ADX). — gsscoder, Apr 04 '21 at 07:03

score 1948 · Answer 1 · edited Jul 01 '20 at 19:28

1948

I had same question, and after reading all answers here I definitely stay with SINGULAR, reasons:

Reason 1 (Concept). You can think of bag containing apples like "AppleBag", it doesn't matter if contains 0, 1 or a million apples, it is always the same bag. Tables are just that, containers, the table name must describe what it contains, not how much data it contains. Additionally, the plural concept is more about a spoken language one (actually to determine whether there is one or more).

Reason 2. (Convenience). it is easier come out with singular names, than with plural ones. Objects can have irregular plurals or not plural at all, but will always have a singular one (with few exceptions like News).

Customer
Order
User
Status
News

Reason 3. (Aesthetic and Order). Specially in master-detail scenarios, this reads better, aligns better by name, and have more logical order (Master first, Detail second):

1.Order
2.OrderDetail

Compared to:

1.OrderDetails
2.Orders

Reason 4 (Simplicity). Put all together, Table Names, Primary Keys, Relationships, Entity Classes... is better to be aware of only one name (singular) instead of two (singular class, plural table, singular field, singular-plural master-detail...)

Customer
Customer.CustomerID
CustomerAddress
public Class Customer {...}
SELECT FROM Customer WHERE CustomerID = 100

Once you know you are dealing with "Customer", you can be sure you will use the same word for all of your database interaction needs.

Reason 5. (Globalization). The world is getting smaller, you may have a team of different nationalities, not everybody has English as a native language. It would be easier for a non-native English language programmer to think of "Repository" than of "Repositories", or "Status" instead of "Statuses". Having singular names can lead to fewer errors caused by typos, save time by not having to think "is it Child or Children?", hence improving productivity.

Reason 6. (Why not?). It can even save you writing time, save you disk space, and even make your computer keyboard last longer!

SELECT Customer.CustomerName FROM Customer WHERE Customer.CustomerID = 100
SELECT Customers.CustomerName FROM Customers WHERE Customers.CustomerID = 100

You have saved 3 letters, 3 bytes, 3 extra keyboard hits :)

And finally, you can name those ones messing up with reserved names like:

User > LoginUser, AppUser, SystemUser, CMSUser,...

Or use the infamous square brackets [User]

edited Jul 01 '20 at 19:28

Jacob Stamm

1,064
1
19
47

answered Apr 30 '11 at 10:56

Nestor

1,881
4
25
29

686

I'll bet if you put a label on a sock drawer you'd call it "Socks". – Chris Ward Feb 06 '12 at 08:02
80

Definetelly. It is difficult to get one standard that works for all and for everybody, important is that works for you. This works for me, and here I have explained why, but again, that is for me, and I find it very convenient. – Nestor Feb 14 '12 at 12:10
2

I like this answer, it gives many options as the advantages of using the Singular. I am sure there are also disadvantages, like the socks example, still I think its quite a neat approach. – will824 Apr 10 '12 at 19:53
Reason 6, well until you try to get more than one user returned :P SELECT Customers.CustomerRole FROM Customers WHERE Customers.CustomerRole = 'admin' – steve Jun 20 '12 at 21:58
499

The bigger question Chris, is why would you follow database naming conventions when naming a sock drawer? – Kyle Clegg Jul 25 '12 at 17:29
17

@Kyle because of Nestor's Apple Bag / Container analogy (Reason 1). When you label a moving box, you'd write "Books", not "Book". – S22h Oct 04 '12 at 13:11
87

This answer needs more praise. It discusses a bunch of practical reasons that I apply to why I prefer singular names. The alternate discussions about proper language in reference to sets are just philosophical and are obscuring the real point. Singular just works better. – Jason Oct 08 '12 at 21:58
2

Reason 4 is cute, but it serves little purpose if you have to use the plural form elsewhere in the code. Let's say there is a model function to retrieve the list of inactive users, it is natural to name it `getInactiveUsers`. So, the simplicity becomes: (1) singular in SQL for everything. (2) singular class in code. (3) for the rest, singular or plural depending on whether it is a collection or a single instance. My simplicity is just (3). – sayap Nov 24 '12 at 02:49
2

+1 For reason 5 (globalization) alone. I have faced that problem before and certainly it helps foreign programmers to use an uniform naming convention for objects and tables. – MV. Dec 24 '12 at 21:30
331

I have a "Sock" drawer at home, not a "Socks" drawer. If it were a database, I'd call it the "Sock" table. – jlembke Apr 11 '13 at 21:43
5

@Chris An entity is not a 'label' or a 'collection.' It 'should' define an 'instance of ' ... – Edward J Beckett Apr 30 '13 at 03:09
1

@sayap, you have a point, although, if you usually work in a team and project has several layers, only the ones working on coding need to deal with the plural form, while others like the database designer and UI can work easier with singulars. – Nestor May 03 '13 at 11:29
1

@Nestor, after reading your comment, reason 4 now makes sense. I am actually sold :) – sayap May 04 '13 at 02:21
2

So bad that I cannot upvote this response twice. One for reason 1 and another for reason 4. – Oybek Aug 31 '13 at 06:21
2

Reasons 2-6 are rather moot once you agree that the idea of verbose languages and OOP is for humanization of programming in order for concepts to be instantly understood. – Tim Sep 18 '13 at 00:44
7

As for Reason 1, you can justify a singular name with using the name of the collection suffixed when talking about one object: "This is my SockDrawer". However when you are referring to all of your drawers, you would state "These are my socks, these are my pants" etc. This is because the context of talking about a collection type has already been established. "What is this table for? My Users. What table is this? My Users Table. What will you label it? Users." – Tim Sep 18 '13 at 00:55
3

@jlembke: It’s a sock drawer containing socks. You wouldn’t label your sock drawer “sock”. Which analogy is more accurate? – Ry- Oct 09 '13 at 20:22
7

@minitech: Those are good points. For me, it's just what Nestor and Jason said, I just think it works better. But to continue the useless wrangling :) - "Socks", "Pants" and "Gloves" are typically plural anyway. Maybe "Shirts" is better. I hear "Put these shirts(particular) in your Shirt(abstract) drawer" in my house everyday, and at work I hear "Persist these customers in the Customer table" But in the end it doesn't matter. Singular works better for me for the reasons listed in this answer. And if I see a database with plurals, no biggie, but I know there will be a few inconveniences. – jlembke Oct 10 '13 at 21:57
2

@jlembke: what a great example "socks usually plural" (because comes by pairs), and thanks for the "put these shirts in the Shirt drawer", since I'm not native English speaker, I was not aware of that. Thanks to your examples, now i'm confident the "socks drawer" is not a solid argument against the singular concept anymore. – Nestor Oct 26 '13 at 05:18
11

You have user records, not users records. Therefore you have a User table, not a Users table. – OCDev Oct 26 '13 at 23:24
29

@minitech: True, you wouldn't name your sock drawer "Sock." But you COULD name it "Sock Drawer." In the same way you could name the user table "User_Table." The names CAN be singular as long as the word "Table" comes after it. So here's how I look at it: imagine that the word "Table" should appear at the end of all of your table names, but as a convention the word "Table" gets dropped, leaving you with just the singular word. Just as a convention. When you look at a table name, you're seeing "User," but you're interpreting it "User Table." This can work for OCD folks like me. – OCDev Oct 26 '13 at 23:42
2

@FriendlyDev: And I read it “Table with Users”. `ERR_SUBJECTIVE` – Ry- Oct 26 '13 at 23:58
2

@minitech: I agree the way it can be read is subjective. I am making a different point: Reasons 1-6 are mostly objective and show that using singular table names is more effective for development purposes. However, singular names for tables looks and feels awkward sometimes to some people (that's where the subjective experience comes in). That is the only drawback that I can see to using singular names. The perspective that I shared helps me get over the mental reservations to using singular names. I'm sure this perspective will help others feel more comfortable with using singular names. – OCDev Oct 27 '13 at 00:34
1

@FriendlyDev: Reason 3 is the only objective one in the list. 2 and 5 are the same reason. Reason 6 is stupid. But… okay. – Ry- Oct 27 '13 at 00:49
2

@minitech: Reason 1 isn't worded very well, but it is objective. A singular word is always used to label a collection or a set. It is never the "socks set" or the "trinkets collection," but always "sock set" or "trinket collection." I agree that you can REFER to the set or collection by saying "set of socks" or "collection of trinkets," but you aren't actually making reference to the actual name of the set or collection itself when referring to them this way. In "User Collection" vs "collection of users," "User" is the name of the collection, "users" is part of a description of the collection. – OCDev Oct 27 '13 at 01:52
@FriendlyDev: I don’t agree at all. My name is Ryan; you don’t call me “the Ryan person”. “The sock table” is not implicit. (Disagree? That’s why it’s subjective.) – Ry- Oct 27 '13 at 01:54
1

@minitech: 2 and 5 are closely related and could be lumped together, yes, but that does not mean they are not objective. It can also be proven that 4 contributes to simplicity, which makes 4 objective. 6 is weak, but it is also objective. These are all objective. And finally, you are Ryan, but you are not and never will be a set or a collection, and so the Ryan example is a non sequitur. If there was a collection of people called Ryan, it can be called the "Ryan Collection," not the "Ryans Collection." A "collection of Ryans" would be referring to it, but wouldn't be its name. – OCDev Oct 27 '13 at 02:09
2

Although I like this answer, my eyes are drawn to the URL `stackoverflow.com/questions/...`. I would put "questions" in the "questions" table, and the URL would reflect the table name without any confusion. – rybo111 Nov 14 '13 at 12:51
I'd leave params/data out of your table naming conventions. Don't create a new table for every type of drawer there is. Do add a column to label the type of item being stored in our drawer. – Travis Nov 19 '13 at 06:20
referring to your applebag example, I agree. But if I am creating a table which has different species of apple yes I could create a table named Apple[id, color, size, countryOrigin, etc...) but if I am creating a table in which I would save all fruits grown in my country then can I name it fruits[id, name]? – shabby Dec 09 '13 at 20:23
3

@shabby If multiple apples go in the `Apple` table, then multiple fruits go in the `Fruit` table. You could also name them both plurally. The only thing that is guaranteed to be unacceptable is to name one singularly and the other plurally. – Rainbolt Feb 12 '14 at 16:03
1

After reading all the comments I feel that I must say.... SOCK.... +10 to OP for a very good answer! – melodiouscode Apr 23 '14 at 11:14
Reason 7. In case the table maps an object, the table name and the class name will match which has the benefit of avoiding the toll of adding extra impedance in names mismatch. – Sebastian Sastre Jul 06 '14 at 03:47
Usually, a pair of socks are removed from the drawer at a time, not just one sock. The tie rack, shirt draw, shoe closet seem to support the SINGULAR reason. – bvj Jul 14 '14 at 00:24
2

Having a news table is as meaningful as having a love table or a weather table. They are all https://en.wikipedia.org/wiki/Mass_noun There is no such thing as a news, a love, or a weather. How many weathers would you like today? – Tom Haws Sep 06 '14 at 11:15
I recently came across another good reason to stick with singular. I had a table that contained a bunch of settings - where each set was a collection of settings for an organization - and I wondered if I should go with singular or plural. That's when it hit me because obviously I had to name the table "OrganizationSettings". If I had gone with plural I couldn't make this distinction. So in this case the plural refers to a bunch of settings for each organization. And you could still create a table that contains a different setting in each line. I hope I could get my point across. – Semmel Oct 04 '14 at 13:44
4

While I agree with the conclusion that singular is better, the example for the first reason seems illogical. You're saying a bag of apples should be called AppleBag; by this reasoning, the table should be called UserTable (as in you're describing the container - either a bag of apples, or a table of users). Or conversely, if you think a table of users should be called User, then you should be calling your bag of apples Apple. – Rob Grant Nov 27 '14 at 11:43
4

I gotta call you on your 'AppleBag' comment form your first point. Why? Because by adding the suffix 'Bag' you *are* changing it to reference a container for something. For the same reason I have no problem if you said AppleTable which equates to an AppleCollection. But without the descriptive part--'table' or 'collection' here--you are calling something by what it may or may not contain, not what it *itself* is. In other words, you *can* say 'An AppleBag holds apples' but you would *not* say 'An apple holds apples' but you can say 'The item named 'Apples' holds zero or more apple objects'. – Mark A. Donohoe Jul 09 '15 at 04:25
@jlembke, you're right. You would call it a 'sock drawer' but by your own admission you're not doing that in SQL because if you were, you'd name it 'SockTable' which you don't. You're calling it 'sock'. As I said above, 'A sock drawer contains sock objects'. You wouldn't say 'Sock Drawer contains sock objects' nor would you say 'Sock contains sock objects'. You're confusing a name with a description. Your 'Sock drawer' (description) has the label (name) 'Socks', not 'Sock.' – Mark A. Donohoe Jul 09 '15 at 04:33
4

If a table is not intended to be read by a human, name it `asdjfhasgkdjagksjdg`. – Quarktum Sep 17 '15 at 15:49
What about naming a database itself? For example, I want to have a sandbox DB that unit tests will utilize, and I am trying to decide between UNIT_TEST and UNIT_TESTS. – Leonid Sep 25 '15 at 15:05
2

The main reason for a singular naming convention is: a tables is the direct database expression of an entity in the ER-Model language/Relational Algebra. The table "apple" represents the entity apple and contains the "instances" of an apple. In OOP, a Class name is singular: "Apple". The you will have many "apples" - the instances (objects) of type "Apple". – tobia.zanarella Jan 13 '16 at 20:31
About the sock vs socks: if I were to have such a drawer, I would probably call it a socksDrawer, because that's what it is. It is not a "sock" or "socks", it is a "drawer". I would name the container, not only what it's used for. Why not name the table for what it is: User(s)Table or User(s)Tbl? That makes the singular vs plural discussion irrelevant in this scope as long as it's consistently used. Using such tablename convention in your code makes it 100% undubious despite the extra postfix. – html_programmer Mar 31 '16 at 09:48
2

Reason 1: Imagine you’re walking around town with your apple bag, and someone asks what you’re carrying. You’d reply, “my apple bag” or “apples.” But wouldn’t say “apple” unless you’re a lunatic. Reason 2: Plural words aren’t so difficult. By your logic, one might stop using plural word altogether. I think person might actually find this more confusing. Reason 3: Order is valid, although I don’t often lexicographically order my tables. Reason 4: “Customer.CustomerID.” Really? Reason 5: Just a rehash of reason 2. Reason 6: Fine, I’m sold. Plural it is. – Lorenz Forvang Apr 30 '16 at 05:42
4

I don't like your `AppleBag` analogy. It suggests that you would have to name a table for customers `CustomerTable` instead of `Customer`. – pacoverflow Oct 21 '16 at 19:36
2

@Nestor Table with name AppleBag is confusing ? is it container for apples or for applebags. According to you a table named with Customer is a container for customer. AppleBag is a type which stored inside Bags table. – Navrattan Yadav Dec 02 '16 at 06:28
1

You know what took me here today? Staring at a database full of awkwardly pluralized table names. It just made my eyes twitch and I wasn't sure why. When I look at an ERD (or schema diagram), I look at each table as a single record, so in my mind that's how it's registered. A matter of opinions, but I agree with the points presented in this answer. – megamaiku Feb 24 '17 at 19:00
1

@tobia.zanarella, just as I said above in regards to the other poster, your argument equating class names is incorrect because a class represents a single instance. It doesn't represent a collection of things. Conversely, a table represents a collection of things which are represented by rows. The Row is what's synonymous with a class, not the table. You wouldn't say 'Sock has a collection of socks in it', but you *can* say 'Socks (name) or SockTable (description) holds a collection of Sock objects in it.' – Mark A. Donohoe Apr 17 '17 at 18:40
2

@pacoverflow, your comment is exactly why the table should be plural. Customers clearly shows that it holds a collection of customer objects. If it's just 'Customer', then how do you know it doesn't hold properties for a specific customer? Singular is ambiguous. Plus it doesn't follow proper grammar either. "Select * From Customers where Name = "Joe"" clearly says your are selecting customers with that name. But does "Select * From Customer where Name = "Joe"" mean you're selecting a part of a customer where that part is named Joe, or are you selecting an entire customer with that name? – Mark A. Donohoe Apr 17 '17 at 18:47
1

You never mention understandability. Which should come before convenience or having to type less. A table is a list of things. If I saw something named apple, I'm going to understand that as one apple. If I saw the name apples, I'm going to understand that any number of apples. I think using the singular confuses someone trying to understand the meaning of code. Therefore it is semantically deficient and the plural should be used. – JCF May 04 '17 at 15:01
I called my sock drawer 'bob'. Calling it 'bobs' would be weird... Furthermore, I wouldnt call a table "news", that seems either way vague, so I'd avoid that name and for with i.e. 'newsarticle', which is more descriptive of its' actual contents. – Kevin R May 10 '17 at 15:06
@ChrisWard , you are not naming drawer; you are only naming socks at this case. I mean that label is not "Socks" Drawer, it is only "Socks" which are in Sock Drawer. – Burak Kalafat Nov 02 '17 at 12:41
4

This sounds very much like an opinion from someone whose biggest part of the job is to work with the database itself. I work in full stack positions and I prefer collective names or plurals simple because it works well throughout the complete application. You always have a list of users (table), and a user (row) inside. Choosing good names at the SQL level pretty much guarantees that even across multiple developers those names will be carried out to the entire application. When you start trying to teach developers to think somehow different about plurals at the SQL level, things gets messy. – lolol Feb 28 '18 at 19:59
If you need to label your own sock draw you have bigger problems than table naming ;) – niico Jun 11 '18 at 08:51
I think it depends what you are doing, for whether to choose to use plural or singular form of nouns for table names. However, `fruit` is already plural - and you would say that you have a pair of `socks` *not* a pair of `sock`. Socks is the singular, and `Sock Pairs` would be the plural. That's just, I believe, the way it is. lol. I do like the take away from both sides, and do definitely see the pros of using singular form. Great answer! – Rik Dec 12 '18 at 20:08
I like the way someone save disk space by cutting `s` in table name and waste a much more space by prefixing every customer property with `customer`. – sempasha Aug 27 '19 at 15:15
@Semmel if you're linking settings to organizations, I'm pretty sure you just have a junction table – Alexander Oct 24 '19 at 19:32
Reason 1 is based on False premises "You can think of bag containing apples like "AppleBag" why should this be the case? "the table name must describe what it contains, not how much data it contains" it contains Users :) But even if it should be named after the type it contains why must this be done? you're not providing any reason. Reason 2. (Convenience) This will be the case only if you stay within your database. When developping an application you will need to start dealing with singular and plural names anyway. Not being able to do so will make for hard to read code. – Patrick Dec 13 '19 at 13:49
Either way, root prob is we're still trying to shortcut natural lang, and therefore either table-prefixed col refs will suffer (w/ plural) or table refs will (w/ singular), and I believe most devs can't *just* work with either SQL or app code. In natural lang, you would *always* suffix your refs with the name of the container (e.g., "employee table/list/spreadsheet" (vs. just "employees"), but in SQL and app code, even though we've made *progress* in dropping (what was once thought ok) 1/2-letter id names, we've still haven't *fully* evolved to including the container name in our id names. – Tom Jan 29 '20 at 20:10
...so "select Customer.Name from Customer where Customer.Id = 1" should really be (to not shortcut natural language), "select CustomerTable.CurrentRow.Name from CustomerTable where CustomerTable.CurrentRow.Id = 1" – Tom Jan 29 '20 at 20:12
I really like your answer, the only problem is what happens when you want to access all records it looks a bit odd, example: $article->get_all() makes no sense, $articles->all() sounds more semantic, I would imagine a singular would represent a single row and nothing else – Apr 04 '20 at 23:47
I came to say something similar to Tom, I sometimes think about naming my tables tables. Whether I write `Select * from PeopleTable` or `Select * from PersonTable` - it's clear what I'm doing. I moved to EF codefirst a while ago and in VS it autosuggests plural names for tables because it makes more sense in LINQ and oop object terms. I realised I've been suffixing `ViewModel` objects with VM to help clarify my code. Then I thought maybe the models should be suffixed with `Model`. But then you'd have to call the table `PersonModelTable` - all in all, pick whatever's best for your setup!? – jamheadart Sep 30 '20 at 07:23

score 272 · Answer 2 · answered Dec 03 '08 at 21:09

272

If you use Object Relational Mapping tools or will in the future I suggest Singular.

Some tools like LLBLGen can automatically correct plural names like Users to User without changing the table name itself. Why does this matter? Because when it's mapped you want it to look like User.Name instead of Users.Name or worse from some of my old databases tables naming tblUsers.strName which is just confusing in code.

My new rule of thumb is to judge how it will look once it's been converted into an object.

one table I've found that does not fit the new naming I use is UsersInRoles. But there will always be those few exceptions and even in this case it looks fine as UsersInRoles.Username.

answered Dec 03 '08 at 21:09

Brian Boatright

33,940
33
76
102

55

I voted down and I'll tell you why, because I disagree. ORM by it's very nature is about mapping. Every ORM tool that I've ever used supports specifying the table name to be used for an entity when it is different from the entity's name. This is important because the whole reason we map to relational databases is so that we can easily make ad-hoc queries and reports with different shapes than our object model. Otherwise we'd all just be using object/document databases by now. – joshperry Aug 21 '10 at 00:51
29

The ORM should not dictate the names of the objects they map to. The point of ORM is an abstraction of the object, granting this flexibilty. – barrypicker Sep 14 '11 at 23:53
Is this predominant in tools, or just the tools you've used? – Bruce Alderson Sep 23 '11 at 20:38
19

In my world I try to use consistent names across the whole project to avoid wasting time wondering if this instance has an s on the end or not. The fact that an ORM *can* rename and be independent doesnt mean that doing so is helping your fellow developers. – John Nicholas May 01 '12 at 15:38
2

Some ORM (like many programming tools) have default behavior that generates implementations without configuration... with the selling point of PRODUCTIVITY. So creating an Employee class, without explicit mapping, would generate an Employee table by default – user919426 Feb 14 '15 at 22:44
1

@barrypicker. Plural names don't just look dumb in ORM code. Plurals look bad in SQL too, especially when referring to a unique attribute. Who's never written select user.id from users? Or perhaps ...from users left join on thingy.user_id = user.id...? – Samuel Danielson Feb 24 '17 at 05:34
@SamuelDanielson - I don't see your point. Using the example SQL you provide I would have used a table alias anyways such as 'select u.Id from users u'. The table is a collection of items therefore it should be plural - as in a basket of apples, not basket of apple. – barrypicker Feb 24 '17 at 21:49

score 260 · Accepted Answer · answered Dec 03 '08 at 19:31

260

Others have given pretty good answers as far as "standards" go, but I just wanted to add this... Is it possible that "User" (or "Users") is not actually a full description of the data held in the table? Not that you should get too crazy with table names and specificity, but perhaps something like "Widget_Users" (where "Widget" is the name of your application or website) would be more appropriate.

answered Dec 03 '08 at 19:31

Tom H

44,871
12
81
121

7

I agree. OrgUsers, AppUsers, anything to avoid using a keyword. – MikeW Mar 12 '09 at 23:11
8

-1. Table Users (and Countries, Languages) can be used in few applications simultaneously. – OZ_ Sep 27 '11 at 10:19
Which would be exactly why I said, "Is it possible...?" and "perhaps" – Tom H Sep 27 '11 at 13:53
143

Wouldn't associating schema name would remove all the confusion? AppName1.Users, AppName2.Users ? – Zo Has Oct 25 '11 at 09:49
5

That's one possibility, but there could be many reasons why someone wouldn't want to use schemata that way. Also, even with a schema you will still run into problems with them being keywords. – Tom H Oct 25 '11 at 13:57
8

I disagree with the table prefix -- that should perhaps be a schema? -- but I agree with a "more descriptive name". Even something as simple as "AppUser" would be sufficient, without entering the entire namespace debate. – Feb 10 '12 at 16:21
5

What does prefixing the table name have to do with *using singular vs plural*? Now the question just becomes do I use *Widget_User* or *Widget_Users*. This has nothing to do with the question. – Wesley Smith Nov 30 '16 at 23:14
18

This accepted answer is more of a side-comment and doesn't answer the question. – Viliami Jan 01 '17 at 12:19
The table should be client-agnostic, as in, it should not care if it's called Widget or if how it's used, the names could change. The table's only responsibility is to retain user data in an structured and explicit form. – tsuz Feb 13 '18 at 10:25
1

Definitely don't use the app name! At some point, someone in marketing will rename your product NewFooApp and you'll be stuck with the legacy of "Widget" polluting your database schema forever, with only a handful of people still at the company long enough to remember what it even means. – gregmac Jun 08 '20 at 18:39
While querying, ` from Countries, Cities where Countries.id` , Here countries having single ID, dont's sounds good. – Ravi Parekh Oct 16 '20 at 16:27

score 238 · Answer 4 · edited Apr 03 '17 at 17:48

238

I prefer to use the uninflected noun, which in English happens to be singular.

Inflecting the number of the table name causes orthographic problems (as many of the other answers show), but choosing to do so because tables usually contain multiple rows is also semantically full of holes. This is more obvious if we consider a language that inflects nouns based on case (as most do):

Since we're usually doing something with the rows, why not put the name in the accusative case? If we have a table that we write to more than we read, why not put the name in dative? It's a table of something, why not use the genitive? We wouldn't do this, because the table is defined as an abstract container that exists regardless of its state or usage. Inflecting the noun without a precise and absolute semantic reason is babbling.

Using the uninflected noun is simple, logical, regular and language-independent.

edited Apr 03 '17 at 17:48

Ry-

199,309
51
404
420

answered Oct 08 '10 at 20:53

Ian Mackinnon

11,717
10
48
62

39

Probably the most logical argument on this subject I've ever seen, and makes me glad I spent that time on Latin. +1 for sure. – Apr 22 '13 at 20:57
61

Well I clearly need to beef up my vocabulary. – TJ Biddle May 21 '13 at 04:54
19

+1 See, these are the kinds of answers the Internet needs more of. Impeccable proofs using rich vocabulary to execute perfect logic. – OCDev Oct 26 '13 at 23:54
9

I'll take note of this next time I'm programming in Latin. In the meantime users will go into the users table and customers into the customers table. – Caltor Aug 03 '15 at 23:18
9

Convinced – uninflected it is. Interesting to see that after all this time, the popular choices of "singular" and "plural" are **both** wrong! – Stuart Aug 21 '15 at 15:51
5

Late to the party, but it makes me curious if you apply the same rule to variable names in (other) programming, when they contain multiple items of a kind (e.g. arrays, lists, sets and so on). `FrequentCustomerArray` rather than `FrequentCustomers`? If not, why is that different? – Thijs van Dien Oct 10 '16 at 12:36
3

Also a late reply. In code you need to identify the difference in plural and singular because the amount of items in your variable matters. In classnames it is different: you wouldn't name your class `Clients` if it's a client. – Arno Jul 02 '18 at 19:38
2

Some lay definitions and examples would be very helpful for those of us who don't have time to get a linguistics degree before creating a table. – MarredCheese Apr 23 '20 at 19:27
"SELECT Name FROM Users" is a prepositional phrase - you are taking stuff out of the users table. So actually, you would want the *ablative* case. Now, English doesn't have different spelling for different cases, but the reasoning for plural is the same - you want a name that will fit into the sentences where it's used. You are saying "Select these things from the list of users," not "Select these things from the list of user." – beleester May 28 '20 at 14:54
1

Singular isn't 'uninflected', it just uses the default count inflection for English that means singular and not plural. – Jason Goemaat Mar 25 '21 at 16:57

Michael Haren · Answer 5 · 2008-12-03T19:28:20.380

129

What convention requires that tables have singular names? I always thought it was plural names.

A user is added to the Users table.

This site agrees:
http://vyaskn.tripod.com/object_naming.htm#Tables

This site disagrees (but I disagree with it):
http://justinsomnia.org/writings/naming_conventions.html

As others have mentioned: these are just guidelines. Pick a convention that works for you and your company/project and stick with it. Switching between singular and plural or sometimes abbreviating words and sometimes not is much more aggravating.

edited Dec 03 '08 at 19:28

answered Dec 03 '08 at 18:13

Michael Haren

97,268
39
159
200

47

When applying set theory to tables, any instance in the set is representative of the set, so Apple is an Apple set, it is agnostic of how many apples are in the set - it is an Apple with many instances. A 'bag' of apples doesn't become a 'bags' when it contains many apples. – ProfK Dec 05 '08 at 14:49
A relation consists of a heading and a body. A heading is a set of attributes. A body (of an n-ary relation) is a set of n-tuples. The heading of the relation is also the heading of each of its tuples. [http://en.wikipedia.org/wiki/Relational_model] – ProfK Dec 05 '08 at 15:02
94

What if you have a bag with 5 apples inside? Do you call it a bag of apple? or a bag of apples? – Christopher Mahan Jan 03 '09 at 07:30
26

I think the theory would be that the set is named Apples. A singular apple is still "a set of Apples" - albeit, a set with a single instance. Multiple apples are also a "set of Apples". – Mark Brackett Apr 04 '09 at 02:16
3

How would you define an array of Apple objects in your code? `Apple apples[100]` or `Apple apple[100]` – joshperry Aug 21 '10 at 00:54
27

@Christopher, if the raison d'être of the bag is to hold apples and only apples, then it is an "apple bag" , regardless of whether it contains 1 apple, 100 apples or no apples. – Ian Mackinnon Oct 07 '10 at 19:58
14

@ Ian: That's because a table is generic, and can be compared to a shipping container (can contain nearly anything, from apple crates to crates of Harley Davidson motorcycles). You say: a cargo container of oranges, not an orange cargo container. You say: a cargo container of car parts, not a car parts cargo container. If you made a custom data structure meant to hold only a specific type of data, such as names of apples, and you named it "kungabogo", then you could have an apple kungaboko. I know what your thinking, but think first of a sac of balls, and understand the difference in meaning. – Christopher Mahan Oct 08 '10 at 04:20
6

@Christopher, a specific database table is designed to hold one type of thing only, forever, and to be associated with this type even when empty, so a shipping container is an inadequate metaphor. They are also abstract concepts, so it's unlikely anyone will confuse them with a scrotum. – Ian Mackinnon Oct 08 '10 at 20:20
3

@Ian: Ok, I agree with you. Tables as implemented cannot hold anything except the thing they are meant to hold. Except of course that they can hold many different kinds of things. Red shirts, blue shirts, soft shirts, short shirts, etc. Shirts with sequins. They all fall into the shirt table. Shirts on sale, shirts on order, defective shirts, shirts being designed, shirts in wrappers, shirts being worn, shirts destroyed, shirts in africa, shirts in pakistan, shirts on the ship, shirts in the box, shirts on the shelf, shirts on the floor. All shirts. In the shirt table. Makes sense. Not. – Christopher Mahan Oct 08 '10 at 20:33
3

These are hilarious examples - I'm inclined to agree with @Ian though @Christopher - I know that down the road is an apple cart. It sells Granny Smith, Jonathon and even Golden Delicious apples. And after all, it's a User table, not a Users table... – Pete - MSFT May 31 '11 at 05:14
1

-1 for not referring to any standard (or authoritative writer). – Bruce Alderson Sep 23 '11 at 20:33
7

My dad, an old DB admin from wayback, refers sometimes to tables using the word "record", presumably as a way of applying the name of the instance to the collection. In other words, he'll say, "the WidgetUser record". From him and a very good DB architect I know, I instinctively picked up that singular is the way the real guys do it. – Tom Haws Dec 05 '11 at 00:00
7

"AppleBag" works best in the singular because it includes the container type, but I do not prefer to suffix "-Table" to every database table that I create. Similarly, it would not make sense to label an apple bag with the singular word "Apple." – Nov 25 '13 at 03:55
The point is: A table is a collection of objects. So, it is obvious to me that a table name should be in plural form. That is, table Users is a collection of objects / entities called User. – Stack0verflow May 07 '15 at 18:49
1

"A user is added to the Users table." It's like to say in Java a variable user belong to the Users class. Or to say that "ints i" is better than "int i" because i is just another int variable (or, in Javascript, "vars i" better than "var i" because i it's just another variable). With this approach a Database should be called Tables – Plap Sep 18 '15 at 13:57
I don't think is fair to compare tables with Java classes. A class works fundamentally different than a DB table (a collection of specific elements). It would be better to compare tables with arrays, I believe. – Sebastianb Jan 02 '17 at 15:00
@Plap Users is not a class, it is a Collection of that class or a list. You ad a user to a users list. – JSON Feb 05 '19 at 17:54
1

Plural is the way to go. The only argument against it doesn't hold water, in that selecting relationships. SELECT * from Users where Users.Name = "somename". This should really be written as SELECT * from Users user where user.Name = "somename". Or think of it in entity framework. Users.Where(user => user.Name == "somename"). A table is a collection entities, a table IS NOT A RELATION. A relation is a a characteristic of the entity, nothing more. Table names should be plural – JSON Feb 05 '19 at 18:00

score 85 · Answer 6 · edited Apr 18 '15 at 02:16

85

How about this as a simple example:

SELECT Customer.Name, Customer.Address FROM Customer WHERE Customer.Name > "def"

vs.

SELECT Customers.Name, Customers.Address FROM Customers WHERE Customers.Name > "def"

The SQL in the latter is stranger sounding than the former.

I vote for singular.

edited Apr 18 '15 at 02:16

Paulo Freitas

11,380
13
68
93

answered Apr 04 '09 at 01:24

58

In that example, yes, but in practical sql it would never be written that way. You'd have a table alias so it'd be more like `SELECT C.Name, C.Address FROM Customers WHERE Customers.Name > 'def'` – Michael Haren Aug 21 '09 at 22:37
+1, (a year later) You cited a TERRIFIC example on how the singular makes sense. This is somewhat a religious debate. I was turned onto the singular several years ago by a data architect many years my senior and it's felt right to me (after requiring much convincing for me to switch). – Chris Adragna Oct 04 '10 at 13:52
27

I think the sql sounds better plural. You wouldn't have table names for each column, why do you like typing so much. SELECT Name, Address FROM Customers WHERE Name > "def" You're selecting from the pool of customers where the name greater then def. – jamiegs Apr 06 '11 at 01:06
6

How about using an alias/AS to get around that one issue? SELECT Customer.Name, Customer.Address FROM Customers AS Customer WHERE Customer.Name > "def" – James Hughes Jun 15 '11 at 16:45
2

The second one sounds much better, singular sounds like someone who can't speak English. – HLGEM Aug 30 '13 at 15:00
I know this is old, but if you think about it it's select every customer's name, customer's address from the customers table. By using plural, you always remember that it will be a set that is returned, even if this set contains only one item. – Marco Sep 30 '19 at 20:26

Adam Carr · Answer 7 · 2009-01-03T17:01:18.117

63

I am of the firm belief that in an Entity Relation Diagram, the entity should be reflected with a singular name, similar to a class name being singular. Once instantiated, the name reflects its instance. So with databases, the entity when made into a table (a collection of entities or records) is plural. Entity, User is made into table Users. I would agree with others who suggested maybe the name User could be improved to Employee or something more applicable to your scenario.

This then makes more sense in a SQL statement because you are selecting from a group of records and if the table name is singular, it doesn't read well.

edited Jan 03 '09 at 17:01

answered Jan 02 '09 at 23:38

Adam Carr

2,936
7
28
37

4

I especially like the SQL statement comment. Using singular here does not feel intuitive to the reader. – hochl Oct 05 '11 at 11:18
2

Excellent point about the ERD. I suspect this is why, to someone who sees the world through DBA eyes, singular naming makes sense. I suspect they don't get, as you point out, the difference between an entity and a collection of them. – William T. Mallard May 09 '14 at 01:02
1

A table is not a collections of records; a table is a definition of what a record looks like. That's the disconnect all the plural/singular folk seem to have. – rich remer Sep 21 '16 at 20:05
I disagree with the SQL statement comment. What if you want to join against the Users.Id – hashtable Nov 30 '18 at 16:31
1

One old_lady has many cat OR old_ladies have cats. I think ERDs read nicer as plural. And table of entities, tables have many entities so again I think plural sounds nice. – user3121518 Jul 08 '19 at 02:05

score 44 · Answer 8 · edited Apr 18 '15 at 02:09

44

I stick with singular for table names and any programming entity.

The reason? The fact that there are irregular plurals in English like mouse ⇒ mice and sheep ⇒ sheep. Then, if I need a collection, i just use mouses or sheeps, and move on.

It really helps the plurality stand out, and I can easily and programatically determine what the collection of things would look like.

So, my rule is: everything is singular, every collection of things is singular with an s appended. Helps with ORMs too.

edited Apr 18 '15 at 02:09

Paulo Freitas

11,380
13
68
93

answered Dec 05 '08 at 01:15

Ash Machine

8,770
9
43
51

7

what about a word ending with a 's'? If you have a table called 'News' (just as an example), what would you call the collection of news? Newss? Or would you call the table 'New'? – Anthony Aug 28 '09 at 11:35
19

I would call the table NewsItem and a collection NewsItems. – Ash Machine Aug 28 '09 at 20:46
5

What if you have to spell-check all code or else it will not compile ;) ? – Hamish Grubijan Jun 08 '10 at 15:33
@Hamish: you actually spell check code with common entries like orderby, sbyte, const, sizeof? Remind me again? – Ash Machine Mar 25 '11 at 20:25
2

The ORM should not dictate the names of the objects they map to. The point of ORM is an abstraction of the object, granting this flexibilty. – barrypicker Sep 14 '11 at 23:55
1

`fishs` doesn't work so well with this rule. Pluralizing the names of collections in the ORM isn't as hard, because you can look at the object type to map to the singular table name. Then the property can be named whatever you want, and you can let your ide auto-completion handle the rest. – Merlyn Morgan-Graham Oct 28 '11 at 08:46
17

@HamishGrubijan then stop using Word to write your code! ;) – Valentino Vranken Feb 08 '12 at 12:22
Adding to what you said, there's another kind of example: category and categories. Laravel's Eloquent ORM assumes the lowercase plural of the model name. I wonder why they didn't consider singular as the default option. – Karma Sep 15 '14 at 21:45
You contradicted yourself. You said 'every collection of things is singular with an s appended' but you state that table names should be singular. By your own admission, table names should be plural (or as you pointed out, pseudo-plural) because a table is a collection of data in its singular form. (i.e. a a table holding cars is a collection of car objects therefore it should be called 'cars') If you like singular for the car.doors syntax, use an alias. (i.e. Select car.WheelCount from Cars as car where car.Id = 44) – Mark A. Donohoe Jul 09 '15 at 04:15
@MarqueIV : when I said "a collection of things" being plural, I meant the collection in the object domain, not data domain. For Example, a list, an array, an IEnumerable would be collections (plural). A data table would be singular. Hope this is helpful. – Ash Machine Jul 13 '15 at 15:50
It comes down to if you're naming the table as a distinct object (i.e. a CarTable) or as the objects it contains (Cars). The mistake in logic that I have with people who would call the table 'Car' is the table is not a car! It is a CarTable which is the type of thing that it is, equivalent to a data type. A CarCollection (also singular.) As soon as you give that 'data type' a name (i.e. a variable name) you usually describe what it's used as, in this case a container/collection/whatever of Car objects, therefore the DataType should be CarTable but the name of the table should be Cars. – Mark A. Donohoe Jul 13 '15 at 20:23
@MarqueIV : OK have it your way, as SOF indicates, the answers are primarily opinion based. So I am glad you have yours. – Ash Machine Jul 13 '15 at 20:34
Aaah... the old 'Agree to disagree' stance! lol Fair enough. I'll concede to that. – Mark A. Donohoe Jul 13 '15 at 23:50
This answer has a very good point. Mapping `Mouse` class to `Mice` table just feels so wierd. – Thupten Jul 15 '15 at 12:59

score 37 · Answer 9 · edited Apr 18 '15 at 02:10

37

IMHO, table names should be plural like Customers.

Class names should be singular like Customer if it maps to a row in the Customers table.

edited Apr 18 '15 at 02:10

Paulo Freitas

11,380
13
68
93

answered Apr 30 '09 at 21:03

Gulzar Nazim

50,518
24
125
170

score 35 · Answer 10 · edited Dec 04 '12 at 07:00

Singular. I don't buy any argument involving which is most logical - every person thinks his own preference is most logical. No matter what you do it is a mess, just pick a convention and stick to it. We are trying to map a language with highly irregular grammar and semantics (normal spoken and written language) to a highly regular (SQL) grammar with very specific semantics.

My main argument is that I don't think of the tables as a set but as relations.

So, the AppUser relation tells which entities are AppUsers.

The AppUserGroup relation tells me which entities are AppUserGroups

The AppUser_AppUserGroup relation tells me how the AppUsers and AppUserGroups are related.

The AppUserGroup_AppUserGroup relation tells me how AppUserGroups and AppUserGroups are related (i.e. groups member of groups).

In other words, when I think about entities and how they are related I think of relations in singular, but of course, when I think of the entities in collections or sets, the collections or sets are plural.

In my code, then, and in the database schema, I use singular. In textual descriptions, I end up using plural for increased readability - then use fonts etc. to distinguish the table/relation name from the plural s.

I like to think of it as messy, but systematic - and this way there is always a systematically generated name for the relation I wish to express, which to me is very important.

exactly. the main thing many people are not aware here is what they are naming... you are giving name to a relation (a single record in the table), not the set of records in the table. — Alexandre Martini, Dec 03 '14 at 16:55
Couldn't disagree more. 'Select * from Users where Name like 'J%'' because I am selecting all users where the name starts with 'J'. If your argument is that you want to write '...where User.Name like...' then simply use an alias. Same reason I say 'Give me a pair from all available socks.' — Mark A. Donohoe, Jul 09 '15 at 04:38
@AlexandreMartini Exactly. Like some people who call _a single record in the table_ "relation". — Nuno André, Mar 20 '20 at 18:07

score 31 · Answer 11 · edited Apr 18 '15 at 02:23

31

I also would go with plurals, and with the aforementioned Users dilemma, we do take the square bracketing approach.

We do this to provide uniformity between both database architecture and application architecture, with the underlying understanding that the Users table is a collection of User values as much as a Users collection in a code artifact is a collection of User objects.

Having our data team and our developers speaking the same conceptual language (although not always the same object names) makes it easier to convey ideas between them.

edited Apr 18 '15 at 02:23

Paulo Freitas

11,380
13
68
93

answered Dec 03 '08 at 20:23

Joseph Ferris

12,244
3
43
72

10

I agree.. why the inconsistency between code and storage? I would never name a collection of user objects "User" in code... so why would I call a table that? It makes no sense. When I read the arguments above about it, they are focusing on the entity, not the table... there is a distinction between whats in the table than the table itself in my mind. – Jason Mar 12 '12 at 20:39
How do you deal with a table name like `companies` where other tables have a referencing field called `company_id`? While it's properly spelled, it seems inconsistent for those that are picky about table naming conventions. – Jake Wilson Feb 15 '16 at 16:46
2

By remembering that the singular of `companies` is `company`, and that this id is a reference to a singular item. It shouldn't bother us in code any more than it bothers us in English. – David Jan 04 '18 at 19:47

score 23 · Answer 12 · answered Feb 26 '13 at 18:39

I personaly prefer to use plural names to represent a set, it just "sounds" better to my relational mind.

At this exact moment i am using singular names to define a data model for my company, because most of the people at work feel more confortable with it. Sometimes you just have to make life easier to everyone instead of imposing your personal preferences. (that's how i ended up in this thread, to get a confirmation on what should be the "best practice" for naming tables)

After reading all the arguing in this thread, i reached one conclusion:

I like my pancakes with honey, no matter what everybody's favorite flavour is. But if i am cooking for other people, i will try to serve them something they like.

It is not wise to use such convention in relational model world, especially when you describe relation between objects, e.g. "Each Team may have only one Main Coach and many secondary Coaches" , which is described: Team->MainCoach , Team->>SecondaryCoach — noonex, Aug 02 '15 at 13:04

score 17 · Answer 13 · edited Apr 18 '15 at 02:15

17

I've actually always thought it was popular convention to use plural table names. Up until this point I've always used plural.

I can understand the argument for singular table names, but to me plural makes more sense. A table name usually describes what the table contains. In a normalized database, each table contains specific sets of data. Each row is an entity and the table contains many entities. Thus the plural form for the table name.

A table of cars would have the name cars and each row is a car. I'll admit that specifying the table along with the field in a table.field manner is the best practice and that having singular table names is more readable. However in the following two examples, the former makes more sense:

SELECT * FROM cars WHERE color='blue'
SELECT * FROM car WHERE color='blue'

Honestly, I will be rethinking my position on the matter, and I would rely on the actual conventions used by the organization I'm developing for. However, I think for my personal conventions, I'll stick with plural table names. To me it makes more sense.

edited Apr 18 '15 at 02:15

Paulo Freitas

11,380
13
68
93

answered Sep 17 '10 at 20:10

Randy

1
1
2

9

Isn't this the convention in RoR too? Plural names for tables and Singular for ORM classes? Makes a lot of sense to me. Table is called "cars" because it has many instances of "car" and class is called "Car" because it will hold one instance of a car!! – Sap Jul 14 '11 at 04:38
@Sap A minor correction of the latter part of your sentence - The class "Car" is an Abstract DataType representing a real-life Car. Whether it'll hold one instance or multiples depends on how it's used. – asgs Oct 10 '16 at 06:15
lets face it, table `car` is a definition of the structure of a single car. If you look at the structure of the table, it will spit out basically "id int, color string etc" furthermore: say you have a table `car_vendor` (or for your plural version it would be `cars_vendor`) with the foreign key `cars_id` ?! what is that stupid shit? it is `car_id` no need to make me think. Singular is strongly preferred by me – Toskan Jan 04 '17 at 15:51
4

I really like this answer! Let me explain. If the collection is `car` and you want everything from the `car` that is `blue` the result should be something like `tire, mirror, engine`. And then it is getting confusing because all results are `parts` from a `car`. So the table name should be `carparts` (or `car_parts`, `CarParts` whatever you like) – arnoudhgz Jan 06 '17 at 13:18
Any database designer who enforces singular table names is basically declaring war against any Ruby on Rails app developers who may come into touch with that database in the future. Rail's strict insistence on singular words for classes, and pluralized names for tables, enables a lot of powerful behaviour within many gems inside Ruby's ecosystem. So even if you think singular sounds better, for the sake of compatibility you should stick to plural. I imagine this holds true for many other Object Relational Mappers too. – Kelsey Hannan Jan 26 '18 at 10:39
Real problems begin when you have to join ```SELECT cars.id, cars.name, cars.speed, cars_parts.engine, cars_parts.something FROM cars LEFT JOIN cars_parts ON cars_parts.cars_id=cars.id``` vs ```SELECT car.id, car.name, car.speed, cars_part.engine, car_part.something FROM car LEFT JOIN car_part ON car_part.car_id=car.id``` the last query is more descriptive when you have to mention a field – BIOHAZARD Oct 24 '19 at 13:02
In practical SQL, you would rather use alias to table: `SELECT c.id, c.name, c.speed, cp.engine, cp.something FROM cars c LEFT JOIN car_parts cp ON cp.car_id=c.id` – krozaine May 06 '20 at 19:32

score 16 · Answer 14 · answered Jan 02 '09 at 23:48

16

Singular. I'd call an array containing a bunch of user row representation objects 'users', but the table is 'the user table'. Thinking of the table as being nothing but the set of the rows it contains is wrong, IMO; the table is the metadata, and the set of rows is hierarchically attached to the table, it is not the table itself.

I use ORMs all the time, of course, and it helps that ORM code written with plural table names looks stupid.

answered Jan 02 '09 at 23:48

chaos

115,791
31
292
308

To each his own, I guess. A relational database table is by definition a heading (i.e. metadata naming the attributes) and a set of tuples matching the heading. You can focus on the metadata, whereas other folks focus on the tuples. :-) – Bill Karwin Jan 03 '09 at 19:43
Hey, User_Table is a name I like! :) – Camilo Martin May 09 '10 at 00:25
4

The ORM should not dictate the names of the objects they map to. The point of ORM is an abstraction of the object, granting this flexibilty. – barrypicker Sep 14 '11 at 23:56
1

I look at it this way.. if you create an array/list/dictionary of anything in code, my bet is you name it with the plural name of whatever it holds. If you are using an ORM to abstract your database, tables are represented with some sort of collection, so why would you treat them any different? To use singular names may sound good, but you are always fighting your instinct that a table holds many of the same thing, just like a collection in code does. Why the inconsistency? – Jason Mar 12 '12 at 20:19
@Jason: Please compare and contrast the way these things read: 1) `$db->user->row(27)`, `$db->product->rows->where(something)` 2) `$db->users->row(27)`, `$db->products->rows->where(something)`. – chaos Mar 12 '12 at 20:35
In SQLAlchemy, the examples would be `User.query.get(27)` or `User.query.filter(User.id == 27).one()`, and `Product.query.filter(something).all()`, regardless of how the tables are named. Like @barrypicker said, the ORM you use is not flexible enough. – sayap Nov 24 '12 at 03:02
@barrypicker The grammar should not dictate the programming conventions (do you know the java beans is/get idiocy?). It should be followed as long as it doesn't come into way. The possibility to use a different mapping in an ORM is there for cases when it's needed. Grammar is much to irregular and things like "matrixes" vs "matrices" are rare but strong examples for why the code shouldn't be infested by it. – maaartinus May 05 '18 at 03:21

score 15 · Answer 15 · answered Dec 03 '08 at 19:28

I don't like plural table names because some nouns in English are not countable (water, soup, cash) or the meaning changes when you make it countable (chicken vs a chicken; meat vs bird). I also dislike using abbreviations for table name or column name because doing so adds extra slope to the already steep learning curve.

Ironically, I might make User an exception and call it Users because of USER (Transac-SQL), because I too don't like using brackets around tables if I don't have to.

I also like to name all the ID columns as Id, not ChickenId or ChickensId (what do plural guys do about this?).

All this is because I don't have proper respect for the database systems, I am just reapplying one-trick-pony knowledge from OO naming conventions like Java's out of habit and laziness. I wish there were better IDE support for complicated SQL.

Us plural guys either name the 'id' column 'id' like you do, or 'singular_id'. I believe tables should be plural (think of them like arrays), but column names should be singular (attributes of a single element). — mpen, Apr 04 '09 at 02:29
plu_ral/PluRal for table names, singular_id/singularId for primary keys. — hochl, Oct 05 '11 at 11:26

score 14 · Answer 16 · answered Dec 03 '08 at 19:24

14

We run similar standards, when scripting we demand [ ] around names, and where appropriate schema qualifiers - primarily it hedges your bets against future name grabs by the SQL syntax.

SELECT [Name] FROM [dbo].[Customer] WHERE [Location] = 'WA'

This has saved our souls in the past - some of our database systems have run 10+ years from SQL 6.0 through SQL 2005 - way past their intended lifespans.

answered Dec 03 '08 at 19:24

stephbu

5,011
24
40

Seems like ritualistic self-flagellation. Has any such name grabs happened yet? – Kjetil S. Feb 20 '19 at 22:02

score 14 · Answer 17 · edited Apr 28 '18 at 14:26

14

The system tables/views of the server itself (SYSCAT.TABLES, dbo.sysindexes, ALL_TABLES, information_schema.columns, etc.) are almost always plural. I guess for the sake of consistency I'd follow their lead.

edited Apr 28 '18 at 14:26

answered Dec 03 '08 at 20:20

Michel

1,418
11
15

Microsoft are what they are for business reasons first (and often unethical reasons at that), logical reasons last. My only reason for following them would be that they are the big gorilla and everyone else goes that way. When I have a choice, I choose the other way. – Bruce Patin Nov 30 '12 at 16:38
3

It should be noted that the `information_schema` is part of ISO/IEC 9075-11, the SQL standard. And yes, it does use plural tables/views names. – Paulo Freitas May 29 '18 at 00:53

score 13 · Answer 18 · answered Nov 25 '09 at 21:23

13

Tables: plural

Multiple users are listed in the users table.

Models: singular

A singular user can be selected from the users table.

Controllers: plural

http://myapp.com/users would list multiple users.

That's my take on it anyway.

answered Nov 25 '09 at 21:23

Andrew

196,883
184
487
673

1

Closer to my take, but mine is that the table's storage of multiple users is actually incidental, and that any singular user is represented by the table, or rather the relation that is a set of tuples representing a User entity. – ProfK Nov 26 '09 at 08:54
I think I tend to agree with this. The only thing confuses me is why should models be singular? Only if the model is only concerned with a single User. If I was querying the db to get all users then would I need to access the model? It does not make sense for a singular instance to fetch all records, example: $user->get_all() //does not make sense – Apr 05 '20 at 00:11

score 12 · Answer 19 · answered Jan 02 '09 at 23:50

I am a fan of singular table names as they make my ER diagrams using CASE syntax easier to read, but by reading these responses I'm getting the feeling it never caught on very well? I personally love it. There is a good overview with examples of how readable your models can be when you use singular table names, add action verbs to your relationships and form good sentences for every relationships. It's all a bit of overkill for a 20 table database but if you have a DB with hundreds of tables and a complex design how will your developers ever understand it without a good readable diagram?

http://www.aisintl.com/case/method.html

As for prefixing tables and views I absolutely hate that practice. Give a person no information at all before giving them possibly bad information. Anyone browsing a db for objects can quite easily tell a table from a view, but if I have a table named tblUsers that for some reason I decide to restructure in the future into two tables, with a view unifying them to keep from breaking old code I now have a view named tblUsers. At this point I am left with two unappealing options, leave a view named with a tbl prefix which may confuse some developers, or force another layer, either middle tier or application to be rewritten to reference my new structure or name viewUsers. That negates a large part of the value of views IMHO.

Good example of a pitfall of prefixing object names with a 'type' qualifier! — Kenny Evitt, Nov 08 '10 at 16:14

score 11 · Answer 20 · edited Apr 28 '18 at 13:34

11

If we look at MS SQL Server's system tables, their names as assigned by Microsoft are in plural.

The Oracle's system tables are named in singular. Although a few of them are plural. Oracle recommends plural for user-defined table names. That doesn't make much sense that they recommend one thing and follow another. That the architects at these two software giants have named their tables using different conventions, doesn't make much sense either... After all, what are these guys ... PhD's?

I do remember in academia, the recommendation was singular.

For example, when we say:

select OrderHeader.ID FROM OrderHeader WHERE OrderHeader.Reference = 'ABC123'

maybe b/c each ID is selected from a particular single row ...?

edited Apr 28 '18 at 13:34

answered Nov 02 '12 at 21:48

Richard

1
1
2

1

Microsoft are what they are for business reasons first (and often unethical reasons at that), logical reasons last. My only reason for following them would be that they are the big gorilla and everyone else goes that way. When I have a choice, I choose the other way. – Bruce Patin Nov 30 '12 at 16:36
1

Two things. One, you normally wouldn't use the table names and would write 'select ID FROM OrderHeaders WHERE Reference = 'ABC123' because you are 'Selecting all IDs from OrderHeaders where something is true' but if you had to use table names because of a join or whatever, you would use an alias like so... 'select OrderHeader.ID FROM OrderHeaders as OrderHeader WHERE OrderHeader.Reference = 'ABC123' – Mark A. Donohoe Jul 09 '15 at 04:48

score 10 · Answer 21 · edited Oct 10 '16 at 06:11

10

I've always used singular simply because that's what I was taught. However, while creating a new schema recently, for the first time in a long time, I actively decided to maintain this convention simply because... it's shorter. Adding an 's' to the end of every table name seems as useless to me as adding 'tbl_' in front of every one.

edited Oct 10 '16 at 06:11

asgs

3,718
6
36
49

answered Oct 21 '10 at 01:37

Dave Markle · Answer 22 · 2009-01-03T07:20:53.017

9

Possible alternatives:

Rename the table SystemUser
Use brackets
Keep the plural table names.

IMO using brackets is technically the safest approach, though it is a bit cumbersome. IMO it's 6 of one, half-a-dozen of the other, and your solution really just boils down to personal/team preference.

edited Jan 03 '09 at 07:20

answered Dec 03 '08 at 18:15

Dave Markle

88,065
20
140
165

2

I like your 'prefix' idea, but would call it SystemUser. – ProfK Dec 05 '08 at 14:50

score 9 · Answer 23 · answered Dec 03 '08 at 18:33

This may be a bit redundant, but I would suggest being cautious. Not necessarily that it's a bad thing to rename tables, but standardization is just that; a standard -- this database may already be "standardized", however badly :) -- I would suggest consistency to be a better goal given that this database already exists and presumably it consists of more than just 2 tables.

Unless you can standardize the entire database, or at least are planning to work towards that end, I suspect that table names are just the tip of the iceberg and concentrating on the task at hand, enduring the pain of poorly named objects, may be in your best interest --

Practical consistency sometimes is the best standard... :)

my2cents ---

score 9 · Answer 24 · answered Oct 04 '11 at 18:23

My take is in semantics depending on how you define your container. For example, A "bag of apples" or simply "apples" or an "apple bag" or "apple".

Example: a "college" table can contain 0 or more colleges a table of "colleges" can contain 0 or more collegues

a "student" table can contain 0 or more students 
a table of "students" can contain 0 or more students.

My conclusion is that either is fine but you have to define how you (or people interacting with it) are going to approach when referring to the tables; "a x table" or a "table of xs"

score 8 · Answer 25 · answered Nov 30 '12 at 16:28

8

I once used "Dude" for the User table - same short number of characters, no conflict with keywords, still a reference to a generic human. If I weren't concerned about the stuffy heads that might see the code, I would have kept it that way.

answered Nov 30 '12 at 16:28

Bruce Patin

305
7
9

score 8 · Answer 26 · answered Mar 10 '10 at 11:08

8

I think using the singular is what we were taught in university. But at the same time you could argue that unlike in object oriented programming, a table is not an instance of its records.

I think I'm tipping in favour of the singular at the moment because of plural irregularities in English. In German it's even worse due to no consistent plural forms - sometimes you cannot tell if a word is plural or not without the specifying article in front of it (der/die/das). And in Chinese languages there are no plural forms anyway.

answered Mar 10 '10 at 11:08

helloworlder

1,385
13
18

In university I am taught plural for tables, I also have a book here, DB management third edition from the 90's, tables are singular; while I also have an updated copy, 11e, singular and some abbreviated names, while the XML section uses plural. \n But if you check the actual content for the RDBMS sections, it's literally still the same text with some images having gotten a face lift. \n The "data modelling checklist" states nothing on plural vs singular, just that entities should only map to a single object, that's probably what they were trying to enforce in the books. – Marco Sep 30 '19 at 20:39

score 8 · Answer 27 · answered Dec 03 '08 at 19:11

As others have mentioned here, conventions should be a tool for adding to the ease of use and readability. Not as a shackle or a club to torture developers.

That said, my personal preference is to use singular names for both tables and columns. This probably comes from my programming background. Class names are generally singular unless they are some sort of collection. In my mind I am storing or reading individual records in the table in question, so singular makes sense to me.

This practice also allows me to reserve plural table names for those that store many-to-many relationships between my objects.

I try to avoid reserved words in my table and column names, as well. In the case in question here it makes more sense to go counter to the singular convention for Users to avoid the need to encapsulate a table that uses the reserved word of User.

I like using prefixes in a limited manner (tbl for table names, sp_ for proc names, etc), though many believe this adds clutter. I also prefer CamelBack names to underscores because I always end up hitting the + instead of _ when typing the name. Many others disagree.

Here is another good link for naming convention guidelines: http://www.xaprb.com/blog/2008/10/26/the-power-of-a-good-sql-naming-convention/

Remember that the most important factor in your convention is that it make sense to the people interacting with the database in question. There is no "One Ring to Rule Them All" when it comes to naming conventions.

Ignoring the horrors of hungarian notation. Never, never, never use sp_ in front of stored procedures because MS-SQL uses that for system stored procedures and treats them special. Since sp_ are stored in the master table, MS-SQL always looks thier first even if you qualify the location. — Will Dieterich, Dec 08 '08 at 07:59

score 7 · Answer 28 · answered May 27 '12 at 18:51

7

I only use nouns for my table names that are spelled the same, whether singular or plural:

moose fish deer aircraft you pants shorts eyeglasses scissors species offspring

answered May 27 '12 at 18:51

bobby

1
1
2

score 7 · Answer 29 · answered Dec 03 '08 at 18:17

7

I always thought that was a dumb convention. I use plural table names.

(I believe the rational behind that policy is that it make it easier for ORM code generators to produce object & collection classes, since it is easier to produce a plural name from a singular name than vice-versa)

answered Dec 03 '08 at 18:17

James Curran

95,648
35
171
253

11

This convention has been part of relational theory long, long, before ORM ever existed. – ProfK Dec 05 '08 at 14:51
1

The ORM should not dictate the names of the objects they map to. The point of ORM is an abstraction of the object, granting this flexibilty. – barrypicker Sep 15 '11 at 00:07

score 6 · Answer 30 · answered Feb 18 '13 at 04:32

I did not see this clearly articulated in any of the previous answers. Many programmers have no formal definition in mind when working with tables. We often communicate intuitively in terms of of "records" or "rows". However, with some exceptions for denormalized relations, tables are usually designed so that the relation between the non-key attributes and the key constitutes a set theoretic function.

A function can be defined as a subset of a cross-product between two sets, in which each element of the set of keys occurs at most once in the mapping. Hence the terminology arising from from that perspective tends to be singular. One sees the same singular (or at least, non-plural) convention across other mathematical and computational theories involving functions (algebra and lambda calculus for instance).

Bruce Patin · Answer 31 · 2012-11-30T16:49:09.333

5

The SQL definition of a table is in actuality the definition of one potential row of the table, not the collection. Therefore, the name used in that definition must designate the type of the row, not the name of the collection. People who prefer plural because it reads well in their English statements need to start thinking more logically and look at all of the logic and programming code that is involved with actually using a table. There are several very good reasons mentioned in these comments to use singular table names. These include very good reasons NOT to use plural table names. "Reading well" should not be any reason at all, especially since some may read the idea differently.

edited Nov 30 '12 at 16:49

answered Nov 30 '12 at 16:24

Bruce Patin

305
7
9

No, the SQL definition of a table is the definition of *all* rows that will be in that table, not one potential row as you said. As an example, using your definition if I say a description of one potential date this weekend is a redhead, that wouldn't mean I couldn't also go out with a brunette. However saying that *all* potential dates this weekend are redheads *would* preclude that, which is exactly what a table does. It restricts *all* rows to match that description, or put another way, it's a description for all of the rows--the word 'rows' being plural hence so should the table be. – Mark A. Donohoe Jul 09 '15 at 04:46
Extending that idea, every column in the definition should also be plural. – Bruce Patin Nov 03 '17 at 20:10
I see how you got there, but I would argue that’s because you are looking at the column as a container, which it isn’t. Put another way, a column is just metadata for storing a property of the row. Columnar data itself doesn’t exist outside of a row because a row is where it’s stored. When you query on a column, you’re querying the rows for that column data. You’re not querying the column autonomously. The row is the entity. The table is a collection of those entities. A column is just a property on that entity. – Mark A. Donohoe Nov 03 '17 at 20:37
I was being facetious. A table might contain a collection of rows, but a table definition is not the definition of a collection, it is a definition of a table row, and therefore must be singular. If you get into translating table definitions into class definitions of the programs that deal with it, all of this will be abundantly clear. – Bruce Patin Nov 07 '17 at 16:24

score 5 · Answer 32 · answered Dec 03 '08 at 18:19

5

Guidelines are really there as just that. It's not "set in stone" that's why you have the option of being able to ignore them.

I would say that it's more logically intuitive to have pluralized table names. A table is a collection of entity after all. In addition to other alternatives mentioned I commonly see prefixes on table names...

tblUser
tblThis
tblThat
tblTheOther

I'm not suggesting this is the way to go, I also see spaces used a LOT in table names which I abhor. I've even come across field names with idiotic characters like ? as if to say this field answers a question.

answered Dec 03 '08 at 18:19

BenAlabaster

36,299
19
100
147

4

Agreed. Spaces and prefixes are from the Devil. – Dave Markle Dec 03 '08 at 18:24
2

MSAccess encourages table names with spaces. I suspect many MSSQL tables in spaces were imported from there. – James Curran Dec 03 '08 at 18:26
4

+1 again. Tables and Fields named with spaces are the mark of the whizz kid Office Junior making a real kool access application for Beryl in account :-). – Cruachan Dec 03 '08 at 20:30
1

Hey, I like Beryl in accounts... but it wouldn't ever cause me to put prefixes or spaces in my table names... nor would it cause me to put question marks or exclamation marks in my field names. I don't care how cute she is. :P – BenAlabaster Dec 03 '08 at 20:53
3

tbl as a prefix is the son of devilspawn and smelly carrion, but semantic prefixes are a little better. In our main application, we, Chase Software, prefix all our tables with 'ca' or 'cs', being chase application, or chase system. – ProfK Dec 05 '08 at 14:52
1

(continued) 'ca' tables are business tables and change regularly in day to day use, such as caDocument, which is invoices, PO's, cost sheets, etc. 'cs' tables are more for navigation etc. such as csForm, which we use to navigate between forms (pages), and is normally only changed by us. – ProfK Dec 05 '08 at 14:52
1

I spent hours once trying to select from a table that had a _single leading space_ prefix. I thought I'd lost my mind. – scipilot Dec 29 '13 at 10:01

score 4 · Answer 33 · answered Dec 03 '08 at 20:16

4

If you go there will be trouble, but if you stay it will be double.

I'd much rather go against some supposed non-plurals naming convention than name my table after something which might be a reserved word.

answered Dec 03 '08 at 20:16

Patrick Harrington

41,248
5
21
19

score 4 · Answer 34 · edited Apr 18 '15 at 02:19

4

I will just give my opinion why I use singular names.

For example, I need to get all the fields from an user:

-- Select every fields from 'user' table
SELECT * FROM user

I need the name of the user that is 21 years old:

-- Select every fields from 'user' table which have 21 years old
SELECT * FROM user WHERE age = '21'

Of course the plural way can be used by the same means, but for my brain to read, I really think that's the right way to go.

edited Apr 18 '15 at 02:19

Paulo Freitas

11,380
13
68
93

answered Jan 20 '09 at 13:57

nicruo

500
4
13

3

I disagree; In oracle database there is a system table called USER, now you have to decide to call it "APP_USER" or something like that. But given that it sounds better to say "SELECT ALL FROM USERS" anyways, I'd just name all of tables the plural form of my entities, as to not conflict with any system table. – cosbor11 Jun 19 '15 at 23:23
1

I like select from `users` or `userTable`, but not `user`. With singular it sounds like you are getting just one record. I see that you like to think select columns from table. Most like to think select returns rows from table, thus a collection name sounds logical. – Thupten Jul 15 '15 at 12:52
1

I will give you my 2 cents, Will your database table consist out of 1 row or multiple rows? It's like saying: I have five apple in both hand. Yet we both agree the correct phrase would be: I have five apples in both hands. Why do we want to change this matter for database? As soon as you assign 'userTable' as name we have 1 table, which actually already defines the existance of multiple data rows. However I wouldn't stick with that neither. I rather say go with plural. As age = '21' would most likely return more than 1 rows isn't it? – Mike M. Dec 11 '15 at 22:41
2

Singular are the standard. – danger89 Jan 26 '16 at 12:09
1

Look at app languages like Rail's ActiveRecord. It strictly enforces a separation with table names being plural and object entities of them being singular. So much amazing stuff comes out of following this convention, and you are hurting your app team's about to reason with their system by not following it. – Kelsey Hannan Jan 26 '18 at 10:28
Except for the pain of having to deal with nonstandard pluralization in ActiveRecord... I've always used plural table names, but pluralization without fail creates annoynances - both in ORMs and JSON standards - that singular table names never do. – kevlarr Apr 04 '18 at 01:22
+Kelsey Hannan Fair point, albeit some folks like the singular convention, but certain frameworks (like Rails) use plural as their convention...so sometimes choosing singular vs plural is project-specific.... – twknab Sep 25 '18 at 09:19
'Select every fields from 'user' table which have 21 years old' --> Select every field from every user (multiple) who is 21 years old. So it's more logical to have plural. – Simon Fakir Nov 14 '18 at 21:59

score 3 · Answer 35 · answered Apr 09 '12 at 20:39

3

A TABLE name is ONE definition of a table structure. A VIEW or QUERY name is ONE definition of a view or query of (a or many) tables. A TABLE, VIEW or QUERY may contain one of the following:

0 records 1 record Many records.

Why on earth would you want to put an 's' on the end of a single object name? What do you want to signify by placing this 's' on the end of an object name?

If you want to differentiate, then add '_tbl'. A View is '_vew' (not the silly '_v' convention).

Minimum 3 character suffixing - that stops this discussion dead.

A table is a DB object - no different to any other.

The saving of 3 characters saves nothing except clarity of meaning.

Red ;-)

answered Apr 09 '12 at 20:39

Red

1
1

5

How much harder is it to add 'Table' than to add '_tbl'? – ProfK May 28 '12 at 08:07
For more clarity, a table definition is actually the definition of ONE potential row. – Bruce Patin Nov 30 '12 at 16:46
1

By your own definition, you have given two different things the same name... the object that will be placed in the table, and the table which holds those objects. If I say Car, am I referring to the table or a record in that table? If I say Cars, it's clear I mean the thing that holds zero or more car objects. Using plurals properly differentiates an item from the thing that holds them. if you were to say CarTable that is singular. But without the suffix 'table' you have to give it a name representing what it holds. A 'Sock Drawer' (singular) has the label (name) 'Socks' on it. – Mark A. Donohoe Jul 09 '15 at 04:59

score 3 · Answer 36 · answered Jun 30 '13 at 19:09

While looking for good naming covention, the following confusion arises as should i name :

1) acc. to what the table holds eg: A table of users. Its always going to be plural. So, Users

2) acc. to what a record holds eg: A record in users tables will a single user. SO, User.

Now, the problem for users_roles majorly. case 1: acc. to first naming convention, users_roles What this name suggest, users and their roles.

case 2: acc. to second naming convention, user_role What this name suggest, user and his roles.

Good naming convention is to give a additional idea of Entity relationships, especially when many to many relationships are stored.

Here, as per the scenario, we should identify as sets of information.

In users table, all sets formed are unique user. In Roles table, all sets formed are unique roles. In user and roles relationship table, sets of users can be formed with different roles which gives an idea of 1-many relationships stored.

I would prefer,
Users table => user
Roles table => role
users role relationship table => user_roles

score 3 · Answer 37 · edited Oct 29 '12 at 22:20

3

I always use singular table names but, as already stated, the most important thing is to be consistent and use the same form for all names.

What I don't like about plural table names is that combined names can get quite strange. If for example you have a table named Users and you want to store properties for the user, this would result in a table named UsersProperties...

edited Oct 29 '12 at 22:20

XP1

6,112
7
48
59

answered Nov 23 '09 at 14:34

Vinz

1,976
1
12
16

4

No, it would actually be called 'UserProperties' since they are properties of the user *objects* (rows) which are stored in the table, not properties of the user *table* itself. Saying 'UsersProperties' would imply they are properties of the users table (i.e. RowCount, MaxRows, etc.) whereas UserProperties clearly denotes those to be pertaining to a user (i.e. UserName, Age, SSN, etc.) – Mark A. Donohoe Jul 09 '15 at 04:51

score 3 · Answer 38 · edited Nov 05 '13 at 21:32

There are different papers on both sites, I think that you only need to choose your side. Personally I prefer Plurar for tables naming, and of course singular for column naming.

I like how you can read this:

SELECT CustomerName FROM Customers WHERE CustomerID = 100;

Really we have OOP, and is great, but most people keep using Relational Databases, no Object Databases. There is no need to follow the OOP concepts for Relational Databases.

Another example, you have a table Teams, that keep TeamID, TeamColor but also PlayerID, and will have same teamID and TeamColor for certain amount of PlayerID...

To Which team the player belongs?

SELECT * FROM Teams WHERE PlayerID = X

All Players from X Team?

SELECT * FROM Players INNER JOIN Teams ON Players.PlayerID = Teams.PlayerID WHERE Teams.TeamID = X

All this sound good to you?

Anyways, also take a look to naming conventions used by W3Schools:

http://www.w3schools.com/sql/sql_join_inner.asp

Sounds like the league database needs some normalization, but I agree your point. — Nuno André, Mar 20 '20 at 17:18

score 3 · Answer 39 · answered Dec 03 '08 at 19:06

3

There is no "convention" that requires table names to be singular.

For example, we had a table called "REJECTS" on a db used by a rating process, containing the records rejected from one run of the program, and I don't see any reason in not using plural for that table (naming it "REJECT" would have been just funny, or too optimistic).

About the other problem (quotes) it depends on the SQL dialect. Oracle doesn't require quotes around table names.

answered Dec 03 '08 at 19:06

Gabriele D'Antona

6,866
4
42
78

1

Sql Server only requires braces for names that are reserved keywords ("User" is one). I believe Oracle has the same policy – Jimmy Dec 03 '08 at 20:18

markus · Answer 40 · 2009-03-12T23:04:30.190

3

If you use certain frameworks like Zend Framework (PHP) it is only wise to use plural for table classes and singular for row classes.

So say you create a table object $users = new Users() and have declared the row class to be User you will be able to call new User() as well.

Now if you use singular for table names you would have to do something like new UserTable() with the row being new UserRow(). This looks more clumsy to me than just having an object Users() for the table and User() objects for the rows.

edited Mar 12 '09 at 23:04

answered Dec 03 '08 at 20:11

markus

38,729
23
95
139

2

This is not true. Zend_Db does not impose a naming convention on database table names, table class names, or row class names. I removed that ill-conceived inflection code myself. – Bill Karwin Dec 03 '08 at 20:32
1

But I do support the convention of table class names being plural, and row class names being singular. It's just that the Zend_Db framework doesn't enforce any convention. – Bill Karwin Dec 03 '08 at 20:33
1

just revisited my post and corrected it. it's true that it doesn't impose anything. it just suggests it. – markus Mar 12 '09 at 23:05
There is no class for a table. Any class mapped to a table actually describes the row, not the table. The only correspondence to a class for a table is one that explicitly describes a collection. – Bruce Patin Nov 30 '12 at 16:42

score 1 · Answer 41 · answered Dec 03 '08 at 19:33

1

I solved the same problem by naming the table "Employee" (actually "Employees"). I try to stay as far away as possible from any conflict with possibly reserved words. Even "Users" is uncomfortably close for me.

answered Dec 03 '08 at 19:33

dkretz

36,502
13
76
133

1

Challenge comes that you're only aware of reserved words at the point you write you application - this pool of words will drift over time as the language is enhanced. Qualifying a word as a name with square paren is the only way to salve that pain. – stephbu Dec 03 '08 at 19:41
At the expense of readability, which is problematic enough in sql statements. – dkretz Dec 03 '08 at 20:20
Totally agree - CAPS for keywords, Camelcase etc. for names helps improve that a little but still it reduces readability. – stephbu Dec 04 '08 at 15:24
2

Employees may not always be users. – ProfK Dec 05 '08 at 14:55
2

Nope, so for those cases it wouldn't be appropriate. But for other cases, maybe there's another word (like "members", "people", or such that would enable you to avoid "Users" and not be as likely to collide with something. – dkretz Dec 05 '08 at 17:28

Table Naming Dilemma: Singular vs. Plural Names

41 Answers41

Linked

Related