×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Proper database table naming convention
3

Proper database table naming convention

Proper database table naming convention

(OP)
I was curious if there is a proper naming convention used when creating database tables?

Thanks.

RE: Proper database table naming convention

There are many standards. Pick one and stick to it. It's most important to be consistent. Someone else will probably give you suggestions or links to various standards.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Proper database table naming convention

I'm not a big fan of most of the standards that I've seen.

I try to keep it do a basic view rules.

1. Be descriptive and don't use abbreviations unless they are industry standard accepted abbreviations.
2. Don't start the table name with tbl.  We know it's a table, we don't need table or tbl in the name of the table.
3. No spaces, hyphenes, symbols.  No one likes putting [] around the table names.  If you want a space use an underscore.
4. Don't make everything uppercase or lowercase.  Use uppercase letter to be the work seperators (ie "CustomerAddresses", "PhoneNumberTypes", etc).  This makes the table names much easier to read then if all uppercase or all lower case.
5. Most important of all, keep the names simple.  If you can describe the table in one word use that.  Don't use a very long table name just because you can.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Proper database table naming convention

mrdenny's points are excellent

2a. if you do, for some reason, need to preface all your table names with "tbl", then please, be consistent, you must therefore also preface all your column names with "col"

6. use plural table names -- this is to remind you that you are dealing with sets of rows, not a singular object

r937.com | rudy.ca

RE: Proper database table naming convention

Excellent points so far.
However I slightly disagree with one of them.

Before applying rule 4 (Don't make everything uppercase or lowercase) you should consider what possibilties your database system can offer.
Usually I am working with Oracle. Names of objects are in uppercase by default, but you may use cases indifferently. So these two queries are equivalent:
select my_column from my_table;
select MY_COLUMN from MY_TABLE;
Actually it is possibly to use lowercase or mixed cases too, so that MY_TABLE and my_table and My_Table will be different tables. But this is a bit tricky. Once you have defined a tablename as My_Table, you will always have to remember those tricks. Otherwise you will get an error message because table MY_TABLE does not exist.
And tricky naming conventions are nothing but a mouse trap for those who will have to deal with it later on, imho.

For separators in Oracle, you usually use an underscore.
CUSTOMER_ADDRESS instead of CustomerAddress

regards

RE: Proper database table naming convention

This is true.  My comments are from the Microsoft SQL Server side of the world.  Oracle folks (as hoinz pointed out) tend to do a few things differently.

Quote (r937):


6. use plural table names -- this is to remind you that you are dealing with sets of rows, not a singular object
This is very true, but can get tricky.  What I ususally do would be a "Customers" table which holds all the generic customer information (account number, etc).  For the customers addresses I'd use a table called "Customer_Addresses".  I use the name that I did, because each customer can have more than one address, so to spell that out in the name I use the singular of the Customer and the plural of Address.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Proper database table naming convention

oh, i do the same too -- BookAuthors, CustomerOrders, StudentClasses...

both "parts" of the name don't have to be plural, just the combination

and in a many-to-many relationship, which way do you name it?  BookAuthors or AuthorBooks?  i usually name it according to the way that it is used most frequently, including the user interface

what i mean by this is that you will almost certainly always show the authors when showing a book, but it's not as likely that you will always show the books when showing an author

so my table would be named BookAuthors smile

i guess the only time calling it BooksAuthors makes sense to me is if most of the time you're doing a FULL OUTER JOIN between Books and Authors

r937.com | rudy.ca

RE: Proper database table naming convention

Agreed.  I would go with BookAuthors.

Also something to think about when naming tables is, does it sound normal.  AuthorBooks just doesn't sound right.  BookAuthors does (at least it sounds better).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Proper database table naming convention

Quote (r937):

6. use plural table names -- this is to remind you that you are dealing with sets of rows, not a singular object
This is one side of a contentious issue:  many people subscribe to the idea that the table name should be the description of a row in that table.  Hence, you would have a "Customer" table.  One counter-argument to the "plural name" implies "set" notion is that it obscures the fact that a set can be empty or have a singular member.

As johnherman said, "[Be a man,] pick one and stick to it."  The worst of all worlds is to mix and match.

RE: Proper database table naming convention

Some databases don't support numbers as the first character of a table name, So I would limit it to A-Z and underline (I disagree their use is bad, just be consistent).

I also disagree, that tbl is a bad prefix, but it's better, if you make some difference, eg sys for system tables, met for meta data (eg for dynamicla, data driven applications or components) etc. If all tables have the tbl or tab prefix, it's quite useless of course. The only thing that would justify this was a list of symbols you make with some project documentation, but then you would normally group symbols by their nature anyway.

Regarding tables used for n:m relation I would use something like BooksOfAuthors, or with a prefix txBooksOfAuthors or txBooksAuthors (t for table, x for cross table).

Bye, Olaf.

RE: Proper database table naming convention

I'm with harebrain. Never use plurals for table names. Although I do accept that it's a contentious issue.

And I'm all for CamelCase to separate words, rather than underscore which looks clunky and involves extra typing.

Cheers, Mike.

RE: Proper database table naming convention

hello mike

what do you call your Orders table, then?   winky smile

r937.com | rudy.ca

RE: Proper database table naming convention

Never new some much thought went into naming a table. Without knowing a lot of these "standards" I went back and looked at some of mine and I'm happy to say I've followed most of them although I do use tbl but not col.  

Do any of you put the data type as a prefix to your column names?  (strLetter, lngNumber)  I don't do this either as it makes it confusing with variables in my code.

Good discussion here!!

I tried to have patience but it took to long! smile  -DW

RE: Proper database table naming convention

I never put the data type in the column name.

If I want to know the data type of a column I'll go look at the meta data for the column.  There's no reason to store the data type twice.  It also makes the column names more confusing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Proper database table naming convention

good one, mrdenny

similar reasoning for why i don't call them tblFoo and tblBar

if i should ever run across a name in the FROM clause and i can't remember if it's a table or not, i can find that out easily enough by looking at the metadata

which more or less leads to some interesting questions, like "can you use a table name anywhere else besides a FROM clause?" and "is there anything else you can put into a FROM clause besides a table name?"

i won't spoil people's weekend fun by hinting at the answers

winky smile

r937.com | rudy.ca

RE: Proper database table naming convention

rudy in answer to your questions - yes

Questions about posting. See FAQ183-874

RE: Proper database table naming convention

Hi rudy,

We don't have an Order table, only an order by.  blllttt

RE: Proper database table naming convention

IDEF1X

December 21, 1993

Section:3.1.2 Entity Syntax

...The entity name is a noun phrase that describes the set of things the entity represents. The noun phrase is in singular form, not plural.  Abbreviations and acronyms are permitted, however, the entity name must be meaningful and consistent throughout the model...

Does this settle it? Not by a long shot. I'm sure this debate will continue until the second coming of Codd ;)

RE: Proper database table naming convention



i'm glad no one has yet mentioned _____


winky smile

r937.com | rudy.ca

RE: Proper database table naming convention

That sounds way to offical to be used in this debate.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Proper database table naming convention

Good one Denny ;)

RE: Proper database table naming convention

[quote harebrain]
many people subscribe to the idea that the table name should be the description of a row in that table.
[quote]

Assume you can refer to a field of a row with tablenam.fieldname, eg customer.id or customer.name is of course good, but so is customers.id or customers.name...

You mostly use a table name within SQL, and select ... from customers seems more natural to me.

One other thing I'd avoid is two names of tables or fields that are identical besides case, as there still are and will be languages that don't differentiate this.

Regarding tye prefix: It's true you can read this from meta data, but source code is for the prorammer, not the program, if you read and work on code you don't take a look at the meta data of a table. Tables are normally out of the scope for such things as intellisense, so as a programmer your clue about the field type is a type prefix.

Regarding keeping apart field names and variable names, a variable also has different scopes, I normally have two prefixes of scope and type for a variable. With table fields, the scope is the table, I don't have that prefix. Readability is given by CamelCase or underlines. Don't give the argument it's more typing, it's one character. If you're lazy at typing, intellisense should jump in, otherwise you will also be tempted to use abbreviations and names not descriptive enough.

Bye, Olaf.

RE: Proper database table naming convention

(OP)
Great Post!
Thanks to everyone.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close