Trivia perhaps, but this has bothered me for a long time. Over the years,
I've noticed that some folks are adamant that table names should be
singular; others are adamant they should be plural. Myself, I believe in the
plural school. I simply don't understand the argument that a table should be
named singular, as it holds a collection of entities. We have tables of
Customers, Contacts, Addresses, Quotes, Sales, etc. A column is AN attribute
of an entity, a row is AN instance of an entity. During development, when I
populate a list with Customers, the immediate thought is to use the
"Customers" table, not the "Customer" table. I'm interested in someone
convincing me otherwise.

Earl (brikshoe@newsgroups.nospam) writes:
I have a suggestion for a compromise! Let's use the Slovene for the table
names. Then the two camps can meet each other half-way and use the dual
number for table names!

ROFL!

And maybe we can also introduce inflections to designate actions. :)


ML

---
http://milambda.blogspot.com/

Or perhaps use a Macromedia flash encoded object in a table name so that
when you look at it through a graphical tool it gives an animated
representation of what the table is :)

Show quoteHide quote

Of course that's customizable - per user. And DBA's have a few extra
settings. :)


ML

---
http://milambda.blogspot.com/

Earl wrote:

Naming conventions are important but their most important feature is
that they are applied consistently. I can live with either plural or
singular names as long as everyone sticks to the same convention. What
I don't want to see is one table called "Customer" and another in the
same project called "Contacts".

Hear Hear!

I also only like lower case and separate words with _ because you can run
into all sorts of problems if you develop on a case insensitive database and
then go to a case sensitive one. I was part of a big project that had that
exact problem and it convinced me to not use camel case and to use lower
case and underscore instead.

Once you get used to seeing upper case keywords and lower case identifiers
then its fine, bit like moving from ansi 89 to 92 on the join syntax - what
a pain that was!

Show quoteHide quote

On Thu, 26 Jan 2006 11:41:25 -0000, "Tony Rogerson"
<tonyroger***@sqlserverfaq.com> wrote:
in <#Qswf1mIGHA.3***@tk2msftngp13.phx.gbl>

I'd have to agree with that as it simplifies moving your data in and out of
PostgreSQL.  :-)

---
Stefan Berglund

what!!! you don't like my naming system. LOL I am guilty of that.


Show quoteHide quote

There doesn't seem to be any concensus one way or another on this issue of
singular vs. plural naming conventions.

However, when I see table names prefixed with "tbl", I can't help but
cringe, becuase I know there will be more stupidity in store when I start
looking at their choice of keys, T-SQL, and indexes (or lack thereof).  :-)

Show quoteHide quote

I have never had a preference myself, only that whichever convention you
choose be used consistently.

Even then, although it looks funny to see one plural table and one singular
table in a database, the only real issue is that it may be more difficult
for the developer or DBA to remember which table names have an "s" on the
end.

The only arguement I can think of against useing plural names is that
(generally) tables contain more than one row, so the plural name is
redundant, unneccesary, and takes extra typing.

Personally, I can live with the extra character, regardless.

Show quoteHide quote

Of course, putting that extra character at the end of table names will
ultimately consume more disk storage space. It adds up at an exponential
rate, especially if one doesn't use table name aliases in their SQL queries.

That's the argument you give management.    ;-)

Show quoteHide quote

I would tend to agree.

Show quoteHide quote

The reasons to support the singular school might come from programmer’s OOP
mindset: when they design a table, they would rather regard the table
structure as a kind of class definition that represents a blueprint of the
instance of the entity. You might not know, in application side, usually they
will start from a Customer class (so need to have corresponding Customer
table in db at the time) and only later to have a collection class called
Customers. So, if a db modeler happens to come from a strong programmer
background, they tend to prefer singular name.

Just my 2 cents.

James


Show quoteHide quote

Could also be a language thing, if you stick to the singular you don't run
into any gramatical problems with differences in languages, especially
between English and American 'English'.

Also translates better for non-English speaking developers.

Show quoteHide quote

I can see both sides of the issues. For me, the only critical things for any
naming standards are:

    1. Is it understandable
    2. Be consistent is usage

I always use singular names for tables.  The reason is mostly convention
from when I was first learning how to design databases.  My mentor always
taught me to write out the requirements first and then translate to tables.
Like:

A Person can have more than one address. An Address can belong to more than
one Person. Etc.

Since we always used singular in writing, we named the tables the same.

Show quoteHide quote

I have never worked in any environment where there was only one
datastore and one approach (vendors, in-house built 2nd generation and
older) but for all of them there was one communality; the data is as
good as the understanding of its value.

I believe singular, with a good set of classes on top,  gets the most
value. But there is definitly no war going on, on the contrary we're
glueing more and more together!

Gerard

Not sure I agree.  I have always done collections as plural.  Most will
start with Customer, true.  But that is the "row" with the public properties
as the columns.  Then to make a collection of Customers, the natural thing
to do is create a "Customers" class that contains the collection and acts
like a list.  Or just create a generic List<Customer> and call the list
Customers.  You can't call them both Customer (in the same namespace anyway)
so you need a name for the collection that makes sense.  "Customers" seems
natural.  You could go CustomerList or something, but..

Show quoteHide quote

My rules have been to look for table names in this order:

1) Use the industry standard name understood by the users
2) Use a collective noun to show that it is a set.
3) Use a plural name when no collective noun exists.  The exception is
a table with only one entity it in and then it can be singular.

Thus "Employee" is bad, "Employees" is better but "Personnel" is
prefered.  The nice thing about English is that we hae a lot of
collective nouns.

Bear with me one more question.

1) From language perpective, a collective noun looks like singular word, so
they are not plural. Does that will cause inconsistency, which we are so
eager to achieve?

2) When I see a table has a foreign key sheep_id, in singular world, I know
for sure table Sheep sitting there to join. In plural world, I have to
remember whether it's Sheep, Sheeps (I know it's wrong spelling), or Flock.

3) Ususally how do you name the primary key / surrogate key. In sigular
world, I can consistently apply <Entity>_Id pattern. In plural world, do you
agree to use Flock to model sheep entity and use sheep_id as primary key? It
is acceptable to use Customer_Id as PK in Customers table, but I notice some
plural advocates simply use Customers_Id. What's your opinion?

Sincerely,
James

Show quoteHide quote

I have a question to whoever favor plural names. When you define a "link"
table used to model n-n relationship between 2 or even more entities, do you
like use names like "EmployeesDepartments" or "EmployeeDepartment" (suppose
one employee can belong to multiple departments).


Show quoteHide quote

On Thu, 26 Jan 2006 13:37:02 -0800, James Ma wrote:

Hi James,

I try to use a name that describes the link instead of just patching the
linked tables' names together.

Using your example, what does a row in the link table between Emplyees
and Departments represent? That an employee is assigned to a department?
Great, there's the name: Assignments.

What if I need to design 7 different kinds of "Assignments" in the database?

Show quoteHide quote

Qualify them:  "JobAssignments", "ParkingAssignments",
"GuardDutyAssignments", etc.

Now I found a better example. In the AdventureWorks2000 sample DB, there are
"CustomerAddress" and "CountryCurrency" tables. Could you give them better
names?


Show quoteHide quote

Could you give them better names? <<

"CustomerAddresses" with a SAN or FedEx code for the key.

"Currencies"  with the ISO codes for the curency and the country as the
key.

On Thu, 26 Jan 2006 14:15:02 -0800, James Ma wrote:

Hi James,

Joe Celko already answered some of these questions.

Truth is, I try not to give names to any table at all. I use whatever
the client calls the entities stored in them.

If a client assigns employees to departments, then the word "assignment"
is guaranteed to fall in a conversation about their business. And if a
client has 7 different ways to relate employees to departments, then
they'll certainly have 7 different words for them - if only becuase they
have to know what they are talking about. The art of database design is
not the design or the naming - it's knowing how to talk to the customer,
what questions to ask and how to ask them. You could almost say that
they design their database themselves - after all, the DB should support
*THEIR* business model, not force them to use mine.


And on Thu, 26 Jan 2006 14:27:05 -0800, James Ma wrote:

I'm not familiar with the AdventureWorks sample DB. And as such, I have
no idea what entities are modeled in these tables.

In the context of this discussion, I assume "CustomerAddress" to be a
table for an n-to-m relation between Customers and Addresses. I think
that is a suspect design. In most applications, CustomerAddresses would
be 1-to-n related to Customers.

And how about "CountryCurrency" - what does it store? The official
currency of a country - should be a foreign key column in Countries. The
currencies that are accepted in a country? Call it AcceptedCurrencies.
The exchange rate for a currency in a country? Call it ExchangeRates.
Etc.

James Ma (Jame***@discussions.microsoft.com) writes:
The standard in our shop says that we drop the plural in the middle:

users - userauthorisationgroups
authorisationgroups - authorisationgroupsfunctions

Then of course, there is the exception that could drive you nuts:
"particularsavingsforms". (Which is not a link table at all, although
it may look like one.)

Personally I feel "Countries" and "Currencies" are not as good as "Country"
or "Currency". Also name like "CustomerAddresses" gave me an impression that
it is 1-to-n relationship but not n-to-n. In contrast "CustomerAddress" never
makes me feel it's 1-to-1 but always the intended n-to-n.

Show quoteHide quote

The fact that you call it a "link" table shows the wrong mindset.
Links, pointers, locators, etc. are all physical terms for mechanisms.
These are relationship tables, not pointer chains or the edges of a
graph.  In order of searching:

1) Use the industry standard name of that relationship understood by
the users
2) Use a common relationship name
3) Use a compound name when no relationship name exists and they can be
related in one and only one way.  .

Something like  "EmployeesDepartments" sucks, since it tells you what
is involved, but not what their relationship is.  Try "JobAssignments"
instead.  Likewise, "EscrowAccounts"  and not "BuyerSellerLender",
"Marriages" and not "MenWomen", etc.

Again, once you start thinking this way, the names usually come
quickly.

All good food for thought. Thanks to everyone. I liked Celko's guidelines
(albeit I've waffled a bit from Personnel to Employees and back a few
times). For those who questioned the linkage designations, yes, I would use
"CustomersAddresses". That's very straightforward as to the n-to-n
relationship and even resembles English: "Customers' Addresses."

Show quoteHide quote

Finally, a topic of substantial substance and consequence to elicit
the opinions of so many true experts.

This is right out of the Groucho Marx school of relational theory:
'I have principals and if you don't like then I have others'


Show quoteHide quote

Replacement of C code with C# code
Case statement 'Operator' in Where clause
How to format a SELECT resultset as a comma separated list?
Unique Identifier inserting in sqlexpress
Trigger and OnDelete Cascade