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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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. Wink

( "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

Previous Topic: Cursor
Next Topic: Column Altered Backing Out
Goto Forum:
  


Current Time: Sun Feb 09 09:27:33 CST 2025