I am using Oracle 9i with TOAD for the first time and hope someone can give me an advice.
I have a table SPECS with attributes (among others) ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 what means that the number of INP/OUT is restricted to a max of 3.
In order to have as many INP/OUT as needed for each SPEC, I created a new table (SPECS_INFO) with attributes ID (foreign key from table SPECS) , SEQUENCE_NR, INPUT, OUTPUT where I want to copy the information from a row ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 (at the SPECS table) to three rows of the new SPECS_INFO table:
ID INP1 INP2 INP3 OUT1 OUT2 OUT3
A1 CALL A CALL B CALL C ANSW A ANSW B ANSW C
ID SEQUENCE_NR INPUT OUTPUT
A1 1 CALL A ANSW A
A1 2 CALL B ANSW B
A1 3 CALL C ANSW C
Can someone tell me what is the best way to do it or maybe suggest another solution for my problem?
I think you need two tables for all the relationships you'll want though, if the number if ins and out are ever different for example. And you'll probably want to record the type of information that will be sent in and returned...