How should I name my Tables when creating a new database?
Client or Plural:
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Up to you. Just be consistent though.
Personally I prefer singular based on what each *row" stores: Order, Product, User, Item, etc.
This matches my modelling (via Object Role Modelling) where I use singular entities/types.
One reason is that plural fails when you have link tables:
Products would give
OrdersProducts. Neither sounds correct
Or history tables (of course you can use schemas for this):
OrdersHistory or (no!)
OrderHistory be better?
Concerning singular versus plural table names, the subject seems to be controversial, but it shouldn't be.
While a table is a collection of multiple records, a table is named after the definition of the one type of record that it contains. If a table was allowed to have a different name than that of the type of record that it contains, you could give the table a plural name, so that you could for example have an Employees table containing multiple Employee records. But the designer of SQL did not provide for separate names for tables and record types.
Things work out more logically for object oriented programs that use the data, if the name of a record type (and by extension the table name) is kept singular, as it will correspond with the name of the class you would use to describe one record.
If you then want to identify a collection in the program, you can use a plural, or better, use an appropriate modifier, such as EmployeeList or EmployeeArray.
There is also a problem with irregular plurals for automatic code generation and programmers who have different language backgrounds or ideas about the formation of plurals in a program.
The English language is not a good and proper programming language, and trying to make database and program statements conform to English because it sounds better to read one of those statements is a mistake.
Just as @gbn's answer I think this is most a matter of preferences and just like him I recommend that any choice you made, apply it everywhere (in that DB at least). Consistency is worth it.
My preference, however is that a plural sounds better in
SELECT statements :
SELECT Id, Name, Status FROM Persons WHERE Status <> 5 --5 meaning deleted
I mean in this case, at least, there are several persons in the table and several of them are returned to the client.
"order" is a reserved word. "orders" is not
"user" is a reserved word. "users" is not
"session" is a reserved word. "sessions" is not
"result" is a reserved word. "results" is not
"relative" is a reserved word. "relatives" is not
Those seem like common words that might go in line-of-business database. Plural words seem to be less common as key words than singular words. Therefore, it might be beneficial to use plural table names so as to avoid conflict with SQL key words.
I believe SQL table should have plural names. It simply reads much better.
A table of book records should be called books. The ORM should use the same convention. The Books object is a collection, and presides over all records in the Books Table. A Book object presides over a single record.
This makes coding more natural.
select name, publication_date from books where publication_date > '2000-01-01'; books = Books() for book in books.get("publication_date >= '2000-01-01'"): print book.name
After working with programming for some years I have concluded that pluralization is a needless complication. My opinion is that according to the KISS philosophy a programmer should strive for the laziest and easiest solution to all problems for time and efficiency reasons. Thus singular gives you less work needed in all scenarios.
It's a very personal thing. I have been using singular form for 30 years. But I can see why people like plurals. The books - authors is interesting as I think booksauthors is not wrong. A book can have one or more authors. And authors may have written one or more books (e.g. co-written). It also just depends how you handle books written by more than one author. I agree with other answers; choose one and be consistent. In regards to reserved words issues. I think it is not hard to come up with workaround names. user -> app_user , session -> app_session, order -> customer_order
We view things from different perspectives, and I think the two camps are identified by:
The person who makes a correlation between the table name and the fact it represents a container, which can contain multiple rows.
So "user container" can contain multiple rows.
The person who does not make the correlation between table name and that fact it represents a container. Of course they know it is a container, but it's not there in the name.
An "egg carton" can have multiple eggs in it but that's obvious as the container reference is in the name, providing potential for multiple eggs. However with the singular table name "user" the container reference is not there in the name. eg "user_container" would likely be acceptable for people who prefer plural names.
I think this is also because of years of plural being common practice and in most online teaching material.
All this said, I think that technically speaking the singular is more accurate given that we're naming a single container, and containers can contain multiple (or single) rows.
It seems wrong to people as they mentally link the table name to the contents (multiple rows needs a plural name) rather than mentally linking the named container to the contents (a container allows for multiple).
As always though there's often not a right and wrong, and it's more about what suits the scenario, and importantly being consistent with whatever you choose.
If you are doing the project solely and there's no real reason to go either way do whatever you feel is best, or just preference. Apply the same when in a dev team and just come to a unanimous decision.