table name singular or plural?

table name singular or plural?

Post by MP » Tue, 20 Dec 2005 14:58:07


Hi all,
Been reading every sql/ado/mdb/database related article, website, newsgroup
etc I can find to learn vb/ado/mdb/sql.
About half the articles I've found say always name your tables singular, eg
tblEmployee, and that each row is an instance of that singular object type.
The other half say name the tables plural, eg tblEmployees, as they are
always a collection of more than one object of that type.

What is the consensus here?
Thanks
Mark
 
 
 

table name singular or plural?

Post by Um9zaGFuIF » Tue, 20 Dec 2005 17:21:02

When designing a database it's a good idea to follow some sort of naming
convention. This will involve a little thought in the early design stages but
will save significant time when maintaining the finished system.

The benefits of using a naming convention are more to do with human factors
than any system limitations - but this does not make them any less important.

***Table names are plural, field name is singular

Table names should not contain spaces, should be split_up_with_underscores.
Limit the table name to 23 characters.

If the table name contains serveral words, only the last one should be plural:
APPLICATIONS
APPLICATION_FUNCTIONS
APPLICATION_FUNCTION_ROLES

***Fields should be unique within the database schema.

The convention is to prefix the fieldname with a 2 or 3 character
contraction of the table name e.g.

PATIENT_OPTIONS would have a field called po_patient_option

PATIENT_RELATIVESwould have a field called pr_relative_name

ABSENCES would have a field called ab_start_date


--
Software Engineer
Creative Software Solutions (Pte) Ltd.
No:80, Nawam Mawatha,
Colombo 02
http://www.yqcomputer.com/
+94714293268

 
 
 

table name singular or plural?

Post by Paul Cleme » Tue, 20 Dec 2005 22:36:09


Hi all,
Been reading every sql/ado/mdb/database related article, website, newsgroup
etc I can find to learn vb/ado/mdb/sql.
About half the articles I've found say always name your tables singular, eg
tblEmployee, and that each row is an instance of that singular object type.
The other half say name the tables plural, eg tblEmployees, as they are
always a collection of more than one object of that type.

What is the consensus here?

The general database naming convention is to use the singular for table names. However, you will see
this violated frequently (even in Microsoft examples).


Paul
~~~~
Microsoft MVP (Visual Basic)
 
 
 

table name singular or plural?

Post by Branden Jo » Tue, 20 Dec 2005 23:15:44

I have seen both naming conventions. I believe it is your preference; which
ever convention makes more sense to you. I would highly recomend not mixing.
My advice to you: "Choose one, and stick with it."

HTH,

Branden Johnson
 
 
 

table name singular or plural?

Post by Terry Kref » Tue, 20 Dec 2005 23:15:50

At the end of the day it doesn't matter so long as you have a convention you
stick to.

On your specific question I could argue the point either way, it all depends
on whether you are thinking about the table or the entity.



--
Terry Kreft
 
 
 

table name singular or plural?

Post by QmVybmFyZC » Wed, 21 Dec 2005 00:57:02

table name is not important matter. As long as you know what kind of table
is, that will be good. Real matter is how datastructure is created. That
should be your most concern.
 
 
 

table name singular or plural?

Post by MP » Wed, 21 Dec 2005 01:35:39


I agree. To that end I've been doing much research to learn the "right"
ways.
I realize naming conventions are not the most important issue but I'm trying
to learn "best practice" in any aspect i can.
In the process of that research I found various statements that, in respect
to naming conventions only,
were contradictory opinions.
That's why I asked again to test the waters here and see what came back.
It's the same as my previous research.
response 1: Table names are Plural
response 2: Table names are Singular
response 3: Either way - be consistent
response 4: Either way - be consistent
response 5: Doesn't matter

average interpretation: Either way - no real standard- be internally
consistent.

so I'm back (almost) where I started but now I'm more clear that despite the
statements that there *IS* a standard convention in the industry I see now
that, in fact, there is *NOT* a standard convention, but varying opinions
exist about that fact.

Since I'm totally new to databases when I read things stated as a fact
rather than being stated as an opinion, I have to dig a little deeper when I
collect conflicting "facts"

But I'm 100% in agreement that this is not the Most important issue.

Thanks to all.
Mark

ps I think I'd get a similar response if I asked the question:
"Should variable names have an Underscore in them?"
<vbg>
 
 
 

table name singular or plural?

Post by Ralp » Wed, 21 Dec 2005 02:35:44

"MP" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
table
trying
respect
the
I

I will go out on a limb. <g>

Table names should always be 'singular'. The reason is because they are
*always* a "collection". One might use 'plural' - meaning always add an
's'/'ies' which would be redundant, or do one or the other at various times
and thus not be consistent - Therefore, the best policy is to always use
"singular".

The use of "Underscores" has in the past been very language/platform
specific in nature. C was the biggest abuser and this drifted into 'system'
code. By edict in C/C++ (ANSI) they were limited to 'System' or 'Compiler'
namespaces. ('_' - system, '__' (2) vendor/compiler). This drifted into
databases, where 'system' or 'config' tables/names had underscores, but
'User-Defined' tables did not. I believe you will find that to be the
general norm.

Confining ourselves to databases... Using type_XXXXX, or object_XXXXX naming
is called 'warts'. It had some usefulness where table names were all upper
or lower case. However, as Upper/Lower Case (Camelback) has come into vogue.
They tend to be redundant and the waste of a character, as tbl_Customer is
just as clear as tblCustomer. There is some evidence that warts are more
difficult to read (McConnall), but it is marginal, and wholly depend on
experience. With editors that now underline regions of code they can
actually disappear at times. <g>

However, if it is used universally within any organization, it is likely one
could get use to it. Again consistency is more important that either
accepting or banning them.

IMHO, (based on C/System roots) limit 'underscores' to
hidden/system/configuration files/tables and ban them from user-defined
objects. But I have only the weak reasons as stated above to back me up. <g>

-ralph




 
 
 

table name singular or plural?

Post by QmVybmFyZC » Wed, 21 Dec 2005 03:03:02

putting '_' has to consider careful. Some of language is using it as system
code. However, if you tried to use '-' also have some problem. Access and
MSSQL don't recorgnize '-' as part of table name. If you need a space bew ***
words for table name, use '_'. Starting table name with '_' is not good.
 
 
 

table name singular or plural?

Post by Ralp » Wed, 21 Dec 2005 03:20:02


system
bew ***

Good point.

I wasn't that clear. Using warts (xxx_XXX) will generally work with any
application, but using only a leading underscore is often problematic.

You can use then, like spaces, if you delimit the name (in VB, MSAccess,
etc, with square brackets), but that often becomes a hassle.

-ralph
 
 
 

table name singular or plural?

Post by MP » Wed, 21 Dec 2005 04:31:41


That



ok now we're 2 to 1 for singular
:-)
I can go either way at this point so I'll wait a while and see who else
weighs in on this one.

to me it makes sense for a table to be named Plural since if I only had one
Employee I probably wouldn't need a database to keep track of him <vbg>
it just "sounds" weird to me to say TableEmployee (guess I'm stuck in my
thoughts of equating a table with a collection) If I had a collection of
Employees I'd probably name it colEmployees for no good reason except that
there were presumably more than one of them. <g>
but it doesn't matter to me at all what *my* opinion is, I'm trying to learn
the "right" way from the experts here.

Thanks to everyone who has (will) respond(ed)
:-)
Mark