Home » SQL & PL/SQL » SQL & PL/SQL » Need Design Idea to Store 1 - 1 - n relationship
Need Design Idea to Store 1 - 1 - n relationship [message #294413] |
Thu, 17 January 2008 08:50  |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Hello,
I'm facing the following situation :
We have an application with stores "documents" in one table. The document is identified by an document code and a document number and has all the columns for the additional document data.
Example table without the additional data :
CREATE TABLE doc (
code VARCHAR2(4),
num NUMBER(7)
);
INSERT INTO doc VALUES ('AAA',1);
INSERT INTO doc VALUES ('AAA',2);
INSERT INTO doc VALUES ('AAA',3);
INSERT INTO doc VALUES ('AAA',4);
INSERT INTO doc VALUES ('BBB',1);
INSERT INTO doc VALUES ('BBB',2);
INSERT INTO doc VALUES ('BBB',3);
INSERT INTO doc VALUES ('BBB',4);
INSERT INTO doc VALUES ('CCC',1);
INSERT INTO doc VALUES ('CCC',2);
INSERT INTO doc VALUES ('CCC',3);
INSERT INTO doc VALUES ('CCC',4);
INSERT INTO doc VALUES ('CCC',5);
INSERT INTO doc VALUES ('CCC',6);
INSERT INTO doc VALUES ('CCC',7);
INSERT INTO doc VALUES ('CCC',8);
We have one business flow which always consists of one AAA document, one BBB document and N (~1-10) CCC documents.
We have a procedure which looks for problems in that business flow, and writes the relevant documents into a table, currently in this format :
CREATE TABLE trans_problem (
code01 VARCHAR2(4),
num01 NUMBER(7),
code02 VARCHAR2(4),
num02 NUMBER(7),
docs03 VARCHAR2(100)
);
INSERT INTO trans_problem VALUES ('AAA',1,'BBB',2,'CCC2,CCC4,CCC5');
INSERT INTO trans_problem VALUES ('AAA',4,'BBB',3,'CCC1,CCC8');
I'm now trying to find a way to select all relevant documents from DOC for a given row in TRANS_PROBLEM.
I've pondered to change the trans_problem table to store the data in another format, like for example in three colums the other way around :
id doc no
1 AAA 1
1 BBB 2
1 CCC 3
1 CCC 4
1 CCC 5
2 AAA 4
2 BBB 3
2 CCC 1
2 CCC 8
But that would then require much changes to program parts that use the table as it now is.
Someone here as an idea?
Version 9.2.0.8.
|
|
|
Re: Need Design Idea to Store 1 - 1 - n relationship [message #294432 is a reply to message #294413] |
Thu, 17 January 2008 10:37   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Your table trans_problem is not in 1NF and so your problems.
You can do something like:
SQL> select * from trans_problem;
CODE NUM01 CODE NUM02 DOCS03
---- ---------- ---- ---------- ---------------
AAA 1 BBB 2 CCC2,CCC4,CCC5
AAA 4 BBB 3 CCC1,CCC8
2 rows selected.
SQL> with
2 lines as (select level line from dual connect by level <= 10),
3 data as (
4 select a.*,
5 row_number() over(order by null) id
6 from trans_problem a
7 )
8 select id,
9 decode(line,
10 1, code01,
11 2, code02,
12 substr(
13 substr(docs03,
14 instr(','||docs03||',',',',1,line-2),
15 instr(','||docs03||',',',',1,line-1)
16 - instr(','||docs03||',',',',1,line-2) - 1)
17 , 1, 3)
18 ) doc,
19 decode(line,
20 1, num01,
21 2, num02,
22 to_number(
23 substr(
24 substr(docs03,
25 instr(','||docs03||',',',',1,line-2),
26 instr(','||docs03||',',',',1,line-1)
27 - instr(','||docs03||',',',',1,line-2) - 1)
28 , 4)
29 )
30 ) no
31 from data, lines
32 where line <= length(docs03)-length(replace(docs03,',',''))+3
33 order by id, doc, no
34 /
ID DOC NO
---------- ---- ----------
1 AAA 4
1 BBB 3
1 CCC 1
1 CCC 8
2 AAA 1
2 BBB 2
2 CCC 2
2 CCC 4
2 CCC 5
9 rows selected.
The parts "substr(...,1,3)" and "substr(...,4)" depends on your data and how you can separate doc and number.
Regards
Michel
|
|
|
Re: Need Design Idea to Store 1 - 1 - n relationship [message #294647 is a reply to message #294432] |
Fri, 18 January 2008 05:16  |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Thanks, Michel that looks promising.
I will use it for the prototype version to figure out what additional things are needed, then I can better judge if and how to change the way the transfer problems are stored. 
( "Transfer Problems" are not data related transfer problems that I could try to correct btw, but transfer problems of physical goods. )
[Updated on: Fri, 18 January 2008 06:32] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sun Feb 09 09:27:33 CST 2025
|