Plural vs Singular Table Name

Question

How should I name my Tables when creating a new database?

Singular: Client or Plural: Clients?

I once had a co-worker who insisted that table names be singular and view names be plural. — René Nyffenegger, Feb 23 '12 at 08:59
There are other schools of thought. 1) Use verbs that will allow one to express queries in natural language e.g. person NAMED 'fred' EARNS 20,000 (where the uppercase names are the tables). 2) use the enterprise's name for the set e.g. PERSONNEL, PAYROLL, ORG_CHART, etc. — onedaywhen, Feb 23 '12 at 09:10
Possible cross site duplicate: http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names?rq=1 — Ciro Santilli OurBigBook.com, Jan 24 '14 at 14:11

score 70 · Accepted Answer · edited Dec 27 '18 at 00:27

70

Up to you. Just be consistent though.

Personally I prefer singular based on what each *row" stores: Order, Product, User, Item, etc.

This matches my modelling (via Object Role Modelling) where I use singular entities/types.

Edit:

One reason is that plural fails when you have link tables:
Orders, Products would give OrderProducts or OrdersProducts. Neither sounds correct

Or history tables (of course you can use schemas for this):
Orders -> OrdersHistory or (no!) OrdersHistories? Wouldn't Order-> OrderHistory be better?

edited Dec 27 '18 at 00:27

joelparkerhenderson

103
3

answered Feb 23 '12 at 08:10

gbn

69,809
8
163
243

4

Should it always boils down to a Personal choice? What if there were 2 people working in one database design, then one might name tables as plural and the other as singular. Is there no valid reasoning as to why use Singular or Plural? – John Isaiah Carmona Feb 23 '12 at 08:23
2

@JohnIsaiahCarmona: added a reason – gbn Feb 23 '12 at 08:28
5

I agree about using singular as being the most sensible. This doesn't seem to be the popular opinion, if you look around at similar questions here and on SO, etc. Lots of people seem to take a programmerly view of tables as collections which should therefore have plural names. I think that maybe ORMs might be starting to break people of this (bad) habit. If your tables have plural names to begin with it makes it hard to distinguish parent and child navigation properties and to distinguish instance and collection objects for a table. – Joel Brown Feb 23 '12 at 12:47
5

Another reason in favour of Singular is if you have a rule that the PK is named after tablename, for example TablenameID or TablenameCode or tablename_id. With plural table names, you end up with Orders.OrdersID (which doesn't look right) or with Orders.OrderID where you use plural for table names but change to singular for column prefixes. – ypercubeᵀᴹ Feb 27 '12 at 10:10
Another point along the same lines. – Jack Douglas Jul 21 '14 at 19:40
@ypercube There is a way to get around that issue. One can use the AS statement like so:
SELECT * FROM Orders AS Order where Order.OrderID = 0
– Abel Melquiades Callejo Dec 04 '15 at 14:51
@AbelMelquiadesCallejo Yeah but more like SELECT * FROM Orders AS O where O.OrderID = 0 ;. You can't use a reserved keyword as an alias. – ypercubeᵀᴹ Dec 04 '15 at 17:27
1

"Just be consistent though." - the most important part – Holly Sep 11 '19 at 15:24

score 13 · Answer 2 · edited Feb 07 '13 at 23:25

Concerning singular versus plural table names, the subject seems to be controversial, but it shouldn't be.

While a table is a collection of multiple records, a table is named after the definition of the one type of record that it contains. If a table was allowed to have a different name than that of the type of record that it contains, you could give the table a plural name, so that you could for example have an Employees table containing multiple Employee records. But the designer of SQL did not provide for separate names for tables and record types.

Things work out more logically for object oriented programs that use the data, if the name of a record type (and by extension the table name) is kept singular, as it will correspond with the name of the class you would use to describe one record.

If you then want to identify a collection in the program, you can use a plural, or better, use an appropriate modifier, such as EmployeeList or EmployeeArray.

There is also a problem with irregular plurals for automatic code generation and programmers who have different language backgrounds or ideas about the formation of plurals in a program.

The English language is not a good and proper programming language, and trying to make database and program statements conform to English because it sounds better to read one of those statements is a mistake.

Neil McGuigan · Answer 3 · 2023-06-05T17:49:12.700

12

"order" is a reserved word. "orders" is not

"user" is a reserved word. "users" is not

"session" is a reserved word. "sessions" is not

"result" is a reserved word. "results" is not

"relative" is a reserved word. "relatives" is not

...

Those seem like common words that might go in line-of-business database. Plural words seem to be less common as key words than singular words. Therefore, it might be beneficial to use plural table names so as to avoid conflict with SQL key words.

99% of PostgreSQL reserved keywords are singular (except references), so if you use plural table names, you're more likely to avoid using a reserved keyword (such as user)

97% of SQL-92 reserved keywords are also singular (exceptions: constraints, diagnostics, names, references, rows, values)

edited Jun 05 '23 at 17:49

answered Jul 17 '15 at 21:29

Neil McGuigan

8,423
4
39
56

4

This is too close for clarity. Just stay away from reserved words, singular or plural. That really helps when debugging error messages that use plurals of reserved words interchangeably. Ideally pick words from the domain of the application to make it more relevant to use/user. – Emacs User Jul 18 '15 at 00:00
2

what does "too close for clarity" mean? – Neil McGuigan Jul 18 '15 at 00:09
2

It means a needless higher overhead deciphering error messages. For example, order by and orders in syntax error messages. Or trying to debug user and users in authentication error messages. – Emacs User Jul 18 '15 at 20:04
2

IMO PurchaseOrder, PortalUser, UserSession are better than just Order, User, Session so singular might just do fine in this scenario – John Jai May 23 '19 at 19:52

score 7 · Answer 4 · edited Apr 13 '17 at 12:42

7

Just as @gbn's answer I think this is most a matter of preferences and just like him I recommend that any choice you made, apply it everywhere (in that DB at least). Consistency is worth it.

My preference, however is that a plural sounds better in SELECT statements :

SELECT Id, Name, Status 
FROM   Persons
WHERE  Status <> 5  --5 meaning deleted

I mean in this case, at least, there are several persons in the table and several of them are returned to the client.

edited Apr 13 '17 at 12:42

Community

1

answered Feb 27 '12 at 09:23

Andrei Rînea

728
8
14

6
1 though technically the plural of Person is People and this is one reason I use singular. Some ORM's will auto create the tables for you and you get weird scenarios like this where linguistically the naming is not logical.

Mr.Brownstone

Jan 06 '18 at 03:14

score 5 · Answer 5 · answered Jan 02 '19 at 09:46

5

After working with programming for some years I have concluded that pluralization is a needless complication. My opinion is that according to the KISS philosophy a programmer should strive for the laziest and easiest solution to all problems for time and efficiency reasons. Thus singular gives you less work needed in all scenarios.

answered Jan 02 '19 at 09:46

ColacX

159
1
2

1

This answer doesn't really add anything to the whole thread! You fail to answer the problem with calling a table "order" for example! Personally, I use French words when English won't do the trick - ordre, groupe... Always use your table's comments field to explain your choice! But, the really important thing is to have a convention and stick to it! – Vérace Jan 02 '19 at 10:00
3

How does it not add anything? I added that singular is less work in my opinion. If you have a different opinion than mine please do not devalue my opinion. "Orders" is not the problem the problem is more complicated pluralizations that is not an -s such as "Categories" which I have seen misspelled in all manner of combinations causing needless work. – ColacX Jan 02 '19 at 10:11

score 2 · Answer 6 · answered May 21 '15 at 14:18

2

I believe SQL table should have plural names. It simply reads much better.

A table of book records should be called books. The ORM should use the same convention. The Books object is a collection, and presides over all records in the Books Table. A Book object presides over a single record.

This makes coding more natural.

select name, publication_date from books where publication_date > '2000-01-01';

books = Books()
for book in books.get("publication_date >= '2000-01-01'"):
    print book.name

answered May 21 '15 at 14:18

dlink

201
2
3

Makes sense until you have to write for sheep in seep.get... Bend rules, be flexible. – Emacs User Jul 18 '15 at 00:03
True some containers are words with non-plural like nouns - Like 'access'. but one can modify using: 'for access_record in access'. – dlink Jul 20 '15 at 13:21
1

Drives me a bit batty seeing link objects though. How about a link table between Books and Authors? "BooksAuthors" looks and sounds horrible, but "BookAuthor" looks and sounds better. Then you get into words that have odd endings for plurals (statuses) or are irregular nouns (child vs children). IMO a world of eye pain! – blobbles Aug 30 '17 at 06:26
Hi, @blobbles the plural would be BookAuthors, not BooksAuthors. So it still reads natural. BookPublishers, BookFormats, etc. – dlink Sep 03 '17 at 21:32
Readability is always good but it's not about a sentence it's about the places we are obtaining data from. eg table.field, so author.authorName is perfectly fine. Get the authorName from the author table. When there is only one author the plural looks bad too. authors.authorName when there is only one author? That is more confusing imo. This of course is made much better now we've done away with sentence style mysql_ and have better ways to access the data :) – James Jan 17 '18 at 02:29

score 2 · Answer 7 · answered Apr 14 '22 at 03:31

The arguments on "plural" seem to be mostly about being grammatically correct, and while we have grammar to some degree in programming it's not the same as the actual English language itself.

"Plural" names only sound correct because the tables are thought of as multiple things, "users" etc. If you were at a business meeting with 40 of your site users, you'd refer to them as "there's all of our Web users". But you don't actually have "web users" physically in your table, it's just data, so think of it like "webUser data". You wouldn't put the word "data" on your table name, but that's what it is, a collection of data with one or multiple rows that is related to "webUser".

Imagine an agricultural business who has to store data about their animals. They have a table named "cows" and are to start trading with "sheep".

Should the table be "sheep" and break consistency with all the other plural names, like "cows"?
Should the table be "sheeps" which is incorrect English? It's not even a word

The business advances even further, and now they trade in deer and cod...

So do we have inconsistency, which is annoying to work with:

cows
sheep
pigs
deer
cod

Or consistency but with invalid words that would urk most people reading them:

cows
sheeps
pigs
deers
cods

"cods"? C'mon.

It also breaks when you have various tables that relate to each other with multiple words. You either continue the plural for consistency and have terrible sounding table names, or break consistency:

users
usersHolidays or userHolidays
usersHolidaysRemainings or userHolidaysRemainings or...????

vs

user
userHoliday
userHolidayRemaining

score 0 · Answer 8 · answered Jan 06 '18 at 03:09

It's a very personal thing. I have been using singular form for 30 years. But I can see why people like plurals. The books - authors is interesting as I think booksauthors is not wrong. A book can have one or more authors. And authors may have written one or more books (e.g. co-written). It also just depends how you handle books written by more than one author. I agree with other answers; choose one and be consistent. In regards to reserved words issues. I think it is not hard to come up with workaround names. user -> app_user , session -> app_session, order -> customer_order

score 0 · Answer 9 · answered Jan 17 '18 at 02:22

We view things from different perspectives, and I think the two camps are identified by:

Singular ("user")
The person who makes a correlation between the table name and the fact it represents a container, which can contain multiple rows.

So "user container" can contain multiple rows.

Plural ("users")
The person who does not make the correlation between table name and that fact it represents a container. Of course they know it is a container, but it's not there in the name.

e.g.
An "egg carton" can have multiple eggs in it but that's obvious as the container reference is in the name, providing potential for multiple eggs. However with the singular table name "user" the container reference is not there in the name. eg "user_container" would likely be acceptable for people who prefer plural names.

I think this is also because of years of plural being common practice and in most online teaching material.

All this said, I think that technically speaking the singular is more accurate given that we're naming a single container, and containers can contain multiple (or single) rows.
It seems wrong to people as they mentally link the table name to the contents (multiple rows needs a plural name) rather than mentally linking the named container to the contents (a container allows for multiple).

As always though there's often not a right and wrong, and it's more about what suits the scenario, and importantly being consistent with whatever you choose.

If you are doing the project solely and there's no real reason to go either way do whatever you feel is best, or just preference. Apply the same when in a dev team and just come to a unanimous decision.

Plural vs Singular Table Name

9 Answers9