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?

Answer 1

Honestly speaking, stick to one convention whatever you have decided to use and apply that throughout your project. Use what feels the most comfortable for you.

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

It’s mostly recommended to use SINGULAR. I personally believe we are naming a relation not a table. We are addressing the relationship between the user’s name, ID and their address, etc. It’s not difficult to understand that there’s only one relationship for user data.

So, if we have described the user relation once, then we can use for many users. 

I’ll list out some important facts why we should really use SINGULAR instead of PLURAL:

   1.It helps to avoid the confusion of English pluralization which can be tricky sometimes for non-fluent writers. Example: Person becomes people while activity becomes activities and data remains data, so it’s a bit confusing for a few users.

   2.You can consider using the “set theory” when you are using singular names. In simple term, any instance in the set is nothing but a representative of the set. So, orange is an Orange set. It doesn’t matter how many apples are in the set. 

  3. If you see according to SQL statement point, it doesn’t feel natural to write a plural name for a query on a single item.

Example:

SELECT id, name, description

FROM products product

WHERE product.name = ‘foo’ AND product.description = ‘bar’

If you are dealing with more complex pluralization:

SELECT id, name, description FROM activities activity 

WHERE activity.name = 'foo' AND activity.description = 'bar'

Still, if you are concerned about writing the best-looking code that has proper English, then my advice would be to use plural names for the tables whereas, use a singular name for the corresponding entities.

Ex:

/**

* @Person

 *

 * @ORM\Table(name="people")

 */

class Person

{

...

}

The table people store all the instances of possible object person. Thus, when we are working with object then it makes sense to use this syntax:

 $person = new Person () and $personàsetName(“foo”). 

Apart from that, you can follow these tips:

• To separate words (no camelCase or no spaces), for ex: product_quantity.
• For the tables use short names.
• Don’t use ID, use id.
• As your PK (Primary Key), don’t use table name followed by “id”. Use the only id it’s more than enough.
• It’s easier to read customer_bills rather than cub. To be more descriptive while naming it as table name should represent its content.