Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Proper database table naming convention 3

Status
Not open for further replies.

frankyberry

Programmer
Mar 15, 2007
33
US
I was curious if there is a proper naming convention used when creating database tables?

Thanks.
 
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
 
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]
 
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
 
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
 
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.

r937 said:
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]
 
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 :)

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
 
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]
 
r937 said:
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.
 
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.
 
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.
 
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! :) -DW
 
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]
 
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

;-)

r937.com | rudy.ca
 
rudy in answer to your questions - yes

Questions about posting. See faq183-874
 
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 ;)
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top