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