separate columns or separate table?

#1

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

#2

Frank,

Quote:

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

#3

Frank,

Quote:

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

#4

Frank,

Quote:

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

#5

On Aug 22, 1:26*pm, "Frank Swarbrick" <Frank.Swarbr... (AT) efirstbank (DOT) com>
wrote:

Quote:

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

Quote:

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!* *

Quote:

* , 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

Quote:

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).

Quote:

[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.

Quote:

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

#6

On Aug 22, 1:26*pm, "Frank Swarbrick" <Frank.Swarbr... (AT) efirstbank (DOT) com>
wrote:

Quote:

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

Quote:

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!* *

Quote:

* , 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

Quote:

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).

Quote:

[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.

Quote:

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

#7

On Aug 22, 1:26*pm, "Frank Swarbrick" <Frank.Swarbr... (AT) efirstbank (DOT) com>
wrote:

Quote:

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

Quote:

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!* *

Quote:

* , 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

Quote:

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).

Quote:

[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.

Quote:

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

#8

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

#9

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

#10

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