none
Table Names - Singular or Plural?

    General discussion

  • I was surprised when I couldn't find any posts about naming tables with singular or plural names, other than a generic post about naming conventions.  There are debates / arguments about this topic all over the internet.

    I have struggled with this question for my entire career, but recent insight has given me a new, personal standard and the solution actually involves new features from SQL Server 2005:

    Read my blog post for details. (http://www.ericis.com/)

    *Comments are welcome!

    Wednesday, May 10, 2006 8:05 PM

All replies

  • I can see a few minutes worth of struggle but not an entire career. A database table is a container like a pickel barrel, sock drawer, toolbox or cupboard. It's not a pickels barrel or a cupsboard. When you model these tables in an ORM, you want the class name to be Person rather than Persons, so the natural convention of giving the container a singular name has practical benefits. The SQL might not roll off the tongue (select * from person where age > 21) but it ain't bad and SQL is not English.
    Wednesday, November 19, 2008 5:44 AM
  • On the rare occasion where a table only contains a single row, then that would not be plural.

    Most tables should be pluralised (IMO) because they are collections.

     

    You don't have a table of employee detail, you have a table of employee details

    Wednesday, November 19, 2008 11:19 AM
  • Tables by definition, are collections of records.  Therefore, it is redundant to plural a table name.

    No offense, I am dating myself, but your "entire career" must not be very long.  I remember when table names were only 6 characters long, and you NEVER used more characters than needed, because every table name needed to be unique in 6 characters.

    In addition, until Windows came along with a GUI, programmers had to type every table name, instead of drag-n-drop, so programmers don't want to type any more than needed.  Long table names also increase typos and recompiling over and over, and hours of debugging.

    Wednesday, November 19, 2008 2:13 PM
    Moderator
  • Using your logic we have a socks drawer, a pickels barrel and a cupsboard. Did you miss those analogies or simply chose to ignore the implications? We obviously do not tack 'Table" on the end of each table name but it's implied. Those pluralizing are not taking in to account practical experience. Does it really make sense to have one individual mapped to a Persons class? It's not that the folks behind Java EE and Hibernate missed something when they stipulated table names should be singular. When they are plural, you then have to manually add a mapping entry to change the name. The alternative was to provide magic code that converted table names to entity names (Rails does that) using English language rules.

    I would concede that if SQL queries are your main focus, versus C# or Java code with classes that map to the database, plural table names will make the SQL read slightly better. If you want to make your entitlies look comical (a Rooms class is actually a single Room) go ahead and use plural names.
    Wednesday, November 19, 2008 4:16 PM
  • You state that "most tables should pluralized because they are collections" which is a very simple and concise  assertion. Why do you suppose at least one person had a "career long struggle" if it's that cut and dried? If you view the collection of Person records as a set, list or table you would naturally call that the person table, person set or person list (e.g., pickel barrel). On the other hand, if the name describes only what is inside, then persons and pickels makes sense. Consider that ORMs attempt to auto-correct using English pluralization rules. You may have named your table Customers but your LINQ queries have to reference Customer because LINQ 2 SQL corrected the mistake of using plural names. Again, it's easy to see where pluralized table names do make SQL queries more readable, so there are two sides to this coin, and the auto-correction of ORMs makes the choice somewhat moot.
    Wednesday, November 19, 2008 5:16 PM
  • Unfortunately your 3 types of containers are not good examples to prove your point, the name you gave them already specifies the contents of the container.  I would argue that you should be discussing a "label" for the contents of a drawer or a barrel.  Tables themselves do not specify the contents as your cupboard does (tho you could argue vagueness due to the possibility of cups,plates,bowls etc).  So if the barrel contained cherries, and i was going to put a label on it, i would not put "cherry" rather i would put "cherries".  This is the same with a drawer that you wanted to label, if the drawer contained socks, you would not put the label "sock" on it, you would put "socks".  My point is, it is pointless to argue so vehemently on this as one can conjure up all sorts of little scenarios in which one would make more sense.  Incidentally, i agree that it makes more since to use the singular form.  It just works that the table match your class name.  This is not 100% the rule though.  Just have a look at some of the system tables that Oracle, MS, and others provide.  Lots of plurals in there.  Everyone relax now.
    Wednesday, May 26, 2010 5:04 PM
  • I don't think this thread will the the "answer" mark for anyone. It is one of those eternal discussions:

    - singular or plural table names

    - all lower case, mixed case or upper case table names

    - use underscores or avoid them (applies more to column names)

    - use the "tbl" prefix on tables or not (at least this one is easy)

    I've had discussions about them and thought about them, and in the end I changed my position on this topic (a few years ago).

    What I prefer is:

    - all lower case names. This avoids all problems with case sensitive installations

    - use underscore between each word in the name. A must if you choose all lower case names

    - use plural if the table will be storing multiple rows. It is more intuitive and brings the right mind set (set oriented) when writing queries

    So in my database, you might find an orders table and an order_lines table.

    In the end, IMO it is more important to have a consistent naming across the entire database then to have the "right" naming convention.

    -- 

    Gert-Jan

    Sunday, May 30, 2010 9:05 AM
  • I wonder, will you change your table names into plurar if in the event more than one record is added later? seems very time confusing of you have to re-write stored procedures and functions etc. i think single wiout expections would do the trick...

    CamelCase convention is a good alternative instead of underscores.

     

    paul.

    Friday, June 24, 2011 12:08 PM
  • IMHO. The Correct answer is "there is no correct answer, they all work". However it is worth discussing the Pros/Cons within your company, project or team. Then agreeing to a set of standards.

    Keep it short enough that you can insist every new member can read, understand & remember your naming conventions when they join. So 1-3 pages is prefered. Then if you conform to the standard, the names you choose will be correct, for your project & maybe for your company.

    As for my 2 cents.

    A table is a collection of rows. Thus I'm comfortable selecting from EMPLOYEES where start_date is null. It reads better. 
    But if you have an ORDER table with ORDER_DETAIL then if saves me typing 1 char & I can live with that.

    I lived in the worlds of JSDGENER, vi & cat, 8.3 filenames & SAP "random character" table names. Yes in the past we had to be consise & have cryptic names for things. It did save time typing. But wasted more time & caused errors when you couldn't remember if you needed to query NC34 or NC54 to get what you needed. That is why we increased the length of names. Lets not live in the past.

    Now that we can use a data dictionary & do have the benefits of GUI lists. My request is that you think about naming that clumps similar concepts together. ie: instead of TestCase.ActualResults, TestCase.ExpectedResults & TestCase.ResultsTemplates it is easier to work names that sort closer to each other in the alphabet eg: TestCase.ResultsActual, ResultsExpected & ResultsTemplate this assumes that they alll related to some common subset of your app.

     

    Saturday, June 25, 2011 2:04 AM
  • I do not have a books shelve or a salads bar.  The Person table holds People records.  The Product table holds products.   I don't want to query where products.name = Wonka but rather where product.name = Wonka; or perhaps from product in products where name = "Wonka" select product.  Singular table names on balance are superior but as long as you are consistent and your mapping tools produce rational collection names, either will work.  If you are struggling with this for more than 30 seconds flip a coin stay consistent.
    Thursday, October 04, 2012 9:01 PM
  • You state that "most tables should pluralized because they are collections" which is a very simple and concise  assertion. Why do you suppose at least one person had a "career long struggle" if it's that cut and dried? If you view the collection of Person records as a set, list or table you would naturally call that the person table, person set or person list (e.g., pickel barrel). On the other hand, if the name describes only what is inside, then persons and pickels makes sense. Consider that ORMs attempt to auto-correct using English pluralization rules. You may have named your table Customers but your LINQ queries have to reference Customer because LINQ 2 SQL corrected the mistake of using plural names. Again, it's easy to see where pluralized table names do make SQL queries more readable, so there are two sides to this coin, and the auto-correction of ORMs makes the choice somewhat moot.

    LINQ does not correct "mistakes", it simply creates a mapping class that can only hold one record at a time, thus the singular (we may also say it maps to a single row in a table). For instance, a table named Categories will be mapped to a Category class. Again, the rationale behind that is not the correction of a mistake, it's just that the representation of a collection in .NET is different. In this specific case, multiple categories would be retrieved in a collection such as Category[]

    In a database, however, tables are, by default, collections. If we look at the ISO/IEC 11179 and the discussions around it, the recommendation is to use plural or collective/concept. For instance:

    Person => Singular, not recommended

    People => Plural, better

    Personnel => Collective, best.

    If we also take a look at the ANSI 92 standards for metadata, the same principle applies there:

    Information_Schema.Check_Constraints ==> Plural

    Information_Schema.Column_Privileges  ==> Plural

    Information_Schema.Schemata ==> Collective

    Columns, on the other hand, should always be singular, unless the concept itself is plural.


    My personal preference is for names in the singular, rather than plural, but the international standards seem to recommend the latter.

    • Edited by Mr-King Monday, February 18, 2013 6:00 PM
    Monday, February 18, 2013 3:12 PM
  • When potential normalization is considered, the singular becomes preferred.  While a Fishes table allows for creation of tables of fish types, even mammals, the singular Fish table becomes confusing or obsolete if a Miscellaneous table is provided.  In this case clarification is required for a single fish versus plural fish.  Languages help interpret the situation but English has many inconsistencies.   Entities and collections I believe argue further for the singular form for tables.  Singular is the stance taken by Paul Nielsen who maintains that is the prevailing belief.
    Saturday, April 06, 2013 12:02 AM
  • Modeling today, and just remembered the biggest reason why I prefer singular table names.  It's simply because pluralizing is awkward and ambiguous.  It's hard enough to come up with appropriate singular nouns for entities without having to worry about how they pluralize.

    Introducing the plural form of each entity name is simply work that doesn't need to be done.

    And also table names are often compounded with other table names, and not pluralizing makes this cleaner.  Eg Order and OrderDetail vs Orders and OrderDetail, where you have to re-singularize the name to compound it.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, May 20, 2013 5:12 PM
  • While I agree that tables should generally be named in the singular, the pickle barrel analogy does not help.  Other containers are named in the plural after what they contain.  A can of beans.  A bag of chips.  A cache of weapons. 

    Whatever rules in English that cause us to say a "pickle barrel" while also saying a can of beans, they don't apply to how we should name tables.  Speaking about the customer table sounds about as correct as speaking about the table of customers.  However, I've never heard anyone speak about the table of customers -- it's always the customer table, which seems more natural. 

    The idea that classes are generally named in the singular and so should their underlying tables also makes sense. 

    In my travels I have encountered written standards that specify table names be singular.  The databases I have encountered having tables named in the plural are in organizations that haven't addressed this question. 

    Thursday, May 30, 2013 11:51 PM
  • Rick -- I think you are right.  Table names should be singular.  It makes little difference that plural names might make Sql easier to read.  Syntax and grammar rules (as I'm sure you know) of English do not apply to programming languages; if they did then we would have bigger problems than the answer to this debate. 
    Thursday, May 30, 2013 11:58 PM
  • I agree with you about one thing: Consistency is key. 

    Use MixedCase to name tables.

    Type all Sql reseverd words in UPPERCASE (I admit that this is pretty old-school and concede that most dev's don't like this, so I am working on adjusting my ways on this one). 

    Prefixing tables with tbl adds little value.

    Tables should be named in the singular!  If for no other reason then the fact that its more natural to say the BlaBla table, not the BlaBlas table.  On the other hand, if we usually referred to "the table of BlaBlas," then fine, I would agree that plural names make sense.  How it sounds in English is really not that important though.  There are other, better reasons already stated in this thread, not the least of which is the fact that it is a given that tables almost always contain more than one record, so plural names add about as much value as tacking the word "table" onto the end of each table name.  Lets call it, "the CustomersTable."  Sorry, but making it plural does not affect mind-set -- you used the term "set-oriented."  Is it a set of knives or a knife set?  What does intuition have to do with it? 

    Friday, May 31, 2013 12:13 AM