关于一个数据库列设计的问题

关于一个数据库列设计的问题(from askTom)[@more@]

关于一个数据库列设计的问题

From askTom

在ASKTOM上有一个问题是：

Hi Tom, I am in a situation to define a primary key values. My primary value contains data in the following format.. 100020051225000001 Here the first 4 digits represent site id (1000) Second set of 8 digits represent Date in YYYYMMDD (20051225) the last 6 digits represent transaction # (0000001) This is my requirement. Now I have a question 1.Which data type is best suitable ? Varchar2 or Number ? I heard that number search is fast when compared to character. But if I define number, it takes 21 bytes where as if I define char it takes 18 bytes. [Looks char takes less than number] 2.In fact I would like to define it as follows with numeric data type 100020051225.000001 In this case, the transaction id is the value after decimal. If I define the column as NUMBER and data as said above, what are disadvantages and advantages? The reason why I am looking for this choice is that, in future if the transaction value goes beyond 6 digits or site id goes beyond 4 digits, I donot need to worry. But I need to look for the space and performance relates issues. I feel you are the best person for this to answer and I am sure you are the right choice :) Please brief the following considerations in this case. 1.Space 2.Performance What is your best suggestion? -Naveen

意思就是说，有一个表上的关键字需要有三部分组成：site id，日期和事务号，问题的提出者想问的是，如何设计这个关键字为字符型或者是数值型能够达到最优的效果，当然他从存放的空间多少和性能上考虑这个问题。

Tom给出的解决方案是这样的：

One would say "why isn't this three fields as it should be?" That isn't a requirement you've specified, that is a design decision you have made that you *might* well want to reconsider. Site_id - number transaction_date DATE <<== it is after all a DATE!!!! transaction_id number Forget space and everything, this is about data. I can imagine wanting to do things like partition by site.... partition or subpartition by date.... And all of your concerns by transaction id going beyond 6 digits - not a problem. I will be very repetitive in my followups to this. You want three attributes. They should be a number, date, number Never put multiple attributes into a single column.

就是说将这个关键字拆开来设计，最后一句话很重要，不能将多个属性的数据放置到一列上。

从以前我经理的项目上看到很多这样的情况，由于设计的问题给以后性能上产生了很大的影响， Tom 说的这种方式我认为很好，给以后的系统的优化， SQL 的书写，索引的建立等提供很多的方便。同时引申一下，在以前接触的项目中有的项目为了省心或者方便，将一个表设计很复杂，一个表有几十列，甚至是上百列，而且很多业务同时使用这个表上的列，这样就造成了，这个系统的优化的极为复杂性。希望以后引以为戒。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/222350/viewspace-909444/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/222350/viewspace-909444/