0

We are trying data load to SQL server. So Can anyone suggest appropriate table schema for below mentioned Layout.

 01  PRECALC.                                                             
     06 NEWGROUP57.                                                       
        10  PRE-MODIFY-TYPE PIC X.                                        
        10  PRE-HMO-ID PIC X(3).                                          
        10  PRE-SC-CAP PIC X(1).                                          
        10  PRE-ENTRY-SOURCE PIC X(1).                                    
        10  PRE-DIV-NBR PIC 9(2).                                         
     06  PRE-MEMB-NBR.                                                    
        10  PRE-MEMGRP PIC 9(5).                                          
        10  PRE-MEMSUB PIC 9(9).                                          
        10  PRE-MEMDEP PIC 9(2).                                          
     06 NEWGROUP58.                                                       
        10  PRE-CTLNBR PIC 9(12).                                         
        10  PRE-AUDNBR PIC 9(8).                                          
        10  PRE-AUDSUB PIC 9(2).                                                                                
        10  PRE-DSLW-CONT PIC S9(5)V9(2)      DISPLAY SIGN                
                                              LEADING SEPARATE.           
        10  PRE-RECV-CYMD PIC 9(8).                                       
        10  PRE-SYS-CYMD PIC 9(8).                                        
     06  PRE-DETAIL-AREA.                                                 
        07  PRE-DTL-DATA-EXP.                                             
           10  Z-PRE-DTL-DATA PIC X(1068).                                
        07  PRE-DTL-DATA REDEFINES PRE-DTL-DATA-EXP                       
                   OCCURS 4.                                              
           08 NEWGROUP59-1.                                               
              11  PRE-ICDA-CDE PIC X(5).                                  
              11  PRE-PROC PIC X(5).                                      
              11  PRE-PROC-MOD PIC X(4).                                                                 
           08 NEWGROUP59-2.                                               
              11  PRE-AMT-CLAIMED PIC S9(5)V9(2) DISPLAY SIGN             
                                              LEADING SEPARATE.           
              11  PRE-AMT-COPAY PIC S9(5)V9(2) DISPLAY SIGN               
                                              LEADING SEPARATE.           
              11  PRE-AMT-DISCOUNT PIC S9(5)V9(2) DISPLAY SIGN            
                                              LEADING SEPARATE.           
           08 NEWGROUP59-3.                                               
              11  PRE-EPSDT-IND PIC X(1).                                 
              11  PRE-NDC-ID PIC X(11).                                   
              11  PRE-ORIG-POS-CDE PIC X(2).                                               
           08 NEWGROUP59-4.                                               
              11  PRE-ALLOW-AMT-RPR PIC S9(5)V9(2) DISPLAY SIGN           
                                              LEADING SEPARATE.           
     06  PRE-DSIERR.                                                      
        07  PRE-DSI-ERR-EXP.                                              
           10  Z-PRE-DSI-ERR PIC X(100).                                  
        07  PRE-DSI-ERR REDEFINES PRE-DSI-ERR-EXP                         
                   OCCURS 100  PIC 9(1). 




06 NEWGROUP60.                                                       
    10  PRE-PAYOR-INFO PIC X(1).                                      
    10  PRE-RECOVERY-FLG PIC X(1).                                    
    10  PRE-RECV-MDCY PIC 9(8).                                       
    10  PRE-SYS-MDCY PIC 9(8).                                        
    10  PRE-PRV-TAX-ID PIC X(9).                                       
 06  PRE-RPR-DTL-RJMSG-EXP.                                           
    10  Z-PRE-RPR-DTL-RJMS PIC X(8).                                  
 06  PRE-RPR-DTL-RJMSG REDEFINES PRE-RPR-DTL-RJMSG-EXP                
            OCCURS 4  PIC X(2).                                       
 06  PRE-RPR-DTL-RSNCD-EXP.                                           
    10  Z-PRE-RPR-DTL-RSNC PIC X(16).                                 
 06  PRE-RPR-DTL-RSNCD REDEFINES PRE-RPR-DTL-RSNCD-EXP                
            OCCURS 4  PIC X(4).
juergen d
  • 201,996
  • 37
  • 293
  • 362
user2955399
  • 9
  • 1
  • What problem are you having with this? – Bill Woodger Nov 05 '13 at 10:04
  • 3
    Data modeling generally requires knowledge of the business rules, not just data structures. The REDEFINES clauses suggest alternate uses of some fields, there's really no good way to determine how referential integrity should be set up, and we can't tell which fields might be nullable. Badly modeled data tends to lead to nasty performance problems. – cschneid Nov 05 '13 at 12:45
  • Its been a long time since I've seen one of these structures. I don't think the two concepts map over that well, but I would hazard a guess that these are all just variants of a single newsgroup table with some varchar entries to contain the various values. In the COBOL days you used to try pack your bytes together as tightly as possible (hence the y2k bug) - this is no longer necessary. – James Westgate Mar 19 '14 at 07:43

1 Answers1

1

It seems like the OP has departed, but here is some general advice is case someone else stumbles upon this.

For a starting point, make anything which has an occurs or redefines another item have its own table. Possibly all of the 06 levels could each be a table for functional clarity, but that would give you a large number of tables with one row each from the source record, which is inefficient.

Some items like this one:

06  PRE-RPR-DTL-RSNCD-EXP.                                           
    10  Z-PRE-RPR-DTL-RSNC PIC X(16).

Are just the target of a redefines, and probably don't need to be stored themselves. However, some are not the same size as the redefinition, so you need to know if that data is actually needed.

Note that lower levels placed into a separate table will need a foreign key to identify the parent. And all 06-derived tables will need a common key to link them, since they come from the same source record.

As @cschneid says above, you will end up with a poor database design if you don't take the actual requirements/usage into account. You are starting with a COBOL file record layout, not a database design.

Note: Revised based upon helpful comments from Bill Woodger

Turophile
  • 3,367
  • 1
  • 13
  • 21