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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  collection classes  since it is easier to produce a plural name from a singular name than vice-versa

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot AppleBag quot   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  quot Customer quot   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  quot Repository quot  than of  quot Repositories quot   or  quot Status quot  instead of  quot Statuses quot   Having singular names can lead to fewer errors caused by typos  save time by not having to think  quot is it Child or Children  quot   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  gt  LoginUser  AppUser  SystemUser  CMSUser      Or use the infamous square brackets  User

User · Answer

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

User · Answer

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  amp  collection classes  since it is easier to produce a plural name from a singular name than vice-versa

User · Answer

How about this as a simple example   SELECT Customer Name  Customer Address FROM Customer WHERE Customer Name  gt   def    vs   SELECT Customers Name  Customers Address FROM Customers WHERE Customers Name  gt   def    The SQL in the latter is stranger sounding than the former   I vote for singular

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  user Roles table   gt  role users role relationship table   gt  user roles

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot AppleBag quot   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  quot Customer quot   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  quot Repository quot  than of  quot Repositories quot   or  quot Status quot  instead of  quot Statuses quot   Having singular names can lead to fewer errors caused by typos  save time by not having to think  quot is it Child or Children  quot   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  gt  LoginUser  AppUser  SystemUser  CMSUser      Or use the infamous square brackets  User

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

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  amp  collection classes  since it is easier to produce a plural name from a singular name than vice-versa

User · Answer

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  -

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 ---

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  user Roles table   gt  role users role relationship table   gt  user roles

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  collection classes  since it is easier to produce a plural name from a singular name than vice-versa

User · Answer

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

User · Answer

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 ---

User · Answer

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  -

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

How about this as a simple example   SELECT Customer Name  Customer Address FROM Customer WHERE Customer Name  gt   def    vs   SELECT Customers Name  Customers Address FROM Customers WHERE Customers Name  gt   def    The SQL in the latter is stranger sounding than the former   I vote for singular

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 ---

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 ---

User · Answer

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

User · Answer

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

[sql] Table Naming Dilemma: Singular vs. Plural Names

Examples related to sql

Examples related to sql-server

Examples related to naming-conventions