| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-17-09, 11:42
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 17
|
|
|
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
|
|
11-17-09, 12:04
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Unless I'm missing something, this looks like simple concatenation:
Code:
'value4='||value1||'&'||value2||'&'||value3
|
|
11-17-09, 12:08
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
|
|
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.
|
|
11-17-09, 12:24
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by n_i
Unless I'm missing something, this looks like simple concatenation:
Code:
'value4='||value1||'&'||value2||'&'||value3
|
Maybe
Code:
value4 || ' = ' || value1 || '&' || value2 || '&' || value3
?
|
|
11-17-09, 12:34
|
|
Registered User
|
|
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
|
|
11-17-09, 12:49
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
dajense, can you supply some examples of the data? It is a little difficult to attempt a solution without knowing what is being manipulated.
|
|
11-17-09, 13:01
|
|
Registered User
|
|
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.
|
|
11-17-09, 13:16
|
|
Registered User
|
|
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 
|
|
11-17-09, 13:51
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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 13:55.
|
11-17-09, 14:45
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
Originally Posted by tonkuma
How about using generated column?
|
It will not be updated when one of the components changes, which seems to be the requirement.
|
|
11-17-09, 15:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
11-18-09, 06:34
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 17
|
|
Hey thanks a lot @tankuma,
this seems to work for now. Very cool 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Composite Attributes ???
