58

Exact Duplicate

Table Naming Dilemma: Singular vs. Plural Names

Is it better to use singular or plural database table names ? Is there an accepted standard ?

I've heard arguments for and against it, what do you guys think ?

Community
  • 1
  • 1
lalala2007
  • 823
  • 1
  • 8
  • 13
  • 5
    When closing for "exact duplicate" shouldn't a link be provided? I see it in "Related" but I still think mods should provide a link either by editing the question or providing it in a form. – Jake McGraw Apr 30 '09 at 21:05
  • 5
    here's all answers in a nutshell: "Plural because the table is a bunch of one thing" and "Singular because the table itself is one thing and doesn't *have* to contain a bunch of things". It's a moot point; "consistent" is the best practice. – STW Apr 30 '09 at 21:07
  • 1
    Ok this is old but... If you have lets say a unique / auto incremented primary key in a table (for relational purposes), make the table name singular. If you don't have a unique entity in row (table with duplicates) use plural. Now you know that a certain table has a unique relationship vs another table that just contains many that are not unique or not distinct records. – Jesse Oct 13 '11 at 04:26
  • Plural. Using the sock drawer example. You would not tell someone to go grab you a sock from the drawer full of sock. When asking for a sock from the sock drawer you are essentially saying. Go grab me a sock from the drawer of socks. Don't let our common lazy speech confuse. – James Wilson Sep 19 '14 at 18:09
  • @JamesWilson "Database, go grab me a customer from my table full of customers" - realistic DBA/programmer quote – MarredCheese Feb 24 '19 at 01:14

13 Answers13

114

Singular, so you can have:

  • Customer
  • CustomerAddress
  • CustomerAddressAuditTrail

etc.

Tim Robinson
  • 53,480
  • 10
  • 121
  • 138
  • 9
    CustomersAddresses, CustomersAddressesAuditTrails :) – Russ Cam Apr 30 '09 at 20:58
  • 6
    +1 for singular. The code using them will definitely be using singular for class names, and I like my names for the same thing to match. – millimoose Apr 30 '09 at 21:05
  • 6
    @Russ Cam: Sorry, but besides the fact that this sounds awful, the entity the table stores is a "CustomerAddress". The fact that there can be more than one of them is inherent, no need to reflect it in the table name. – Tomalak Apr 30 '09 at 21:08
  • 6
    Plural: Customers.. CustomerAddresses, CustomerAddressAuditTrails.. you wouldn't plural every word, just the object noun – jamiegs Apr 06 '11 at 00:59
  • 2
    Why in the world did he select the plural name as best answer? Table names should be singular. customer ... its the customer table that has many customer records. Plural just doesn't make any sense. It also doesn't make sense to have a table such as users then have primary auto inc user_id / users_id... would be table user.user_id... make the most sense to me. I use to do plural all the time until I realized plural doesn't make sense as a table name. Think "SELECT * FROM {customer table} WHERE customer_id = 1" where "SELECT * FROM {customers table} WHERE customer_id = 1". – Jesse Oct 13 '11 at 04:22
  • As the previous comments indicate: plural will just cause confusion. Keep it simple: singular. BTW @RussCam: shouldn't that be CustomersAddressesAuditsTrails? :) – Valentino Vranken Feb 08 '12 at 12:43
  • @ValentinoVranken Close. For it to be grammatically correct it would be `CustomersAddressesAuditTrials` Technically Customers' Addresses, `Audit` is kept singular but `Trials` is indeed plural. Funny, 3 different people misspelled "trials" with "trails". :D – Tek May 18 '12 at 21:53
  • 3
    @Tek Actually, only one person misspelled "audit trail" with "audit trial"... ;) – Valentino Vranken May 22 '12 at 09:40
  • 2
    @Tek Seriously, what's an audit trial? :) – Tim Robinson May 22 '12 at 16:56
  • OCD is the ONLY good reason that I can see for using singular - but it's a really good one. I hate when they tables get out of order because of the pluralizing. But then I hate `Select * from Product where...` too though! BLAHH! =) – RichC Jul 31 '14 at 17:08
  • @RichC by OCD you mean obsessive–compulsive disorder? That's all Google gave me. – Crono Nov 04 '14 at 13:44
  • Yes, obsessive–compulsive disorder. – RichC Nov 26 '14 at 13:45
  • Singular. That's what I learned from my prof at University. – Maru Dec 08 '14 at 10:22
84

IMHO, Table names should be plural like Customers.

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

bart s
  • 5,068
  • 1
  • 34
  • 55
Gulzar Nazim
  • 51,744
  • 26
  • 128
  • 170
  • 15
    Absolutely correct. – NotMe Apr 30 '09 at 20:52
  • 6
    One slight caveat/suggestion - where the name comprises several words you should pluralise where it makes linguistic sense rather than just the whole thing. Taking an example from below I would use: Customers CustomerAddresses (it's the address that's multiple) CustomerAddressesAuditTrail (the addresses keeps the plural as it's an audit trail applying to CustomerAddresses) – Jon Hopkins Apr 30 '09 at 21:01
  • 12
    a table is still a single entity, despite the fact that the entity contains a collection. The more I think about it the less sense pluralized table names make sense. – STW Apr 30 '09 at 21:02
  • 11
    Plural table names are redundant! – Jake McGraw Apr 30 '09 at 21:03
  • Yoooder: are you British, perchance? :-) – Ken Apr 30 '09 at 21:04
  • I'm half-Canadian, which makes me American English of sorts--but not British. What made you wonder? – STW Apr 30 '09 at 21:13
  • 2
    Please dont mind the people telling you to adopt certain style, its best to define your own, and to usually follow the practice(s) of the languages, tools, IDEs and frameworks that you use. – Azder Apr 30 '09 at 21:20
  • @Azder - Thats true. It is upto the others to accept or not. – Gulzar Nazim Apr 30 '09 at 21:22
  • 1
    added IMHO :)..sheesh so many downvotes and upvotes for this.. – Gulzar Nazim Apr 30 '09 at 21:23
  • 4
    Yoooder: In British English, a collective object is often grammatically plural, e.g., "the committee were unable to agree" versus American English "the committee was unable to agree". So I hypothesized that a Brit might be more willing to use a singular noun for a collection of things. – Ken May 01 '09 at 20:03
  • 2
    I think plural reads easier.. the SQL is more english like.. Select * from customers where firstname = 'Jamie' would say I'm selecting from the pool of customers with a first name of jamie. It contains multiple customers, it may return multiple customers.. make it plural – jamiegs Apr 06 '11 at 01:02
  • 1
    Plural names just make it complicate, you need to add grammar to your programming skills! plus is redundant. Love @JonHopkins example, ironically "Hopkins" looks like plural name to me :) – Nestor Apr 30 '11 at 11:12
  • 1
    A table is a set of records. A set is always named in the singular form: user set, not users set; number set, not numbers set; sock set, not socks set. If you have a collection of user records, you have a user table for your user records. You do not have a users table for your users records. The table should be named User because it's the User table. – OCDev Oct 27 '13 at 00:12
  • I agree. Some time common sense, meaning and convenience is better than following so called "standard". Basically a tables is collection of Customer, so, has to be plural. What would u call... "Settings" or "Setting", "Option" or "Options" in a menu title ? – Tejasvi Hegde Jun 25 '14 at 06:51
  • 1
    @FriendlyDev so sounds like you are arguing that we name all tables like this: CustomerSet, OrderSet, ProductSet, etc. =\ I either say "Numbers" or I say "Number Set" but I never say "Number" when referencing a set of numbers. – RichC Jul 31 '14 at 17:06
  • @Rich C: But you would say "Number" when referring to the Number table. It's why you say "Sock Drawer" and not "Socks Drawer". In all these cases, what you are doing is giving a name to the container. You are not giving a name to the items inside of the container. A table is a single container, so you give it a singular name because you are referring to it in its singular form. Anyway, for some reason I don't care anymore. Name your table "Numbers" if you want. See if I care. Ha! ...wait. Don't. Please. Please no. – OCDev Aug 01 '14 at 03:27
  • @FriendlyDev yeah, but you keep adding things to the end of it to cause it use the singular version. So, yes, I COMPLETELY agree with you that if I were to add the suffix Table to the end of it like NumberTable or SockTable, that would make sense but I'm not, it's just Socks and Numbers not Sock and Number. The only good reason I see (and it's a good one) to use Singular is because of Tim Robinson's answer, not because it's grammatically correct like you are arguing. :) – RichC Aug 01 '14 at 13:17
  • @Rich C: Yeah but but but. But you don't have to say table at the end because it IS a table. Neener neener neener. – OCDev Aug 02 '14 at 03:19
  • 1
    @FriendlyDev Can you grab my red socks out of the sock? (I don't have to say drawer because it IS a drawer) =\ – RichC Aug 13 '14 at 16:42
  • 1
    @Rich C: How about this? You have 50 drawers. They are all organized and labeled based on their contents. I start issuing commands: Go fetch the 10 largest diamonds out of gem. Now, fetch a random fire engine out of toy. Finally, fetch the red socks out of sock. – OCDev Aug 20 '14 at 00:47
31

I like singular names but appear to be in the minority.

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • 3
    I'm with you, but not because I like them--just because almost all of our are singular – STW Apr 30 '09 at 20:58
  • 4
    I'm switching my stance. I'm with you because it is the name of a single thing. You can select multiple widgets from a widget table, but the table itself isn't widgets--it just contains them. – STW Apr 30 '09 at 21:20
  • 1
    I'm with you, I never liked grammar, singular is easier. – Nestor Apr 30 '11 at 11:19
  • 4
    I don't see how it is the minority: number 1 answer is plural, number 2-4 are singular. Total vote count for singular is much higher than total vote count for plural. Vote count for answer number 2 is higher than that for number 1. Table names should be singular. The customer table holds the names of all the customers, and it is called the customer table because its name is "customer", not "customers". – William Pursell Aug 11 '11 at 12:22
18

My personal philosophy is that using a plural database table name is redundant, unless you're only planning for the table to contain one row.

MattK
  • 10,195
  • 1
  • 32
  • 41
  • 27
    select * from customer seems "grammatically" incorrect to me... – Shea Apr 30 '09 at 20:55
  • 5
    @Arnshea: but when you think about what you're doing in pseudo code saying "get all parts of the table customers" doesn't make sense either. – STW Apr 30 '09 at 21:17
  • 1
    Don't write T-SQL, use LINQ and mapping: return db.customers – Nestor Apr 30 '11 at 11:20
  • 1
    @ArnsheaC however, select * from customers inner join managers where customers.managerId = managers.id doesn't read well either. – zeusalmighty Apr 25 '18 at 14:18
12

I like to use singular names like Agent that have PK names like AgentID.

But that's just me :o)

wcm
  • 9,045
  • 7
  • 39
  • 64
11

I like to use plural forms, simply because one table contains several entities, so it seems more natural to me.

Linq to SQL converts plural form table names to singular when creating data entities. I assume that Microsoft would not have implemented this functionality if they considered plural forms for table names bad practice.

Adrian Grigore
  • 33,034
  • 36
  • 130
  • 210
  • 2
    so everything that holds other things should be plural? "I got on the busses and took out my boxes of kleenex to blow my noses full of boogerses filled with green stuff". – STW Apr 30 '09 at 21:01
  • 8
    No, you are getting on one bus. If however, you are storing multiple busses in one busses table, the table name should therefore be plural. Calling the table "bus" although it actually contains several busses makes little sense IMHO. – Adrian Grigore Apr 30 '09 at 21:16
  • 1
    mmm, can we get the same example for noses? – Nestor Apr 30 '11 at 11:24
  • 2
    @Nestor: One nose as a data entity, several noses in one table. I'm afraid can't see the the problem. – Adrian Grigore Apr 30 '11 at 23:21
  • Just a little humor, I guess this Singular vs Plural is more of those PC vs Mac, Open Source vs Microsoft..., I think is up to each one. I stay with Singular. – Nestor May 01 '11 at 11:54
  • @Nestor: Makes sense seeing that computers count only with 0's and 1's as well ;-) – Adrian Grigore May 02 '11 at 08:04
  • @STW ... GREATLY EXPLAINED! YES! Exactly, hit the nail right on the head. – Jesse Oct 13 '11 at 04:30
11

At my current company we use Plural for table names. The reasoning is this: If we have a Customers table we consider each row a Customer, so the table itself is a collection of customers.

Alexander Kahoun
  • 2,458
  • 24
  • 36
  • 12
    but is the table itself customers? isn't it a Customer table? – STW Apr 30 '09 at 20:57
  • 7
    So, I will have a class TOOTH that reads from a table TEETH, and has property TOOTH.THOOTID that maps to TEETH.THOOTID... looks to me more like grammar class than programming – Nestor Apr 30 '11 at 11:30
9

Well, obviously your database table names have absolutely got to be named in a "standard" fashion which I will hitherto arbitrarily define.

First, all tables names shall be prefixed with "t_". Following this, the singular entity name in StudlyCaps, e.g. "Customer". Immediately afterwards, this shall contain the number of columns created in the first version of the schema, for historical purposes, followed by an underscore, and the precise normal form of the data; either "1", "2", "3" or "B" for BCNF. Any higher normal forms shall be denoted by a "P".

Some examples of acceptable names are:

t_Customer_6_3
t_Order_5_B
t_OrderLine_4_2

I think my point is, it really doesn't matter, as long as the name is reasonably descriptive and naming is consistent.

Rob
  • 47,999
  • 5
  • 74
  • 91
  • 6
    If this were slashdot, I'd say +5 funny... – Denis Troller Apr 30 '09 at 21:02
  • This is the lamest post on the board. Take your silly prefixes and stuff them. In my years in this business I've run across a number of these silly encryption methods. Name the table for what it is in plain English. If you can't come up with a simple name, then your design still needs work. And stuff the silly notations where the sun doesn't shine. They add complexity and cost and provide no benefit. – Quark Soup Jan 03 '14 at 01:48
  • 3
    Upvoting for the wit, and for highlighting your point in *bold*. And, even then, someone missed it anyway... – Peter Hansen Mar 08 '15 at 15:58
5

The most important thing is to be consistent in your usage. It is annoying to have to remember which tables are plurals and which are not. Same thing with your field names, pick one stadard and use it. Don't make the poor developers have to determine if this table uses person_id or personid or peopleid or person$id, etc. It is amazing the amount of time you can waste when you don't have standards trying just to remember which table uses what.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
4

There is no should or must be this way or that way correct answer to this question. It's up to the designer of the database and software.

As for me, I usually use singular names becouse when I do the E-R diagram I have an entity Customer , not Customers, so I keep it same as to not get confused.

Ofcourse some frameworks do favor one style or another, so you should be best of to follow those practices when you notice them.

Azder
  • 4,698
  • 7
  • 37
  • 57
2

There are many arguments for each, but it all boils down to what you feel comfortable with. Neither is wrong.

What's really important is that you are consistent. Choose one standard and stick to it, which one you choose is of less importance.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Standards are critical and this is an area that would benefit from one. The problem with your argument is that different groups in the same company can come up with their own brand of a 'consistent' convension. I see it all the time. If nothing else, having a convension saves you time from having to argue about it in meetings or surf the web looking for some guidance, like every single person who's read this post has done. That's 20 minutes I could have been working on actual codeing had there been a real standard in place. – Quark Soup Jan 03 '14 at 01:52
  • @DRAirey1: Yes, it would be nice if there was a standard, but there isn't any. That's not a problem with my argument, that is a problem with the lack of standards, and there isn't anything that my answer can do about that. So, you downvoted my answer because it points out a fact, just because you don't like that fact? Sorry, your downvote isn't going to change the fact either. – Guffa Jan 03 '14 at 02:05
  • Why the downvote? If you don't explain what it is that you think is wrong, it can't improve the answer. – Guffa Feb 18 '14 at 08:56
  • This comment is worse than useless. There are a mountain of opinions on the subject, but to just say "Whatever works for you" simply wastes the time of someone who has a genuine interest in this subject. Aside from that, it's just wrong. One division picks a plural standard and it becomes 'standard' for that division, another picks singular and it becomes 'standard' for that division. Then when Big Data comes along and tries to merge the data for the entire company, you've got a tremendous mess to clean up. – Quark Soup Feb 19 '14 at 12:00
  • @DRAirey1: It seems that it's your interpreteation of the answer that you are reacting to, not the answer itself. What you are saying that the answer says isn't actually in the answer at all. Besides, if you are going to merge data and different ways of naming the tables is your biggest problem, then you are very lucky. – Guffa Feb 19 '14 at 12:31
0

IMHO it doesn't really matter, just do whatever is comfortable with you and the people that are using the database.

I think I subconsciously list main data tables with an s and "pick list" or foreign key tables and singular.

DForck42
  • 19,789
  • 13
  • 59
  • 84
0

As with lots of these types of questions the best answer is often "consistent". You can argue the table represents a single entity and as such deserves a singular name, or that it contains multiple instances of an entity so it should be plural. My advice is flip a coin and go with it for the entire database (or stick with the convention that already has a majority).

STW
  • 44,917
  • 17
  • 105
  • 161