|
|
|
|
As I see it there are two distinct camps regarding this topic. There are those that could care less what method is used as long as it's consistent and used across the board. Then there are those that believe their way of doing things is so infallibly correct that all who dare oppose them should be doused in flaming feces.
Thankfully, I've never had the displeasure of meeting someone so tied to their database naming convention that it borders on religious belief. However, a lot of Googling leads me to believe that these people are out there and that some would rather commit seppuku than change their naming convention.
I am of the opinion that neither method is better, just different. Personally, I don't care to stand toe-to-toe with someone and yell NO PLURAL, NO SINGULAR like preschoolers. To be quite honest every time I design a new database I end up toiling over the plural vs. singular approach. Like most I started naming tables plural b/c it just felt more appropriate. Now that I have a few database designs under my belt and a new database to create (the one that runs this blog) I've finally mustered up the courage to "go singular". I don't claim this method is better, but it does have some unique advantages.
Since I have yet to find an itemized list of advantages for each method I've included my personal list below. Its been compiled over a few days of thought and Googling.
Singular - Promotes naming consistency with database diagrams (ER, ORM etc...)
- Makes people use correct grammar. It's much better grammatically to say "The product table contains products" than "The Products table contains Products".
- Promotes consistency with naming of primary key fields and lookup tables. For example, Product table has Product_ID, Employee has Employee_ID.
- Ensures alphabetical ordering of a table before its lookup or linking tables. Those that spend half their day using Enterprise Manager will appreciate this.
- Avoids confusion of english pluralization rules. For example activity becomes activities, box becomes boxes, person becomes people, data remains data, etc... (http://www.unc.edu/~jwatt/writings/naming_conventions.html).
- Natural for those that think of databases in terms of models (boxes and circles connected with lines and arrows)
Plural - Emphasizes the fact that developers often work with sets of data.
- Plurals often feel more applicable because tables are collections of one or more records. Most developers I've spoken to agree that naming a table customers feels better than naming it customer.
- Natural for those that think of databases in terms of SQL scripts.
I recommend everyone try singular table names at least once, you just might find singular fits you best.. ...
|
Posted Sep 09, 2004 @ 9:27 PM |
Comments: 27
|
Comments
|
TastyWheat
(12/6/2005 5:35:43 AM)
|
| |
I agree with the singular style of naming. Not only does it stay consistent with ER naming, it should make sense in terms of SQL as well. It makes more sense (to me at least) to say "SELECT name WHERE age > 40 FROM Customer;" because the script checks one record at a time. So don't think about it like this is the criteria for the whole table, but this is the criteria for a record and it checks each record in a loop. |
|
Eric Swanson
(5/10/2006 6:47:13 AM)
|
| |
I have personally thought long and hard about this and, at long last, have my own conclusion: http://www.ericis.com/Posts/default.aspx?id=184
Query fix to the last comment: "SELECT name FROM Customer WHERE ( age > 40 );" |
|
these,those
(8/6/2006 2:21:57 AM)
|
| |
these those |
|
DB MAN
(8/25/2006 11:25:12 AM)
|
| |
rubbish, go plural, the reasons for singular are extremely week. I have no problem relating boxes table name to boxid field. It ain't rocket science. |
|
Architect, Developer, DBA
(9/13/2006 12:58:29 PM)
|
| |
I believe that it is pretty much pointless to argue over the pluralization or singularization of object names. The real point here is understanding what an object contains. Yes, tables are collections of single entities. However, from a readability stand-point, singular makes the most sense when looking at the WHERE clause of a SELECT statement. It also makes the most sense since there are "inconsistencies" in pluralization. So, for example...
SELECT Name FROM Customer WHERE Customer.ID = 1
OR
SELECT Name FROM Customers WHERE Customers.ID = 1
The first has a better readability in terms of the WHERE clause. However, the second has better readability in terms of the FROM clause. In conclusion, there are more important discussions than this. It is most important to stay consistent, but it is easier to stay consistent with singular names. |
|
Richard
(2/8/2007 6:30:00 AM)
|
| |
If singular is the way to go, why does the schemas of the databases (like the system tables in Microsoft SQL Server) has plural table names like SysLocks and SysObjects ? |
|
Just Visting
(2/12/2007 2:37:59 AM)
|
| |
The other big advantage of singular is that the ISO standard is singular for names and present tense for verbs. For international software, plurals are out because some major languages like Japanese don't have them. |
|
GSR
(3/22/2007 2:45:42 AM)
|
| |
It is very nice article. I decide to use singular noun |
|
Confused
(5/14/2007 7:30:00 AM)
|
| |
I am a strong supporter of the singular approach and also of dousing my opponents in flaming feces. But how do you make it dousable yet combustible at the same time? |
|
teced
(5/15/2007 12:20:28 PM)
|
| |
go singular ! |
|
andrezero
(5/23/2007 10:06:20 AM)
|
| |
LMAO!!!!
I was googling for the exact same reason this article was written!!
I've always been for the singular approach.
I believe the division really ties to where you stand in the software life cycle.
I would say analysts and designers tend to be singular, programmers tend to go plural.
The reasons are obvious if you read the above list of advantages and some of the comments.
Anyways, after 12 years of application development and data modeling, I still had some of this reasoning unclear (until I read this article)
Thank you guys.
I'm sure that singular is the way to go but it really takes some courage, specially when you know you'll get the programmers asking questions and criticizing!
@Richard: it's just the same with MySQL... I'd say that typically, the engineers that built the race cars were not race car drivers |
|
andrezero
(5/23/2007 10:12:35 AM)
|
| |
...eheheh! and now I'm going to google for "primary key id or tablename_id"...
I've always used PK's named just "id"
but it bothers me that DBDesigner only creates the relation automatically between fields with same name!
it always seemed obvious to me that user.group_id is the exported FK of user.id
but maybe I'll just start using user.user_id as PK... don't know... googling now... |
|
Woogz
(6/14/2007 4:23:19 AM)
|
| |
Interesting reading.
In the end I've decided to go singular (for now!). What tipped the balance was the English pluralization advantage.
I have a table which could be called either singular "Assembly", or plural "Assemblies" both of which seem fair enough. However the primary key "AssemblyID" (or "Assembly_ID") sounds much more reasonable than "AssembliesID" (or "Assemblies_ID") which seems at best a little awkward and at worst nonsensical.
Thanks for the article and comments... |
|
ee
(7/22/2007 11:51:41 AM)
|
| |
Woogzie: Name your table Assemblies and your column AssemblyID.
select * from assembles where assemblyid='3'
Not too bad eh? Now concentrate on working and let the idiots continue debating. |
|
John H
(8/7/2007 7:52:06 AM)
|
| |
I agree with the reasons for using singular. A row of data is a single object. I also like to look at a primary or foreign key and know immediately what table it's referring to (sans the suffix.)
What about naming conventions? I like to use all lowercase. For example company_id versus Company_ID.
Uh-oh, sounds like another blog article to me! :-) |
|
Tellioglu
(8/11/2007 12:23:15 PM)
|
| |
I can't agree with the point that table names in singular "make people use correct grammar". I am not a native speaker but "select a person from persons" sounds more grammatical to me than "select a person from person". Sometimes the singular for table names sounds better but sometimes the plural does.
I agree with "Architect, Developer, DBA" that singular sounds better in where clauses but than one could use aliases. I normally do if multiple tables are involved.
SELECT Name FROM Customers c WHERE c.ID = 1
or even
SELECT Name FROM Customers Customer WHERE Customer.ID = 1
;) |
|
Dooch
(8/17/2007 11:11:54 AM)
|
| |
"Persons" is a pet peeve of mine. It's People, "Persons" isn't even a word.
Another example of why the singular approach is best :) |
|
person A
(9/6/2007 1:52:37 AM)
|
| |
Maybe person is just a bad table name. Unless the whole of your table represents exactly one instance of the thing its name is describing, plural makes more sense. |
|
dpenton
(11/1/2007 5:07:20 AM)
|
| |
Singular + "Set"...
so a table that has records with user detail: UserSet
But this all depends on the person doing the application. |
|
ThatRickGuy
(12/26/2007 6:41:54 AM)
|
| |
My preference is Singular. But the bigger issue is consistency. Nothing like trying to keep queries straight when going from 'Form_Args' to the 'Arg' table.
@ee:
Now write a script that creates your code data object for you. One that knows the plurally named table 'Assemblies' has a PK named 'Assembly_ID'. And also that the table 'Data' has a key 'Data_ID'. In order to do so you'll need to dig a lot deeper into the tool set to get the description of the table.
On the other hand, if you just leave your tables named singularly, you know that for any (non-linking) table 'x' there will be a field 'x_id' |
|
bill
(1/28/2008 4:43:29 AM)
|
| |
Every db design book I've ever read says to use singular. The idea is not that a table would obviously have multiple records, but you think of it in terms of object-orientation: a record in the Customer table is an instance of Customer. When you're building a class file that works with a specific db table, that class is singular -- you'd wouldn't name the class Customers, right? So the table name matches the class name. |
|
David Sullivan
(1/29/2008 5:15:21 AM)
|
| |
I have been a developer for over 24 years and I am a singular kind a guy.
However i did find an option that makes my compulsion with good patterns, happy. I name my Tables singular then I use singular names on queries for singular Items or plural names on queries for many items.
Table Car has ID, Make Model
Query Get_ALL_Cars, or Get_Cars_BY_Make
Query Get_Car_BY_ID
Quick and Simple.
My next question is why do people preface the ID field with the name ex. USER.USER_ID instead of USER.ID
I find the USER.ID style is easier to read and makes more reusable code.
Suggestions?
Dave Sullivan
Developer
RapidMind Solutions Inc. |
|
socratea
(2/5/2008 12:11:33 PM)
|
| |
Convention of plural will not hold unless you live in a small world.
When you are designing a database for a customer in a country such as Japan, for instance, don't event try to make table name (or anything) plural - there is no plural form of nouns in Japanese language.
Singular works everywhere; plural does not. |
|
ix
(2/15/2008 1:51:45 AM)
|
| |
Off the beaten trail, but the reason for naming a PK user_id and not just id is because when looking at other tables where the column might be a foreign key it can't be named "id" in the other table because it would conflict with that table's "id". Well, duh, so you name it "id" in the user table and "user_id" in the "log" table. Here's the thing--I like the columns in foreign key relationships to have the same name, that way you can look at a list of columns and immediately know where the id is coming from. user.user_id --> log.user_id. customer.customer_id --> order.customer_id, etc. Now, the rubber hits the road when you're aliasing out the wazoo. Again, I like c.customer_id = o.customer_id much better than c.id = o.customer_id. I like being verbose so that there's no question. But then again, I write short paragraph comments in code, and do things like (a*2)+(b*3) instead of a*2+b*3.
Just my preference, and makes life easier when trying to keep as many plates spinning as I have to. |
|
deko
(2/17/2008 2:03:23 AM)
|
| |
When I first started programming, the natural inclination was plural because it made sense logically: tables are collections of things. But what about nouns like Email and Furniture? I'll concede it's a bit easier to maintain a consistent convention with singular table names. But logic itself is consistent. I'm not sure I want to abandon logical consistency (e.g. plural names for collections) for a contrived consistency.
If we abandon logic, the universe will collapse in on itself (a fate far worse than being doused with flaming feces). In the end, as long as we are consistent in our convention, all is well. As for me, you can find my post in the Posts directory of this web site. |
|
Richard
(4/16/2008 1:51:20 AM)
|
| |
Yes, for both table names and directory names, I try to use singular, but sometimes when in a rush, I forget and so there are a few plurals in there... |
|
spiderman
(6/29/2008 1:27:10 AM)
|
| |
singular rules |
|