Database table names - singular or plural?

General Programming
1

It may be an old question that came up here again:

How do we best name data base tables, singular or plural?
Do we use ‘book’ or ‘books’, ‘student’ or ‘students’?

What is your take?

2

I use plural because there are many books or students records in the table

3 Likes
3

I prefer singular for table names…

Pros

  • Maps 1:1 to entity/class names in ORM and domain models
  • Avoids irregular plural ambiguity (person→people)
  • JOIN reads naturally: book.author_id = author.id

Cons:

  • SELECT * FROM book reads slightly odd

Of course you can have them just the other way around - no universal correct answer :wink:

So consistency matters more than the choice itself… Mixing is the worst option.

3 Likes
4

We looked at it from the record perspective. You don’t have books (plural) in a record you have a singular book.

To take it one step further my DBA wife insisted that we prefix our tables and views. So it would be t_book and v_bookhistory (or vw_history) and so on. A many to many table had a tx_ prefix so, again, you know what sort of table you’re dealing with just by looking at it. Lookup tables had a prefix too but for the life of me I can’t remember what it was as I don’t think it was tl_. tm_ maybe? Been too long.

Primary keys always had the table name and _id suffix*.* So it would be book_id. That way if you had joins and views with multiple tables and each brought back the primary key you wouldn’t have to alias them and you always knew which primary key you were talking about. We had other rules for date fields but I feel like those weren’t as important.

At first I thought this was way overkill, and it is. BUT, given 25+ years of database applications we worked on it made sense to have a system and keep to it. Remember that you’re not designing for right now, but for the poor sap that might have to figure out your database design 20 years from now - even if that poor sap is you.

Overall, I’d say have a design system and be consistent. Don’t mix and match plural and singular names for table or field names. Keep them predicable. Make your developer life as simple as possible with your database design.

Any time you think you have to have a query to get some bit of information to query again to get additional information this is almost always a sign of needing a view or at the very least a query with a join.

2 Likes
5

Yeah this is the main point – have a system and use it consistently.

Personally I prefer plural table names but it’s not a hill I would die on or something.

1 Like
6

plural. then the individual entities (records) are singular

1 Like
7

I agree, that the object name should include some indication of what type of object it is, but after 20+ years of full-time database development, I took the opposite approach and put the indicator as a suffix.

That way, in autocomplete (intellisence), I could just start typing “book…” and all related data, views, functions and stored procedures would pop up together.

Sorta like [category]_[type]_[optional subcategory or action].

And I always preferred plural.

But to each, their own, eh :wink:

1 Like
8

I tend to plural for table names aswell. As old fart who still knows DB2 and dBase and 8+3 name limitatons I would like to add to use rather short names and like Bob’s “DBA wife” (Carol as I remember right?) mentioned: With self-explainantory prefixes. Same is valid for variables.

1 Like
9

Yup. Carol. I can say with some certainty that in 27 years of marriage our biggest arguments have been about database design. :wink:

Many of our arguments were solved with a ‘you just a view that gives you this information in one go, right?” The answer was always yes. But it took a heated discussion of why I wanted it. :slight_smile:

2 Likes
10

Thanks to all who responded and shared their views and wisdom!