的最佳实践是什么命名数据库表提供自然组织?What is the best practice for naming database tables to provide natural organization?
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:
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?
(原文：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...
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?
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!
(原文：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.
(原文：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?
- 如何加入另一个MySQL表在使用主/明细在Netbeans示例表单吗?How to join another MySQL table when using Master/Detail Sample Form in Netbeans?
- MySQL条件语句MySQL conditional statement
- 我怎么能有MySQL写输出文件作为不同的用户? - How can I have MySQL write outfiles as a different user?
- SQL查询字段不包含$ x的地方SQL Query Where Field DOES NOT Contain $x
- MySQL更新错误当使用特殊字符MySQL update error when special characters are used
- 的最佳实践是什么命名数据库表提供自然组织?What is the best practice for naming database tables to provide natural organization?
- 在相同的表一对多关系One-to-many relationship in the same table
- 在MySQL外键吗?Foreign keys in MySQL?
- 帮助编写一个查询:混乱的顺序操作的GROUP BY和秩序Help write a query: Confusion over order of operations of GROUP BY and ORDER BY
- MySQL加密列MySQL encrypted columns
- 全文搜索相关性测量在吗? - Full-text search relevance is measured in?