Re: Table/Column Definition Question

In article <32563d75.9268319_at_n5.gbso.net>, Chuck Hamilton <chuckh_at_dvol.com> writes
>BEH_at_csd.uwm.edu (Barbara Hiles) wrote:
>
>>Can anyone give me some feel for the best way to define this stuff to Oracle?
>>Specifically, I am struggling with the level of detail. For example (1 of
>>many) I have a general ledger number of 20. Made up of 8 parts. or 9 if I go
>>down to subdept. So do I define one column of 20 char or 8 pieces? What are
>>the pros and cons I should be considering to make this decision?
>>
>>If I use views to 'redefine' the look - then if I use a function in the view
>>(to string columns together maybe) then I can't update using the view? Right?
>
>How are existing applications written? Do they expect one field or
>many? If you're going to be using existing programs for some time then
>that's ultimately going to end up driving the decision.
>
>Ideally I would break it up into 9 columns. I don't see any benefit to
>storing it in one. It's going to take up the same amount of storage
>either way. Either the application has to add a delimiter between
>columns, or the database adds a length byte internally. Six in one,
>half dozen in the other. Having separate columns though makes it
>easier to get at the individual fields.
>--
>Chuck Hamilton
>chuckh_at_dvol.com
>
>This message delivered by electronic sled dogs. WOOF!

You should define the table structures in the most flexible way possible, which *probably* means 9 columns rather than 1, and let COBOL take care fo redefinitions. It is much more flexible than Oracle for that sort of thing.

The flexibility of COBOL in manipulation of data can lead to data design deecision which do not fit well with relational databases. For example, is your general ledger number really a number or is it a composite key?

If it is a composite key, and you need to do queries or updates based on parts of that key then in Oracle these parts probably need to be indexed separately, which means separate columns. These separate columns can be redefined as a single column for those programs that need it.

Example (excuse the syntax, its a long time since I used COBOL)

Assuming a record definition something like this.

01 Ledger-entry

   05 GL-NUMBER pic 9(20)
   05 gl-number-bits redefines gl-number

       10 gl-region-number pic 9(5)
       10 gl-area-number pic 9(5)
      etc

Then do this

(create table ledger_entry

   gl_region_number number(5)
   gl_area_number number(5)
   etc)

select gl_region_number, gl_area_number
into :gl-region-number,:gl-area_number from ledger_entry

rather than

(create table ledger_entry

    gl_number number(20)
  etc)

select gl_Number
into :gl-number
from ledger_entry

Although the lattter is a more naturally cobol way to do it.

It is important you get your physical database design as moving from IMS to Oracle is likely to hit performance drastically.

-- 
Jim Smith