- 浏览: 89007 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
httpping:
谢了
《oracle SQL性能优化》中文电子书 -
phywhm:
多谢多谢,看到这个解决到问题了,Hoho
[Exception... "Not enough arguments [nsIDOMHTMLSel -
qinkangwencai:
我遇到过这个问题,根据你 的方法解决了!万分感谢!
[Exception... "Not enough arguments [nsIDOMHTMLSel -
hn_evian:
不错,谢谢
《oracle SQL性能优化》中文电子书 -
alexyao8:
顶上
[Exception... "Not enough arguments [nsIDOMHTMLSel
突然发现创建数据库没有统一的命名规范. 下面一篇文章不错可以参考, 另外还可以参考MSDN-Oslo的表设计模式.:
The following list provides the SQL guidelines for designing tables for the “Oslo” repository.
- Create Tables to Store Entity Instances of a Data Model.
- Create a Primary Key Named Id.
- Add a Folder Column to Support the “Oslo” Repository Folder Design Pattern.
- Use Foreign Keys to Connect Related Tables within a Data Model.
- Consider Performance Implications of Table Design Choices.
- Restrict Access to Base Tables to the RepositoryAdministrator and RepositoryService Users.
- Add a timestamp Column for Use with Lifetime Services.
-
Follow “Oslo” Repository Design Patterns for the Localization of Strings and Resources.
SQL Naming Convention
转自:http://kurafire.net/articles/sql-convention
There are millions of people making applications that use some sort of database or another, and there are several dozens of conventions which you can use to make a more well-formed database schematic. The CMS that powers this website is written by a semi-selfmade SQL convention. This article explains how it works.
The Media Design Content Management System, which is what I use to create and manage this website, uses PostgreSQL as its database system, rather than the more well-known MySQL.
For those of you that don't know, SQL stands for Structured Query Language, and is pronounced as [es queue el] (and not as sequal, as that name had caused trademark conflicts 18 years ago). There are various database systems that are based on SQL standards, although all of them have their own extentions to the official SQL Standard. If you want to read more about SQL, see this Wikipedia page on SQL. This article focuses only on PostgreSQL and this CMS's implementation using it.
Before we get started, we should look at what a standard or convention generally requires: consistency. You can make a standard without a lot of consistency (see RSS for example), but then it probably won't be a very good or useful standard. And as it is consistency and logic that determine a lot of the quality of a product or standard, it is consistency and logic that should be a major focus point of a naming convention.
Our CMS's Convention
Short and descriptive
At all times, stick to using as short as possible database-, table- and column names, but not at the cost of descriptiveness. Make sure that one look at the name can explain the purpose and content of the table or column.
Examples: filename
, title
, moderated
, dateline
.
Underscores are evil
Using underscores between words generally means your table or column names are not short and descriptive enough. There really is little need to have columns like user_name
when username
works just fine. Not only is the latter shorter, but it is also better: sometimes you'll have a column name that is valid as two words but also as one word, and then people may come to expect it as one word only to find out you treated it as two and put an unexpected underscore in-between. If you're the only person that will ever, ever touch your database system and your code… you should still stay away from underscores. You don't know what the future brings. Stick to consistency, and consistency can only be achieved here by not using underscores in table and column names.
Say no to CamelCase
CamelCase is an often adopted naming convention, used mainly in programming languages like Java and JavaScript. Its principle is simple: start every new word with a capital letter: camelCase, myObject, someFunction, etc. But CamelCase, or any uppercase in general, is not recommended for table and column names in SQL. Why, you wonder? Well, SQL databases tend to convert all identifiers (table/column names) to lowercase unless you wrap them in quotes. So even though you create a table called Authors
, it'll be made as authors
. Then, if you somehow end up moving your database to a server where the SQL system is compiled to be case-sensitive, and your code has Authors
everywhere, it won't work anymore. Additionally, exporting your database scheme and moving it from Windows to Linux using certain tools comes at the risk that your names will all be lowercased along the way, which will - again - make your code (with CamelCase in it) stop working. Or imagine you have a column called authorId. The capital I for id can easily look like a lowercase L in many fonts, adding to the risk of confusing other people who have to work with your code. There are just too many small risks involved in using CamelCase, so try to avoid it and stick to lowercase.
Table names in column names
Certain conventions dictate that you use {tablename}_{columnname}
, ie. table author
would have author_name
, author_birthday
and so forth. Our suggestion: don't do that. A table is a collection of columns that belong in it, and a column name is exactly what it says: a column name; not a "column identifier with a table identifier in it as well". Stick to columnname
; not only is it much shorter, it also does not require underscores nor does it clutter up queries unnecessarily. For instance, compare these two queries:
- SELECT post_title AS title, post_content AS content, post_dateline AS dateline, post_author_id AS author_id FROM post;
- SELECT title, content, dateline, authorid FROM post;
Which is easier to read? The second, as it keeps all data clean and easy to scan.
Furthermore, there are other reasons to stay away from using table names in column names (besides being illogical in their presence): what would happen if you had a table called post
that would, later on in development of the application, start to conflict with something else? What if you had to rename it to entry
? You'd end up with table entry
and a bunch of columns that are prefixed with post_
. Now you'll be stuck with highly inconsistent naming, or you have to go through all of your code and not only update the table name, but all of your column names as well! That can easily become quite tedious, as you can probably imagine.
And that's still not the end of it! Aside of being illogical, cluttersome, space-consuming and hardly future-proof, they also make it harder to join tables in queries. You can say that in a way it makes it easier, because you'll never have to worry about ambiguity in column names, but you're removing the use of USING() and you're making JOIN queries look very complicated, even when they're not. What's worse, some people make queries such as this:
SELECT post_title AS title, post_dateline AS dateline, author_name AS name FROM post LEFT JOIN author ON (post_author_id = author_id)
Why is that so bad?
SELECT title, dateline, name FROM post LEFT JOIN author USING (authorid)
…is simply much cleaner and easier to scan.
And to top it all off, using table names in your column names will only make matters highly confusing once you start having columns that reference columns on other tables. You'll end up with a column like this: post_link_id
. That's 2 table names in a single column name. Not exaclty desirable, I would say.
There is one example where this is justifiable, see ID Columns below.
Avoid reserved keywords!
Tools such as phpMyAdmin allow you to name your tables and columns whatever you want, even when the names you choose are actually reserved keywords. That's because MySQL allows you to create such tables, but requires them enclosed in backticks (`). Using reserved keywords is very risky; you'll be forced to always use backticks (this is MySQL-specific, by the way) and if you forget them anywhere at all, you'll run into problems. Additionally, if your dump of the database(s) or table(s) is made without backticks, you'll have to manually add them because otherwise restoring the dump won't work. Be smart; just avoid using them at all times. They're never a necessity.
ID columns
Also known as auto_increment
or serial
columns, these are usually the very first column on any table and are almost always the Primary Key column of the table. For ID columns, you can choose to name them all id
or, what we adopted for the Media Design CMS, {tablename}id
(avoiding underscores). Having been completely consistent in the design of the database and all of its tables, we've been able to simplify a lot of functions and methods around this, making our life much easier. Whenever we need a serial (PostgreSQL's version of the ID column), we know it's found at tablename + 'id'. This makes referencing, joining and simply querying a lot easier. Additionally, by going for a tablename + 'id' instead of just 'id' we can always see from every serial and in every JOIN
query what table it is for. Doing this for all columns, though, is overkill.
That sums up the rough convention used for the database scheme of our CMS. It may not be the "end-all, be-all" convention, but it's logical, consistent (providing you accept the good reasons to break one consistency rule) and works very nicely.
发表评论
-
ORACLE中的锁
2013-04-13 02:14 0(转载)ORACLE里锁有以下几种模式:0:none1:nul ... -
避免全表扫描
2010-04-26 11:10 1003转: http://www.cnblogs.com/jackl ... -
mysql到oracle的移植问题
2009-11-09 22:50 0最近折腾一个系统时发现从Mysql数据库移植到Oracle时, ... -
用oracle发送邮件(功能很全)
2009-04-10 17:39 1951转自: http://www.itpub.net/732719 ... -
Oracle级联修改通用过程
2009-04-10 17:38 1225转自http://blog.itpub.net/post/16 ... -
移植数据库时的几点发现
2009-04-10 17:09 1257a. SQL Server与Oracle的两点区别 ... -
用PL/SQL查看SQL语句执行计划
2008-12-10 16:51 10340一般通过很多工具可以看PL/SQL的执行计划来分析语句性能。 ... -
《oracle SQL性能优化》中文电子书
2008-12-09 23:14 4339《oracle SQL性能优化系列》是我接触最早的一本有关or ... -
LINUX下MYSQL完全安装使用指南
2008-12-09 15:17 0http://www.35dx.com/html/200601 ... -
oracle中锁的问题
2008-12-08 23:38 1821前两天同事又碰到oracle连接n慢的问题,由于这个现象时 ... -
oracle锁的问题
2008-12-05 13:38 0select s.PROCESS, s.SID, ... -
Oracle中查找和删除重复记录方法简介
2008-12-04 11:21 1186收藏的共享下,蛮不错的. 作者:bensiyuan 转 ... -
Linux下安装MySql5.0
2008-11-28 16:58 1911转自http://www.blogjava.net/huang ...
相关推荐
The Naming Convention for Clock Tree Optimization Repeaters Description Question: When clock tree synthesis or clock tree optimization is run, a lot of buffers with the cto_st* naming convention are ...
IPC-7351指导硬件开发中PCB封装库的相关设计,根据不同的封装类型定义不同的焊盘形式,以便实现可...附件中资源为Library Expert Land Pattern Naming Convention,与IPC-7351B相比有很大提升,命名规则更加贴近使用。
check-file-naming-convention插件 用法 首先,安装gulp-check-file-naming-convention作为开发依赖项: npm install --save-dev gulp-check-file-naming-convention 然后,将其添加到您的gulpfile.js : var ...
genicam_standard_features_naming_convention.pdf genicam 标准文件 详细说明
npm install naming-convention-transfer 浏览器(iife) 请此文件。 并在您的文档中嵌入脚本标签。 < script src =" ./dist/naming-convention-transfer.js " > </ script > 例子 Nodejs的 import { ...
赠送jar包:nacos-naming-2.0.4.RELEASE.jar; 赠送原API文档:nacos-naming-2.0.4.RELEASE-javadoc.jar; 赠送源代码:nacos-naming-2.0.4.RELEASE-sources.jar; 赠送Maven依赖信息文件:nacos-naming-2.0.4....
naming-common.jar
Why is proper column naming so important?Nulls in the database cause wrong answers. Why? What you can do about it?How can image relations help you formulate complex SQL queries?SQL supports ...
naming-factory.jar
Why is proper column naming so important? Nulls in the database cause wrong answers. Why? What you can do about it? How can image relations help you formulate complex SQL queries? SQL supports ...
naming-resources.jar
赠送jar包:nacos-naming-2.0.4.RELEASE.jar; 赠送原API文档:nacos-naming-2.0.4.RELEASE-javadoc.jar; 赠送源代码:nacos-naming-2.0.4.RELEASE-sources.jar; 赠送Maven依赖信息文件:nacos-naming-2.0.4....
javax.naming.NamingException: Cannot create resource instance类加载异常,希望可以帮助跟我一样错误的人。
util-naming-2.0.1-450.jarutil-naming-2.0.1-450.jarutil-naming-2.0.1-450.jarutil-naming-2.0.1-450.jarutil-naming-2.0.1-450.jarutil-naming-2.0.1-450.jarutil-naming-2.0.1-450.jarutil-naming-2.0.1-450....
随机网络中Naming Game的作用规则,宋倩倩,郑志明,Naming Game(NG)在语言动力学中有着广泛的应用,它研究了智能主体基于局部的相互作用,通过自组织演化最终达到全局共识的一个过程��
DMW-命名-公约-项目我们下载了大约 11,000 个 PDF 文件,其中包含古吉拉特邦人民的选举数据。 然后使用一些很酷的 PDFBox(Java 库)功能,我们将所有这些 PDF 转换为文本文件并创建一个大型数据库来查询一些有趣的...
With naming an easily reversable act, the naming system of a program is more fluid than at any other point in software history. This is a good thing. Now there is no need to leave poor names in place...
naming-factory-dbcp.jar
首先配置Tomcat服务器文件 ... <Resource name="jdbc/jndidemo" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="sa" password="1" driverClassName=...