的最佳实践是什么命名数据库表提供自然组织?What is the best practice for naming database tables to provide natural organization?

- 此内容更新于:2014-12-30


We put common prefixes on related tables to assure they display next to each other in our DB management software (Toad, Enterprise Manager, etc).

So for example, all user tables start with the word User:

  • User
  • UserEvent
  • UserPurchase

Ideally, in honor of the three great virtues of a programmer these tables should be named User, Event, Purchase respectively to save some typing, agreed?

Is this naming convention the best (only?) practice for grouping related tables together naturally?

人们会认为使用ID为每个表的PK……请为我的孩子们,对我来说,所有死者的爱尔兰作家不要# 39;t。当你有200个数据库中的表你# 39;我有接近200列与完全相同的名称和所有与完全不同的意义。完整的地狱。

(原文:People will argue for using ID as the PK for every table... please for me, for my children, for all the dead Irish writers don't do that. When you have 200 tables in the database you'll have close to 200 columns all with the exact same name and all with totally different meaning. Complete Hell.)

Bill Karwin的回复:粘度@Mark:我完全同意。“ID"但是,与科里# 39;年代问题吗?

(原文:@Mark: I totally agree vis. "ID" but what does that have to do with Cory's question?)


I tend to go against the grain in naming conventions on two counts here...

  1. I don't like using prefixes so that things group together in a given UI. To me the tables should be named such that in code they are easily readable and make sense. There have been numerous studies (mostly ignored by programmers) that show that things like using underscores, logical names, eliminating abbreviations, and eliminating things like prefixes have a very big impact on both comprehension and the speed of working with code. Also, if you use prefixes to break down tables what do you do when a table is used by multiple areas - or even worse it starts out in only one area but later starts to be used in another area. Do you now have to rename the table?

  2. I almost completely ignore name lengths. I'm much more concerned about readability and understandability than I am about taking 1/10th of a second longer to type a column or table name. When you also keep in mind all of the time wasted trying to remember if you abbreviated "number" as "no", "num" or "nbr" and keep in mind the use of things like Intellisense, using longer, more meaningful names is a no brainer to me.

With those things in mind, if your UserEvents table really has to do with events related to a user then that name makes perfect sense. Using just Events would end up giving you a poorly named table because the name isn't clear enough in my opinion.

Hope this helps!

Alix Axel的回复:你有任何关于你提到的研究链接?

(原文:Do you have any links regarding the studies you mentioned?)


I would use this notation i.e "UserEvent" in case this table is a representation of many-to-many relation between "User" and "Event" tables.

我不会使用一个命名约定的按字母排列地表名。工作时很高兴,但这不该由设计。 读乔Celko SQL编程风格的书”。“他在那本书的第一章是关于命名约定,由ISO 11179标准的元数据命名。他的建议之一是避免不必要的前缀的命名约定。

I wouldn't use a naming convention for purposes of alphabetizing table names. It's nice when it works out that way, but this shouldn't be by design.

Read Joe Celko's book "SQL Programming Style." His first chapter in that book is about naming conventions, guided by the ISO 11179 standard for metadata naming. One of his recommendations is to avoid unnecessary prefixes in your naming convention.

Cory House的回复:一个很好的观点。尽管Celco一些观点的方式。他咆哮只使用自然键和mySQL数据库不是真的降低了我尊重他的意见。

(原文:An excellent point. Although Celco has some views that are way out there. His rants on only using natural keys and mySQL not being a database really lowered my respect for his opinions.)

Bill Karwin的回复:是的,我同意Celko绝对是一个心怀不满和一个狂热者。但是我们可以采取他的意见与一粒盐,同时还从他的书获得有用的东西。

(原文:Yes, I agree Celko is definitely a grouch and a zealot. But we can take his opinions with a grain of salt, while still getting something useful from his books.)


I guess that the naming convention in SQL is or at least should follow the same guidelines as used in other programming languages. I have always preferred variable names / class name that clearly identified the purpose and that usually means more typing. I think the importent thing to remember is that code is only written once but read a lot of times, so clarity is vital. The last 4 or 5 years I have noticed that the variable names have grown from "eDS" to "employeeDataSet" and I think that IntelliSense is the main reason for that. I think we will see the same happen in SQL since Red Gate SQL Prompt and now MS SQL2008 have introduced Intellisense into the mainstrem database world.


I think it depends on how if/how you plan to expand upon your data model. For instance, what if you decided that you not only want to have User Events, but also, Account Events, or Login Events, and User, Accounts, and Logins are all different types of entities, but they share some Events. In that case, you would might want a normalized database model:

  • Users (Id int, Name varchar)
  • Accounts (Id int, Name varchar)
  • Logins (Id int, Name varchar)
  • Events (Id int, Name varchar)
  • UserEvents (UserId int, EventId int)
  • AccountEvents (AccountId int, EventId int)
  • LoginEvents (LoginId int, EventId int)

I personally favor naming conditions similar to what you have listed there, as it is a very logical pattern, they are organized in a way that you can easily find them, and overall the extra bits of typing typically doesn't cause many issues or reductions in time.


I also would avoid using prefixes in order to get meaning out of an alphabetically ordered list. It's generally bad data management to use names that way. If tables are closely related, then the software that manages the data model should be able to model relationships between tables, regardless of how the tables are named.

A name like UserEvents should be used when the content of each row describes a relationship between a User and an Event. If I saw such a table in a database that was new to me, I'd expect to see a primary key in this table made up of two foreign keys, one to the Users table and one to the Events table.

If I saw something different, I would have to slow down until I understood the designer's convention.

This last point raises some questions: home many new people will be coming up to speed on the database? What documentation will those people have available? How important are those new people to the success of the project that ijncludes the database, or the success of the database designer?