5 Replies Latest reply: Jan 28, 2013 4:52 PM by JohnWatson RSS

    Advice needed on design of table for a custom app

    John K.-Oracle
      Hi All -
      I am not a DBA but am working to build an app in APEX and would like to ask for your opinion of my database design.

      The purpose of the APEX app will be to send strings of text to internal locations around the globe for translation into different languages - e.g. French, Simplified Chinese etc.
      The strings we are translating have been entered into Oracle apps as non-seeded setup data and are stored in tables with the suffix '_TL'.

      There are over 600 of these TL tables in our schema and they all have different primary keys and columns that can be language translated.
      (Translation of seeded setup data is provided by Oracle in a language patch, so I am only concerned with records where the CREATED_BY is not SEED_DATA_FROM_APPLICATION).

      I am trying to homogenize these data elements into a single table and will then base my APEX on top of this table. There will be a fair amount pl/sql involved in the final solution but for now I am just trying to finalize the database design.

      For purposes of example assume there are just two translation tables:
       TABLE_NAME                    COLUMN_NAME                   DATA_TYPE                     COLUMN_ID                     CHAR_LENGTH                   CONSTRAINT_NAME               CONSTRAINT_TYPE              
       GL_JE_SOURCES_TL              JE_SOURCE_NAME                VARCHAR2                      1                             25                            GL_JE_SOURCES_PK              P                            
       GL_JE_SOURCES_TL              LAST_UPDATE_DATE              TIMESTAMP(6)                  2                             0                                                                                        
       GL_JE_SOURCES_TL              LAST_UPDATED_BY               VARCHAR2                      3                             64                                                                                       
       GL_JE_SOURCES_TL              CREATION_DATE                 TIMESTAMP(6)                  4                             0                                                                                        
       GL_JE_SOURCES_TL              CREATED_BY                    VARCHAR2                      5                             64                                                                                       
       GL_JE_SOURCES_TL              LAST_UPDATE_LOGIN             VARCHAR2                      6                             32                                                                                       
       GL_JE_SOURCES_TL              DESCRIPTION                   VARCHAR2                      7                             240                                                                                      
       GL_JE_SOURCES_TL              USER_JE_SOURCE_NAME           VARCHAR2                      8                             25                                                                                       
       GL_JE_SOURCES_TL              LANGUAGE                      VARCHAR2                      9                             4                             GL_JE_SOURCES_PK              P                            
       GL_JE_SOURCES_TL              SOURCE_LANG                   VARCHAR2                      10                            4                                                                                        
       GL_JE_SOURCES_TL              OBJECT_VERSION_NUMBER         NUMBER                        11                            0                                                                                        
       TABLE_NAME                    COLUMN_NAME                   DATA_TYPE                     COLUMN_ID                     CHAR_LENGTH                   CONSTRAINT_NAME               CONSTRAINT_TYPE              
       XLA_EVENT_TYPES_TL            APPLICATION_ID                NUMBER                        1                             0                             XLA_EVENT_TYPES_TL_PK         P                            
       XLA_EVENT_TYPES_TL            ENTITY_CODE                   VARCHAR2                      2                             30                            XLA_EVENT_TYPES_TL_PK         P                            
       XLA_EVENT_TYPES_TL            EVENT_CLASS_CODE              VARCHAR2                      3                             30                            XLA_EVENT_TYPES_TL_PK         P                            
       XLA_EVENT_TYPES_TL            EVENT_TYPE_CODE               VARCHAR2                      4                             30                            XLA_EVENT_TYPES_TL_PK         P                            
       XLA_EVENT_TYPES_TL            LANGUAGE                      VARCHAR2                      5                             4                             XLA_EVENT_TYPES_TL_PK         P                            
       XLA_EVENT_TYPES_TL            NAME                          VARCHAR2                      6                             80                                                                                       
       XLA_EVENT_TYPES_TL            DESCRIPTION                   VARCHAR2                      7                             240                                                                                      
       XLA_EVENT_TYPES_TL            SOURCE_LANG                   VARCHAR2                      8                             4                                                                                        
       XLA_EVENT_TYPES_TL            CREATION_DATE                 TIMESTAMP(6)                  9                             0                                                                                        
       XLA_EVENT_TYPES_TL            CREATED_BY                    VARCHAR2                      10                            64                                                                                       
       XLA_EVENT_TYPES_TL            LAST_UPDATE_DATE              TIMESTAMP(6)                  11                            0                                                                                        
       XLA_EVENT_TYPES_TL            LAST_UPDATED_BY               VARCHAR2                      12                            64                                                                                       
       XLA_EVENT_TYPES_TL            LAST_UPDATE_LOGIN             VARCHAR2                      13                            32                                                                                       
       XLA_EVENT_TYPES_TL            OBJECT_VERSION_NUMBER         NUMBER                        14                            0                                                                                        
      As you can see, the challenge is that with over 600 TL tables there is no consistency between number of primary key constraints on a given TL table or even the data type of the constraints.
      Some TL tables have just two primary constraints yet others have up to seven. In addition, some primary constraint columns are NUMBER datatypes while others are VARCHAR2 and/or DATE columns.

      That said, if I ignore the standard WHO columns and any column with a primary constraint, what's left are the columns that can be translated into the other languages that we have installed:

      USER_JE_SOURCE_NAME and DESCRIPTION for GL_JE_SOURCES_TL, and
      NAME and DESCRIPTION for XLA_EVENT_TYPES_TL

      In order to get the translatable columns of data into a single table, I'd like to create a pl/sql procedure to then loop through each '_TL' table and pull the column names and data of everything that can be translated,
      while putting the table_name and primary key info on each row itself. I'm not asking about the pl/sql though, just the design of this table.

      In order to get everything I need on a single row, this is the table I came up with (shown with just two sample rows of data):
      TABLE_NAME                    COLUMN_NAME                   COLUMN_VALUE                  DATA_TYPE                     CHAR_LENGTH                   PK1CHAR                       PK1CHAR_VALUE                 PK2CHAR                       PK2CHAR_VALUE                 PK3CHAR                       PK3CHAR_VALUE                 PK4CHAR                       PK4CHAR_VALUE                 PK1NUM                        PK1NUM_VALUE                  PK2NUM                        PK2NUM_VALUE                  PK3NUM                        PK3NUM_VALUE                  PK4NUM                        PK4NUM_VALUE                  LANGUAGE                      SOURCE_LANG                   TRANSLATED_VALUE                             
       GL_JE_SOURCES_TL              USER_JE_SOURCE_NAME           Banking                        VARCHAR2                      25                            JE_SOURCE_NAME                300000003210996                                                                                                                                                                                                                                                                                                                                                                                                                                                   F                             US                                                                                                   
       GL_JE_SOURCES_TL              DESCRIPTION                   Banking Transactions           VARCHAR2                      240                           JE_SOURCE_NAME                300000003210996                                                                                                                                                                                                                                                                                                                                                                                                                                                   F                             US                                                                                                      
       
      So each translatable column will have its own row in the table and every row will have the necessary table and primary key information for the subsequent insert statement into the proper TL
      table.

      Since there can be up to seven primary constraint columns on a '_TL' table and the datatypes of these constraints are not consistent between tables, I needed to create place holders for up to seven VARCHAR primary constraints, seven NUMBER primary constraints etc etc. The above is just a prototype so I only included around four of each.
      The LANGUAGE column is always a key constraint so my pl/sql will take that into consideration.

      The users will use my APEX app to enter the proper translation for 'Banking' (row 1) and 'Banking Transactions' (row 2) and then we will run another pl/sql to update the table
      (GL_JE_SOURCES_TL) using the primary key info for that row.

      I realize this is a long question but is this an acceptable table design? I can't think of any other way to homogenize this data into a single table.

      Thoughts or comments are much appreciated. If this isn't clear please let me know and I'll be happy to reformat the question.

      thanks,
      john