關於一個資料庫列設計的問題

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

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.