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.

5 Answers
5

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.

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..
–
MarianMar 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 WhitezelMar 8 '11 at 23:54

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

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.

Why product_groupv and not product_group_v for views (if you insist on this disticntion of views and tables)?
–
ypercubeFeb 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üşhanFeb 14 '13 at 8:43