Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    17

    Unanswered: Composite Attributes ???

    Hi,

    I need some help for the following problem. I am redesigning a database that provides data and business rules for a java application. I can't change the application source code!
    So, here goes my problem. The original design uses an attribute that is not atomic, thus violating first normal forms. In practice it is likely that this will cause problems at some point. I could decompose the attribute within the table into three attributes, thus archiving atomic values, guaranteed. However, as I can not change the source code of the application I need to preserve the original attribute. So, I thought to decompose the attribute as described above, but also preserving the original attribute. Then using a stored procedure to update the original attribute in case one of the decomposed attributes is changes.
    So, my question: Is there a statment, that would do something like (in pseudocode):
    Code:
    INSERT INTO table1
    (coloumn4)
    VALUES ("value4=value1 & value2 & value3")
    If you have any other ideas how to solve this I would appriciate them as well of course.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Unless I'm missing something, this looks like simple concatenation:
    Code:
    'value4='||value1||'&'||value2||'&'||value3
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dajense, if the values all have a character datatype, you should be able to use:

    INSERT INTO table1
    (column4)
    VALUES (value1 CONCAT value2 CONCAT value3)
    (double vertical bars || can be used in place of CONCAT)

    I am not sure of everything involved with your application process but you might look into Triggers instead of a Stored Procedure to make the changes. The advantage is that a Trigger will always activate when the data changes.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by n_i View Post
    Unless I'm missing something, this looks like simple concatenation:
    Code:
    'value4='||value1||'&'||value2||'&'||value3
    Maybe
    Code:
    value4  || ' = ' || value1 || '&' || value2 || '&' || value3
    ?

  5. #5
    Join Date
    Jul 2009
    Posts
    17
    Thanks, that goes in the right direction, however, it's integers and also I need to do this referencing attributes/colloumns.
    I guess I will figure out something from here on, but if you know how to solve this...

    thanks a lot

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dajense, can you supply some examples of the data? It is a little difficult to attempt a solution without knowing what is being manipulated.

  7. #7
    Join Date
    Jul 2009
    Posts
    17
    Ok,
    One Table like this: RULE(RULE_ID (NUMBER), RULE_GROUP_ID(NUMBER), RULE_SET_ID(NUMBER),RULE_CODE(VARCHAR), RULE_GROUP_CODE,(VARCHAR),ITEM_CODE (VARCHAR),SUB_ITEM_CODE(VARCHAR))

    Now, the attribute RULE_CODE should contain in order the values within the attributes RULE_GROUP_CODE, ITEM_CODE, SUB_ITEM_CODE.

    If any of these is changed, RULE_CODE should be updated automatically for all concerned tuples.
    Oh, and sorry, I was wrong. The values are not integer as you can see.

  8. #8
    Join Date
    Jul 2009
    Posts
    17
    Got it, I think.
    Code:
    update RULE
    set RULE_CODE= RULE_GROUP_CODE||ITEM_CODE||SUB_ITEM_CODE
    where rule_id=32;
    Now I just have to learn about triggers and stored procedures .
    Thanks a lot to all of you for your help

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about using generated column?

    Like this:
    ALTER TABLE rule
    ALTER COLUMN rule_code SET GENERATED ALWAYS AS (RULE_GROUP_CODE||ITEM_CODE||SUB_ITEM_CODE)
    Last edited by tonkuma; 11-17-09 at 14:55.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    How about using generated column?
    It will not be updated when one of the components changes, which seems to be the requirement.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is the result on my DB2 9.7 for Windows.

    CREATE TABLE and INSERT:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE RULE
    (RULE_ID         INTEGER
    ,RULE_GROUP_ID   INTEGER
    ,RULE_SET_ID     INTEGER
    ,RULE_GROUP_CODE VARCHAR( 3)
    ,ITEM_CODE       VARCHAR( 7)
    ,SUB_ITEM_CODE   VARCHAR( 5)
    ,RULE_CODE       VARCHAR(15)
        GENERATED ALWAYS AS (RULE_GROUP_CODE || ITEM_CODE || SUB_ITEM_CODE)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO rule
    (RULE_ID
    ,RULE_GROUP_ID
    ,RULE_SET_ID
    ,RULE_GROUP_CODE
    ,ITEM_CODE
    ,SUB_ITEM_CODE)
    VALUES
     (1, 1, 1, 'GR1', '0000001', '00000')
    ,(2, 2, 1, 'GR2', '0000100', '00010')
    ,(3, 2, 1, 'GR2', '0000100', '00020')
    ,(4, 2, 2, 'GR2', '0000500', '00000');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    When SUB_ITEM_CODE was updated, RULE_CODE was updated too:
    Code:
    ------------------------------ Commands Entered ----------------------------
    SELECT * FROM rule;
    ------------------------------------------------------------------------------
    
    RULE_ID     RULE_GROUP_ID RULE_SET_ID RULE_GROUP_CODE ITEM_CODE SUB_ITEM_CODE RULE_CODE      
    ----------- ------------- ----------- --------------- --------- ------------- ---------------
              1             1           1 GR1             0000001   00000         GR1000000100000
              2             2           1 GR2             0000100   00010         GR2000010000010
              3             2           1 GR2             0000100   00020         GR2000010000020
              4             2           2 GR2             0000500   00000         GR2000050000000
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    UPDATE rule
      SET sub_item_code = '99999'
    WHERE rule_id = 1;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM rule;
    ------------------------------------------------------------------------------
    
    RULE_ID     RULE_GROUP_ID RULE_SET_ID RULE_GROUP_CODE ITEM_CODE SUB_ITEM_CODE RULE_CODE      
    ----------- ------------- ----------- --------------- --------- ------------- ---------------
              1             1           1 GR1             0000001   99999         GR1000000199999
              2             2           1 GR2             0000100   00010         GR2000010000010
              3             2           1 GR2             0000100   00020         GR2000010000020
              4             2           2 GR2             0000500   00000         GR2000050000000
    
      4 record(s) selected.

  12. #12
    Join Date
    Jul 2009
    Posts
    17
    Hey thanks a lot @tankuma,
    this seems to work for now. Very cool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •