Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some way like ct_, lut_, or codes_? Are there any other do's/don'ts?

I'm using MS SQL Server and have many databases with many tables so it would be nice to have something we can use as a standard with some supporting rational.

share|improve this question
add comment

5 Answers

up vote 23 down vote accepted

OK, first NEVER put tbl in front of a table's name. It's a table, we already now that. That's called Hungarian Notation, and people stopped doing that 5+ years ago.

Just call the object based on what it is. If a table holds employee data call it "Employee". If it holds information about computers call it "Computer". If it maps computers to employees call it "EmployeeComputer" or "ComputerEmployee" (personally I like "EmployeeComputer" better).

There's no real right naming convention to use (other than to not use Hungarian Notation). As long as the object names make sense that it what is important.

share|improve this answer
9  
In addition to that, please don't put the names of the tables as plural nouns, as I've seen examples of Employees, Cumputers.. We all know tables are supposed to get many rows, not one.. –  Marian Mar 8 '11 at 21:05
1  
Why would you create views of a table? All a view is, is a stored select statement. The data isn't materialized behind a view unless you are using an indexed view. I pretty much never use views, never have. There certainly isn't any performance benefit to doing so. –  mrdenny Mar 8 '11 at 23:01
1  
We use views when we want to do something like join a couple tables or translate code values to human readable values. The second situation is the one is where you would end up with a simular name. –  Beth Whitezel Mar 8 '11 at 23:54
2  
Mr. Denny's answer and following contributers are backed up by this: dba4life.blogspot.com/2007/11/… –  user1161 Mar 10 '11 at 6:54
1  
Stored procedures shouldn't ever start with "sp". Objects that start with "sp" are marked as special and the SQL Server will look in the master database for the object first before looking for the object in your local database. This is why you can run stored procedures like "sp_who", "sp_who2", etc. from any database on the server. Starting the names of views with "vw" isn't needed as I can easily see what objects are views by querying sys.views. –  mrdenny Aug 17 '11 at 0:10
show 4 more comments

I'm content with these since years ago

  • table names: small caps and underscores, singular {customer, product}
  • table names for many to many relations: tablename1_tablename2: {customer_product}
  • views: small caps added v to the end {customerv, productv, product_groupv}
  • stored procudures: table name and function {customer_select, customer_insert, customer_delete, customer_update}

if you have a cheap shared hosting package, you'll need to use the project abbreviation in front of all your objects, like for example, database administrators site, da_users, da_questions

share|improve this answer
    
Why product_groupv and not product_group_v for views (if you insist on this disticntion of views and tables)? –  ypercube Feb 14 '13 at 8:35
    
@ypercube because I'm too lazy to type one extra underscore and I make typing mistakes easily. I read the words easier when I use an underscore and I use v for separating view from tables, and v is not a word so i dont use underscore.. It looks inconsistent, yes, but I find this format practical. –  Uğur Gümüşhan Feb 14 '13 at 8:43
add comment

I don't know that there is really any "best" naming convention out there, as it really boils down to personal preference and ease of development. My advice is to pick a naming convention and adhere to it. If you wish to separate words with an underscore, do so in all of your database objects. If you wish to use camelCase, do so in all of your database objects.

In my shop we adhere to the following rules:

We separate words with underscores and use all lower-case letters.
Our table names describe what they are: dbo.person, dbo.invoice.
Our many-to-many table names also describe what they are (with the addition of mm to indicate a many to many relationship being mapped: dbo.person_mm_address. Our user-defined stored procedures describe both the object and the action being performed: usp_person_select, usp_address_select_by_city Our views and functions follow the same rules as stored procedures. Our indexes include table, key columns (in order), and an indication of clustered/non-clustered: ix_person_last_name_first_name_nc

Just because this is what we use in my shop, it doesn't mean these rules are right for you. Pick something that you and your development team agree is both useful and easy to develop with, and establish a culture of knowing and using whatever naming convention you decide upon. In our case, this includes code review for any objects created in a database. Over time, the combination of a documented naming convention and peer code review has led to fewer and fewer deviations from convention.

I hope this "non-answer" helps in some way.

share|improve this answer
add comment

Personally I'm a great fan of the Fanö Bedingung, meaning here that not name is allowed to be the beginning of another valid name.

And second the Hamming distance between two names is better more than one different letter.

Yes that are rule from predigital times, but they make life easier.

And third names must be pronounceable, or you will became mad, when voice recognition becomes true.

share|improve this answer
2  
When voice recognition becomes true I'll be mad anyway. :-) –  Beth Whitezel Mar 8 '11 at 22:19
    
Only if you do telethon support –  bernd_k Mar 8 '11 at 22:22
1  
When voice recognition comes around, I'm going to become a truck driver. That or a crazy hermit. –  mrdenny Mar 8 '11 at 23:14
    
Could you explain in more detail (perhaps with an example) what "Fano Bedingung" is? –  Nick Chammas Nov 8 '11 at 18:32
1  
@NickChammas I think in English we call this Shannon-Fano coding. –  Iain Elder Feb 14 '13 at 17:20
add comment

We use schemas (think of them as namespaces in SQL perhaps) for both permissions and grouping. So instead of "tbl" etc we have

  • Data.Thing
  • Data.ThingHistory
  • Data.Stuff

No code goes into the Data schema. No tables live outside the Data schema. This can extended of course to have a Lookup or Staging schema if you wish.

For views we use vw but this was to distinguish them from tables in SQL Server 2000 and it's kinda legacy now

share|improve this answer
3  
+1 for recommending schemas. This will come handy while maintaining large db with 100s of tables. We use schemas for functional grouping too.. as did in AdventureWorks db –  Coder Hawk Mar 9 '11 at 7:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.