separate columns or separate table?

Consider a flat file formatted according to the following Cobol copybook:

05 TRFL-DATE PIC 9(9) COMP-3.
05 TRFL-TIME PIC 9(5) COMP-3.
05 TRFL-CALLING-CUST PIC 9(9) COMP-3.
05 TRFL-BILLING-BR PIC 9(3) COMP-3.
05 TRFL-BILLING-ACCT-BR PIC 9(3) COMP-3.
05 TRFL-BILLING-ACCT-NBR PIC 9(9) COMP-3.
05 TRFL-BILLING-ACCT-TYPE PIC X.
05 TRFL-WAIVE-ACCT-CHG-IND PIC X.
05 TRFL-MULTI-ACCT-CALL-IND PIC X.
05 TRFL-MULTI-ACCT-ORIG-DATE PIC 9(9) COMP-3.
05 TRFL-MULTI-ACCT-ORIG-TIME PIC 9(5) COMP-3.
05 TRFL-COMMENTS PIC X(54).
05 TRFL-SERVICES-INFO.
10 TRFL-SVC-OCC OCCURS 32 TIMES.
15 TRFL-SVC-SEL PIC X.
05 TRFL-OPERATOR PIC X(3).

TRLF-SERVICES-INFO is a "table" (or array, if you prefer) that has 32 one
character occurances.

This is a "customer call tracking" file. For each call made, the CSR can
chose between 1 and 32 reasons why the customer called; which bank services
they called about or utilized (like, they wanted their account balance;
wanted to order new checks; wanted to make a transfer between accounts;
etc.).

I would like to replace the use of this file with the use of an RDBMS (DB2,
specifically). Most of it is straight-forward. It's the "services table"
that is getting me. My first thought is that there would be a SERVICES
table that is a "child" of the CALLS table. Each call will have zero or
more services related to it. So this is what I have:

CREATE TABLE CALLTRAK.SERVICE_CODES (
CODE CHAR(1) NOT NULL
, DESCRIPTION VARCHAR(50) NOT NULL
, CONSTRAINT SERVICE_CODES_PK PRIMARY KEY (CODE)
)

CREATE TABLE CALLTRAK.CALLS (
CALL_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
, DATE DATE NOT NULL
WITH DEFAULT
, TIME TIME NOT NULL
WITH DEFAULT
, CUST_NBR DECIMAL(9) NOT NULL
, BILL_BRCH DECIMAL(3) NOT NULL
, BRCH_NBR DECIMAL(3) NOT NULL
, ACCT_NBR DECIMAL(10) NOT NULL
, ACCT_TYPE CHAR(1) NOT NULL
, WAIVE_CHG_YN CHAR(1) NOT NULL
, MULTI_ACCT_CALL_IND CHAR(1) NOT NULL
, MULTI_ACCT_ORIG_DATETIME TIMESTAMP
, COMMENTS VARCHAR(54) NOT NULL

, BILL_ACCT_TYP CHAR(1) NOT NULL

, OPERATOR CHAR(3) NOT NULL

, CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)

, CONSTRAINT ACCT_TYPE CHECK(ACCT_TYPE IN ('D','S','L',' '))

, CONSTRAINT WAIVE_CHG_YN CHECK(WAIVE_CHG_YN IN ('Y','N'))

, CONSTRAINT MULTI_ACCT_CALL_IND CHECK(MULTI_ACCT_CALL_IND

IN ('N','B','C','E'))

)



CREATE TABLE CALLTRAK.SERVICES (

SERVICES_ID INTEGER NOT NULL

GENERATED ALWAYS AS IDENTITY

, CALL_ID INTEGER NOT NULL

, SERVICE_CODE CHAR(1) NOT NULL

, CONSTRAINT SERVICES_PK PRIMARY KEY (SERVICES_ID)

, CONSTRAINT CALL_ID_FK FOREIGN KEY (CALL_ID)

REFERENCES CALLTRAK.CALLS (CALL_ID)

ON DELETE CASCADE

, CONSTRAINT SERVICE_CODE_FK FOREIGN KEY (SERVICE_CODE)

REFERENCES CALLTRAK.SERVICE_CODES
(CODE)
, CONSTRAINT SERVICES_UK1 UNIQUE (CALL_ID, SERVICE_CODE)
)

This all seems pretty reasonable, with one exception. As far as I can tell,
for each call I have to do a single insert into the CALLS table followed by
1 to 32 inserts in to the SERVICES table. This, umm, kind of bugs me. I'm
wanting to do a single insert. Maybe that's not rational, but its my
inclination.

If I were to add to the CALLS table 32 columns, named after the service type
(BALANCE_INQ_YN, TELEPHONE_TRANSFER_YN, CHECK_ORDER_YN, etc.) then I could
do a single insert. But this doesn't feel quite right. I also have a
thought of adding a field to the SERVICE_CODES table that indicates whether
or not a service "counts against" a customer or not. By that I mean that if
a customer makes more than a certain number of calls for certain reasons we
will bill that customer for those calls. (Yes, we are a bank; but we're
still pretty reasonable!) If I used the "named columns" method then I don't
think I'd even be able to utilize the SERVICE_CODES table. With the
SERVICES in a separate table I imagine I'd be able to say something like:

SELECT ...
FROM CALLTRAK.CALLS
WHERE CALL_ID IN (
SELECT CALL_ID FROM CALLTRAK.SERVICES
WHERE SERVICE_CODE IN (
SELECT CODE FROM CALLTRAK.SERVICE_CODES
WHERE BILLABLE = 'Y'));

This would give me all of the calls that had billable services.

Anyway, I am on the right track? Should I just "get over" my dislike of the
multiple inserts thing? Is there a third, better, direction I should be
going?

Thanks,
Frank

Frank,

> If I were to add to the CALLS table 32 columns, named after the service type(BALANCE_INQ_YN, TELEPHONE_TRANSFER_YN, CHECK_ORDER_YN, etc.) then I could do a single insert. *But this doesn't feel quite right. *<

I am having the same feeling and would go the second way you
suggested, i. e. create a "Used_Services" Table. The insert might be
easier with your 32 boolean columns, but any reporting and SELECT
statement will become very difficult or be impossible (as you
correctly stated already).

brgds

Philipp Post

On Aug 22, 1:26*pm, "Frank Swarbrick"
wrote:
> Consider a flat file formatted according to the following Cobol copybook:
>
> 05 *TRFL-DATE * * * * * * * PIC 9(9) *COMP-3.
> 05 *TRFL-TIME * * * * * * * PIC 9(5) *COMP-3.
> 05 *TRFL-CALLING-CUST * * * PIC 9(9) *COMP-3.
> 05 *TRFL-BILLING-BR * * * * PIC 9(3) *COMP-3.
> 05 *TRFL-BILLING-ACCT-BR * *PIC 9(3) *COMP-3.
> 05 *TRFL-BILLING-ACCT-NBR * PIC 9(9) *COMP-3.
> 05 *TRFL-BILLING-ACCT-TYPE *PIC X.
> 05 *TRFL-WAIVE-ACCT-CHG-IND PIC X.
> 05 *TRFL-MULTI-ACCT-CALL-IND PIC X.
> 05 *TRFL-MULTI-ACCT-ORIG-DATE PIC 9(9) COMP-3. * * *
> 05 *TRFL-MULTI-ACCT-ORIG-TIME PIC 9(5) COMP-3. * * *
> 05 *TRFL-COMMENTS * * * * * PIC X(54). * * * * * * *
> 05 *TRFL-SERVICES-INFO. * * * * * * * * * * * ** *
> * * 10 *TRFL-SVC-OCC OCCURS 32 TIMES. * * * * * * *
> * * * * 15 *TRFL-SVC-SEL * PIC X. * * * * * * ** *
> 05 *TRFL-OPERATOR * * * * * PIC X(3).
>
> TRLF-SERVICES-INFO is a "table" (or array, if you prefer) that has 32 one
> character occurances.
>
> This is a "customer call tracking" file. *For each call made, the CSR can
> chose between 1 and 32 reasons why the customer called; which bank services
> they called about or utilized (like, they wanted their account balance;
> wanted to order new checks; wanted to make a transfer between accounts;
> etc.).

so each service is performed AT MOST once per call? looks like it
based on the COBOL

>
> I would like to replace the use of this file with the use of an RDBMS (DB2,
> specifically). *Most of it is straight-forward. *It's the "services table"
> that is getting me. *My first thought is that there would be a SERVICES
> table that is a "child" of the CALLS table. *Each call will have zero or
> more services related to it. *So this is what I have:
>
> CREATE TABLE CALLTRAK.SERVICE_CODES ( * * * * * * * * ** * * *
> * *CODE * * * * * * * * CHAR(1) * * NOT NULL * * * * * * * * * *
> *, DESCRIPTION * * * * *VARCHAR(50) NOT NULL * * * * * * * * * *
> *, CONSTRAINT * *SERVICE_CODES_PK * PRIMARY KEY (CODE) * * * * *
> ) * * * * * * * * * * * * * * * * * ** * * * * * * * * * * * *
>
> CREATE TABLE CALLTRAK.CALLS ( * * * * * * * * * * ** * * * * *
> * * CALL_ID * * * * * * INTEGER * * NOT NULL * * * * * * * * * *
> * * * * * * * * * * * * * * * * * * GENERATED ALWAYS AS IDENTITY
> * , DATE * * * * * * * *DATE * * * *NOT NULL * * * * * * * * * *
> * * * * * * * * * * * * * * * * * * WITH DEFAULT * * * * * * * *
> * , TIME * * * * * * * *TIME * * * *NOT NULL * * * * * * * * * *
> * * * * * * * * * * * * * * * * * * WITH DEFAULT * * * * * * * *
> * , CUST_NBR * * * * * *DECIMAL(9) *NOT NULL * * * * * * * * * *
> * , BILL_BRCH * * * * * DECIMAL(3) *NOT NULL * * * * * * * * * *
> * , BRCH_NBR * * * * * *DECIMAL(3) *NOT NULL * * * * * * * * * *
> * , ACCT_NBR * * * * * *DECIMAL(10) NOT NULL * * * * * * * * * *
> * , ACCT_TYPE * * * * * CHAR(1) * * NOT NULL * * * * * * * * * *
> * , WAIVE_CHG_YN * * * *CHAR(1) * * NOT NULL * * * * * * * * * *
> * , MULTI_ACCT_CALL_IND CHAR(1) * * NOT NULL * * * * * * * * * *
> * , MULTI_ACCT_ORIG_DATETIME *TIMESTAMP * * * * * * * * * * * *
> * , COMMENTS * * * * * *VARCHAR(54) NOT NULL * * * * * * * * * * * * * * *
>
> * , BILL_ACCT_TYP * * * CHAR(1) * * NOT NULL * * * * * * * * * * * * * * *
>
> * , OPERATOR * * * * * *CHAR(3) * * NOT NULL * * * * * * * * * * * * * * *
>
> * , CONSTRAINT * *CALLS_PK * * * * *PRIMARY KEY (CALL_ID)* * * * * * * * *
>
> * , CONSTRAINT * *ACCT_TYPE * * * * CHECK(ACCT_TYPE IN ('D','S','L',' ')) *
>
> * , CONSTRAINT * *WAIVE_CHG_YN * * *CHECK(WAIVE_CHG_YN IN ('Y','N')) * * *
>
> * , CONSTRAINT *MULTI_ACCT_CALL_IND CHECK(MULTI_ACCT_CALL_IND * ** * * * *
>
> * * * * * * * * * * * * * * * * * * * * * IN ('N','B','C','E')) * * * * * *
>
> ) * * * * * * * * * * * * * * * * * ** * * * * * * * * * * * * * * * * * *
>
> CREATE TABLE CALLTRAK.SERVICES ( * * * * * * * * * * * * * * * * * * * * *
>
> * * SERVICES_ID * * * * INTEGER * * NOT NULL * * * * * * * * * * * * * * *
>
> * * * * * * * * * * * * * * * * * * GENERATED ALWAYS AS IDENTITY * * *

why the ID?? you don't need it!* *
>
> * , CALL_ID * * * * * * INTEGER * * NOT NULL * * * * * * * * * * * * * * *
>
> * , SERVICE_CODE * * * *CHAR(1) * * NOT NULL * * * * * * * * * * * * * * *
>
> * , CONSTRAINT *SERVICES_PK * * * * PRIMARY KEY (SERVICES_ID)* * * * * * *
>
> * , CONSTRAINT *CALL_ID_FK * * * * *FOREIGN KEY (CALL_ID) * * * * * * * * *
>
> * * * * * * * * * * * * * * * * * * REFERENCES CALLTRAK.CALLS (CALL_ID) * *
>
> * * * * * * * * * * * * * * * * * * ON DELETE CASCADE * * * * * * * * * * *
>
> * , CONSTRAINT *SERVICE_CODE_FK * * FOREIGN KEY (SERVICE_CODE) * * * * * *
>
> * * * * * * * * * * * * * * * * * * REFERENCES CALLTRAK.SERVICE_CODES
> (CODE)
> * , CONSTRAINT *SERVICES_UK1 * * * *UNIQUE (CALL_ID, SERVICE_CODE)
> )

The PK of this table should be (CALL_ID, SERVICE_CODE) Why add the
indirect ID column

>
> This all seems pretty reasonable, with one exception. *As far as I can tell,
> for each call I have to do a single insert into the CALLS table followed by
> 1 to 32 inserts in to the SERVICES table. *This, umm, kind of bugs me. *I'm
> wanting to do a single insert. *Maybe that's not rational, but its my
> inclination.

Can you create a view with an INSTEAD OF trigger? (You failed to
mention WHICH DBMS you use. I only know it isn't Oracle.)

In your application level code, the services are just in an array,
right? So codewise it is just one INSERT inside a loop.

Your inclination is right, just the code structure needs to match your
inclination. As services are picked, shove them into a list (or array
or other loopable structure).

>
> [snip] *If I used the "named columns" method then I don't
> think I'd even be able to utilize the SERVICE_CODES table. *With the
> SERVICES in a separate table I imagine I'd be able to say something like:
>
> SELECT ...
> FROM CALLTRAK.CALLS
> WHERE CALL_ID IN (
> * * SELECT CALL_ID FROM CALLTRAK.SERVICES
> * * WHERE SERVICE_CODE IN (
> * * * * SELECT CODE FROM CALLTRAK.SERVICE_CODES
> * * * * WHERE BILLABLE = 'Y'));
>
> This would give me all of the calls that had billable services.

Yes.

>
> Anyway, I am on the right track? *Should I just "get over" my dislike of the
> multiple inserts thing? *Is there a third, better, direction I should be
> going?
>
> Thanks,
> Frank

It really shouldn't be multiple inserts, so...

....just get over it. 8^)

Ed

Hello.

I should say sorry, if I get it wrong, but what about bit fields then?

You could store IDs in SERVICE_CODES table as a powers of 2 and then
store and retrieve them from SERVICES table using BITAND operation?

Not sure how this compares with relational databases theory, but it
just struck me as an idea...


--
Hope this helps,
JB

>>> On 8/27/2008 at 10:01 AM, in message
<7b5644e4-96a2-4fa2-8b89-440eb9c138b1@c58g2000hsc.googlegroups.com>, Ed
Prochak wrote:
> On Aug 22, 1:26 pm, "Frank Swarbrick"
> wrote:
>> Consider a flat file formatted according to the following Cobol
> copybook:
>>
>> 05 TRFL-DATE PIC 9(9) COMP-3.
>> 05 TRFL-TIME PIC 9(5) COMP-3.
>> 05 TRFL-CALLING-CUST PIC 9(9) COMP-3.
>> 05 TRFL-BILLING-BR PIC 9(3) COMP-3.
>> 05 TRFL-BILLING-ACCT-BR PIC 9(3) COMP-3.
>> 05 TRFL-BILLING-ACCT-NBR PIC 9(9) COMP-3.
>> 05 TRFL-BILLING-ACCT-TYPE PIC X.
>> 05 TRFL-WAIVE-ACCT-CHG-IND PIC X.
>> 05 TRFL-MULTI-ACCT-CALL-IND PIC X.
>> 05 TRFL-MULTI-ACCT-ORIG-DATE PIC 9(9) COMP-3.
>> 05 TRFL-MULTI-ACCT-ORIG-TIME PIC 9(5) COMP-3.
>> 05 TRFL-COMMENTS PIC X(54).
>> 05 TRFL-SERVICES-INFO.
>> 10 TRFL-SVC-OCC OCCURS 32 TIMES.
>> 15 TRFL-SVC-SEL PIC X.
>> 05 TRFL-OPERATOR PIC X(3).
>>
>> TRLF-SERVICES-INFO is a "table" (or array, if you prefer) that has 32
one
>> character occurances.
>>
>> This is a "customer call tracking" file. For each call made, the CSR
> can
>> chose between 1 and 32 reasons why the customer called; which bank
> services
>> they called about or utilized (like, they wanted their account balance;
>> wanted to order new checks; wanted to make a transfer between accounts;
>> etc.).
>
> so each service is performed AT MOST once per call? looks like it
> based on the COBOL

I wouldn't say that each services is *performed* at most once per call.
Only that each services is *recorded* at most once per call. :-)

Actually, that isn't quite correct. The table is actually what we call
"call segments", where a "call" can consist of more than one call segment,
where different segments within the same call can pertain to the same
customer but different customer accounts. Or something like that! :-)

Probably I should rename this table. I didn't understand the 'call segment'
idea at the time I created the table.


>> I would like to replace the use of this file with the use of an RDBMS
> (DB2,
>> specifically). Most of it is straight-forward. It's the "services
> table"
>> that is getting me. My first thought is that there would be a SERVICES
>> table that is a "child" of the CALLS table. Each call will have zero or
>> more services related to it. So this is what I have:
>>
>> CREATE TABLE CALLTRAK.SERVICE_CODES (
>> CODE CHAR(1) NOT NULL
>> , DESCRIPTION VARCHAR(50) NOT NULL
>> , CONSTRAINT SERVICE_CODES_PK PRIMARY KEY (CODE)
>> )
>>
>> CREATE TABLE CALLTRAK.CALLS (
>> CALL_ID INTEGER NOT NULL
>> GENERATED ALWAYS AS IDENTITY
>> , DATE DATE NOT NULL
>> WITH DEFAULT
>> , TIME TIME NOT NULL
>> WITH DEFAULT
>> , CUST_NBR DECIMAL(9) NOT NULL
>> , BILL_BRCH DECIMAL(3) NOT NULL
>> , BRCH_NBR DECIMAL(3) NOT NULL
>> , ACCT_NBR DECIMAL(10) NOT NULL
>> , ACCT_TYPE CHAR(1) NOT NULL
>> , WAIVE_CHG_YN CHAR(1) NOT NULL
>> , MULTI_ACCT_CALL_IND CHAR(1) NOT NULL
>> , MULTI_ACCT_ORIG_DATETIME TIMESTAMP
>> , COMMENTS VARCHAR(54) NOT NULL

>
>>
>> , BILL_ACCT_TYP CHAR(1) NOT NULL

>
>>
>> , OPERATOR CHAR(3) NOT NULL

>
>>
>> , CONSTRAINT CALLS_PK PRIMARY KEY (CALL_ID)

>
>>
>> , CONSTRAINT ACCT_TYPE CHECK(ACCT_TYPE IN ('D','S','L','
> '))
>>
>> , CONSTRAINT WAIVE_CHG_YN CHECK(WAIVE_CHG_YN IN ('Y','N'))

>
>>
>> , CONSTRAINT MULTI_ACCT_CALL_IND CHECK(MULTI_ACCT_CALL_IND

>
>>
>> IN ('N','B','C','E'))

>
>>
>> )

>
>>
>> CREATE TABLE CALLTRAK.SERVICES (

>
>>
>> SERVICES_ID INTEGER NOT NULL

>
>>
>> GENERATED ALWAYS AS IDENTITY
>
> why the ID?? you don't need it!

Bad habit? :-)

>>
>> , CALL_ID INTEGER NOT NULL

>
>>
>> , SERVICE_CODE CHAR(1) NOT NULL

>
>>
>> , CONSTRAINT SERVICES_PK PRIMARY KEY (SERVICES_ID)

>
>>
>> , CONSTRAINT CALL_ID_FK FOREIGN KEY (CALL_ID)

>
>>
>> REFERENCES CALLTRAK.CALLS (CALL_ID)

>
>>
>> ON DELETE CASCADE

>
>>
>> , CONSTRAINT SERVICE_CODE_FK FOREIGN KEY (SERVICE_CODE)

>
>>
>> REFERENCES CALLTRAK.SERVICE_CODES
>> (CODE)
>> , CONSTRAINT SERVICES_UK1 UNIQUE (CALL_ID, SERVICE_CODE)
>> )
>
> The PK of this table should be (CALL_ID, SERVICE_CODE) Why add the
> indirect ID column

Does this table really need a PK at all? Once a "call segment" has been
added it can no longer be altered in any way (except perhaps being deleted
when it's really old).

Actually, I guess it make sense to have a PK if only to enforce uniqueness
(instead of the SERVICES_UK1 constraint).

>> This all seems pretty reasonable, with one exception. As far as I can
> tell,
>> for each call I have to do a single insert into the CALLS table followed

> by
>> 1 to 32 inserts in to the SERVICES table. This, umm, kind of bugs me.
> I'm
>> wanting to do a single insert. Maybe that's not rational, but its my
>> inclination.
>
> Can you create a view with an INSTEAD OF trigger? (You failed to
> mention WHICH DBMS you use. I only know it isn't Oracle.)

DB2/LUW 9.5. INSTEAD OF triggers are available, but I am not understanding
how one would be utilized here.

> In your application level code, the services are just in an array,
> right? So codewise it is just one INSERT inside a loop.

Yes. I didn't mean to imply I'd have 32 individual INSERT statements. Just
that the single INSERT would be executed from 1 to 32 times.

> Your inclination is right, just the code structure needs to match your
> inclination. As services are picked, shove them into a list (or array
> or other loopable structure).
>
>>
>> [snip] If I used the "named columns" method then I don't
>> think I'd even be able to utilize the SERVICE_CODES table. With the
>> SERVICES in a separate table I imagine I'd be able to say something
> like:
>>
>> SELECT ...
>> FROM CALLTRAK.CALLS
>> WHERE CALL_ID IN (
>> SELECT CALL_ID FROM CALLTRAK.SERVICES
>> WHERE SERVICE_CODE IN (
>> SELECT CODE FROM CALLTRAK.SERVICE_CODES
>> WHERE BILLABLE = 'Y'));
>>
>> This would give me all of the calls that had billable services.
>
> Yes.
>
>>
>> Anyway, I am on the right track? Should I just "get over" my dislike of

> the
>> multiple inserts thing? Is there a third, better, direction I should be
>> going?
>>
>> Thanks,
>> Frank
>
> It really shouldn't be multiple inserts, so...
>
> ...just get over it. 8^)

To me it is still multiple inserts, unless I am misunderstanding what you
are saying.

In DB2 for z/OS it looks like I could do something like this:

01 HOST-ARRAYS.
05 TCS-ID-ARRAY PIC S9(8) COMP OCCURS 32.
05 TCS-SERVICE-CODE-ARRAY PIC X OCCURS 32.
77 NBR-OF-SERVICES PIC S9(4) COMP.

EXEC SQL
INSERT INTO CALLTRAK.SERVICES (
CALL_ID
, SERVICE_CODE
)
VALUES (
:TCS-ID-ARRAY
, :TCS-SERVICE-CODE-ARRAY
)
FOR :NBR-OF-SERVICES TIMES
ATOMIC
END-EXEC

This would be ideal, but unfortunately it is not available on the LUW
platform.

Thanks for your thoughts.
Frank