The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

SQL convention : table prefix and plural/singular?

Not wanting to start another philosophical debate here, just want to do a quick check for things I may have overlooked. I did do a search for sql naming conventions, including this one: http://discuss.joelonsoftware.com/default.asp?joel.3.651781.38 But there's a few things they don't really mention like table prefixes and the reason for using plural table names.

I'm planning on using singular table names and prefix definition tables with 'def', unless there's good reasons against that.

Singular because: 
- consistency  --> CompanyID, Company table, ProjectCompany table all have the same word spelling, which might help with search and filtering etc. Not to mention I wouldn't have to remember when the plural is used and when the singualr is used.

- in code --> A table isn't an object, but often times we have Customer class and Order class, in singular, and it would seem more uniform to use the same word, especially when pluralizing the word gets tricky.

- language concern --> English really sucks for plural form consistencies. For example I have an Addendum, Advice, PublicHoliday and Company tables. That's 4 different rules for 4 different tables. Plus some languages do not have plural and singular forms of nouns, and who knows maybe we'll be hiring a foreign person next *shrugs*

The only reason for plural that I've found is that it is more common now esp with Ruby On Rails et al. A lot of people mention being able to 'say' it easier, but I find the reverse is true sometimes as well. Even then that's pretty "weak" when considering the downsides. So why plural?

Secondly,I have a whole bunch of definition tables (CustomerType, AddressType, OrderType...etc). Having all the def tables listed together in management studio looks more organized. Besides, I plan on having the same column names (key, value) for all definition tables, naming them something obvious will be a good visual cue for column names. But if it's such a good idea why isn't this mentioned much more often?

by the way, should I drop the 'Type' suffix for redundancy?
db
Monday, October 06, 2008
 
 
Plural vs. Singular, I just can't bring myself to care, though our app always uses singular and I would err on that side just because it's a tiny bit shorter and simpler.

On the 'definition tables' thing: if all n tables have exactly the same columns, and are likely to forever, I would consider having a single table with an extra column for 'definition type'. You may find having dozens of table names may be unwieldy, and when you go to add more, it is generally less trouble to add a new row in an existing table than to create a whole new one.

Someone will probably flame me for suggesting this.
Greg Send private email
Monday, October 06, 2008
 
 
I've always liked plural because it distinguishes between an employee instance (typically one row) and the collection of all employees (the table).

More important than which you choose, please be consistent.
Lance Hampton Send private email
Monday, October 06, 2008
 
 
I'm one of those that think about these things too much too, but I've just come to prefer plural 95% of the time.

Ultimately I choose what looks better, for example I'll name a table "stores" but I'll name the "employee" table as that, only because "employees" is a bit funny to type out.

IMHO I just like the look of:

    SELECT * FROM stores WHERE state='NY'

better than

    SELECT * FROM store WHERE state='NY'

but the issue is entirely personal opinion, no reason to argue over it.  It's the people who think one is better than the other that have a problem, not me.
TravisO Send private email
Monday, October 06, 2008
 
 
I contributed to that original thread so I don't have much to add here.  Used to do plural, now I do singular.  No regrets.

"by the way, should I drop the 'Type' suffix for redundancy?"

Probably.  DefCustomer vs. CustomerType -- both say roughly the same thing except for the difference in sorting.  You could even do TypeCustomer which sounds weird but might be closer in meaning.
Almost H. Anonymous Send private email
Monday, October 06, 2008
 
 
The data modeling books I have read state it makes more sense from a "modeling" perspective to use singular table names.  The table name represents the entity your modeling, IE. Car, Order, Customer, Person , Client. 

It does not refer to the collection of rows contained inside.

So, I generally follow this rule as it made sense to me :)

In the end it probably doesn't matter, as long as your consistent.
Ben
Tuesday, October 07, 2008
 
 
I think that a table is like a type or a class; and that each row within that table is like one instance of that type.

There may be one-to-one mapping (ORM) from an SQL table name to a OO class name.

The names of my classes aren't plural.
Christopher Wells Send private email
Tuesday, October 07, 2008
 
 
+1 to Greg.

It is hard to care about this stuff, although it does seem to spark great debates. It really doesn't matter, as long as you are consistent.

The idea of a single table for "type, key, value" instead of numerous "key, value" tables is a good one and makes life easier in many ways. Have to be careful with contention on the table, but effect should be minimal if it is effectively read-only, with only occasional updates.

The concept of prefixing object names is a non-starter to me. There is no need to do this and it just sounds arbitrary.

Apart from anything else, there may be cases where you need to add additional columns to one of your "definition" tables, for example to support business rules. In this case, do you remove the "def" prefix and modify all of the  code that uses the table, or just leave it inconsistent?

I know you could "cheat" here, by creating a synonym, but that is worse, as it is contrived and non-obvious to maintenance guys two years down the line.

In conclusion, I never like encoding meta-data into an object's name, as I have seen too many cases where it has gone horribly wrong. It is hard to justify now, as it is trivial to store meta-data with our objects. It is not the 1980's so using RPN on table names is not the way to go.
Scorpio Dragon Send private email
Tuesday, October 07, 2008
 
 
I'll throw in my problem with the single-table 'definitions' (we call them 'lookups'). Every in-house system I've ever worked on that had single-table definitions had to eventually be broken down into multiple tables. It seems that we always end up needing different kinds of additional information associated, thus 'promoting' the definition to a 'real' table. I just stopped doing it, making my life much easier without having any negative impact on the perceived quality of the application.

That is also why I stopped using custom prefixes for the definitions tables. Now they're all just tables with names that describe the entity without getting into what kind of entity it is.
Ron Porter Send private email
Tuesday, October 07, 2008
 
 
@Christopher - that's probably the best reason I can think of for going singular.

Interesting discussion on it:
http://www.breaksoft.com/Blog/posts/2004/9/Plural_vs_Singular_Table_Names.aspx


SELECT * FROM syscomments;



The way I think of it is if you do something like:
SELECT product_id FROM products;

you will get back multiple things because you're selecting from a multitude of products.

SELECT product_id from product;

sounds more like it should be doing the equivalent of:
product.getId();  // which would return just one thing.

A database isn't OO to me, even if my current programming language is.

If I had a singular database passed on to me though, I'd continue adding to it using singulars.
Lance Hampton Send private email
Tuesday, October 07, 2008
 
 
Everyone, of course, is right that it doesn't "matter" and it entirely depends on particular scenarios.

Lance, that blog post pretty much summarizes why I'm going to go with singular. It's of course not the One True Way to do it, but in my case having a few table names with bizarre plurals and living in a city where 37.5% are foreign-born, not counting second/third generation, tipped the scale in singular's favor.

Ron, that's an excellent reason why I may not want to prefix the definition tables. I've never had one "switch" on me yet, but hey when it happens I don't want to be caught with my prefixes down. It'd be a shame to have them "scattered" among the regular tables in management studio, but better safe and messy than sorry and messy.

As for merging them into the one def table, like another poster mentioned sometimes you need to add a column for business rules, for example, "weight/filter/sort order" column. Convenient, for sure, but unfortunately not doable this time. Good to know others do endorse these "dirty but good" ideas though; keeps one broad minded.
db
Tuesday, October 07, 2008
 
 
If each DEF table needs different columns, then, for sure, you should have separate tables.
Greg Send private email
Tuesday, October 07, 2008
 
 
I prefer singular because I use fully-qualified fields in my queries. i.e.:

SELECT customer.name, order.id, order.price
FROM customer
INNER JOIN order ON customer.id = order.customer_id
ORDER BY customer.name, order.id


Reading that with plural tablenames makes my brain hurt.
Drew Kime Send private email
Tuesday, October 07, 2008
 
 
I've long preferred singular for readability/notional value ... but with the Entity Framework in .NET, some of the assumptions the auto modeler makes push you towards plural naming.
Andrew Badera Send private email
Wednesday, October 08, 2008
 
 
I agree that consistency is far more important than "the one answer that rules them all"--pick a way, document it (simply), then follow it.

Here are some of my thoughts on details:

Singular = simple, obvious, consistent, matches class conventions

Plural = longer, less obvious (natural language quirks like "children"), less consistent, can get confused with class conventions for collections of entities (Customers = collection of Customer)

Having separate tables for lookup data allows for many RDBMS strategies such as locking the data in memory, caching, finding/checking references/use, indexing, etc.  It also allows for individual table variations like additional columns.  Sometimes lookup data transforms into entities over the life of an application as requirements expand.

However, finding those lookup tables among all the others in a RDBMS management GUI (e.g., SQL Server Management Studio) can get messy.  More importantly, the different nature of that data (read only, maybe shared across applications) begs for special/separate treatment.  Therefore, consider placing in a separate schema/database away from the other tables.

On the other hand, I often avoid coding schema/database dependencies into my applications so that I can create multiple environments merely by copying/creating another schema/database.

I avoid using "Type" in names since it is so overused--I prefer "Kind".
Rob Williams Send private email
Wednesday, October 08, 2008
 
 
For those considering querying conventions during the design phase, here's something you may have overlooked:

SELECT * FROM Employees Employee
WHERE Employee.Surname = 'Smith';
Yevgeny Pechenezhsky Send private email
Tuesday, October 14, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz