separate columns or separate table?

#11

Quote:

On 8/27/2008 at 10:01 AM, in message
7b5644e4-96a2-4fa2-8b89-440eb9c138b1...oglegroups.com>, Ed

Prochak<edprochak (AT) gmail (DOT) com> wrote:

Quote:

On Aug 22, 1:26 pm, "Frank Swarbrick" <Frank.Swarbr... (AT) efirstbank (DOT) com
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.

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!

Bad habit? :-)

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

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

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

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

Quote:

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.

Quote:

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

#12

Quote:

On 8/27/2008 at 10:01 AM, in message
7b5644e4-96a2-4fa2-8b89-440eb9c138b1...oglegroups.com>, Ed

Prochak<edprochak (AT) gmail (DOT) com> wrote:

Quote:

On Aug 22, 1:26 pm, "Frank Swarbrick" <Frank.Swarbr... (AT) efirstbank (DOT) com
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.

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!

Bad habit? :-)

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

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

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

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

Quote:

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.

Quote:

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

#13

Quote:

On 8/27/2008 at 10:01 AM, in message
7b5644e4-96a2-4fa2-8b89-440eb9c138b1...oglegroups.com>, Ed

Prochak<edprochak (AT) gmail (DOT) com> wrote:

Quote:

On Aug 22, 1:26 pm, "Frank Swarbrick" <Frank.Swarbr... (AT) efirstbank (DOT) com
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.

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!

Bad habit? :-)

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

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

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

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

Quote:

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.

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

separate columns or separate table?

comp.databases comp.databases

Re: separate columns or separate table? - 08-28-2008 , 06:37 PM

Re: separate columns or separate table? - 08-28-2008 , 06:37 PM

Re: separate columns or separate table? - 08-28-2008 , 06:37 PM