Re: Table/Column Definition Question

Jim Smith wrote:
>
> 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!
>

A lot depends on the GL code. If you can define fields and they are indeed consistant, go for individual fields. (My experience is that a GL layout is never consistant.)

An alternative ... define a 20-40 char field as the GL code to be used everywhere. Then define a separate table with the GL code as the primary key and have supporting 'properly named' columns in that table that can be used for the breakdown. This will provide a lot of flexibility at the expense of performance when you have to join.

Another alternative ... do it via program using structures, records, or whatever your 3GL uses.

Personal preference ... don't use numerics. Too many companies define GL as numeric and then throw in exceptions. (eg: pos 7-10 is the department 'number' & then use "2G23"). Many, many COBOL workarounds have been created to accomodate this nonsense.

Jim made a couple of valid points, esp. about performance.

/Hans Received on Mon Oct 07 1996 - 00:00:00 CEST