21

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.

Improve this question

5 Answers 5

Reset to default
29

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.

Improve this answer
11
  • 11
    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, 2011 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, 2011 at 23:01
  • 2
    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 Lang
    Mar 8, 2011 at 23:54
  • 2
    Mr. Denny's answer and following contributers are backed up by this: dba4life.blogspot.com/2007/11/…
    – user1161
    Mar 10, 2011 at 6:54
  • 1
    @Marian: I use plurals because they make the queries read more naturally and solve collisions with keywords. Many systems I have worked with have had an order entity. Using plurals makes the table name orders not order and avoids having to quote the table name every time it is users. This also applies to group and other keywords. Fortunately, few keywords collide with common entities.
    – BillThor
    Mar 11, 2011 at 15:00
13

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

Improve this answer
2
  • 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
    – Sandeep Kumar M
    Mar 9, 2011 at 7:07
  • +1 I've been using schemas more and more over the years.They both help explain the content and manage access.
    – Beth Lang
    Mar 24, 2020 at 18:42
5

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.

Improve this answer
5
  • 2
    When voice recognition becomes true I'll be mad anyway. :-)
    – Beth Lang
    Mar 8, 2011 at 22:19
  • Only if you do telethon support
    – bernd_k
    Mar 8, 2011 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, 2011 at 23:14
  • Could you explain in more detail (perhaps with an example) what "Fano Bedingung" is?
    – Nick Chammas
    Nov 8, 2011 at 18:32
  • 1
    @NickChammas I think in English we call this Shannon-Fano coding.
    – Iain Samuel McLean Elder
    Feb 14, 2013 at 17:20
2

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.

Improve this answer
1

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

Improve this answer
2
  • Why product_groupv and not product_group_v for views (if you insist on this disticntion of views and tables)?
    – ypercubeᵀᴹ
    Feb 14, 2013 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, 2013 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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