Re: Schema Design for Surveys

On Mon, 10 Jan 2005 14:00:21 -0500, Katz.C@xxxxxxxxxxxx
<Katz.C@xxxxxxxxxxxx> wrote:
> Why would anyone suggest multiple response tables?
> 
> ck
> 

Hi CK,

More on this.  I was reminded of this thread today when doing
some work on an application that has a less than optimal 
database design.

Do you see what is wrong with the following query?

select
   i.id,
   i.Item_number as Item,
   i.description as Item_Description,
   p3.value as ECCN_Code,
   p4.value as Sched_B_Code
from
   agile_mv.item_mv i,
   agile_mv.page_two_mv pt,
   agile_mv.propertytable_mv p3,
   agile_mv.propertytable_mv p4
where
   i.id = pt.id(+) and
   pt.list11 = p3.id(+) and
   pt.list12 = p4.id(+) and
   i.delete_flag is null
/

Consider the fact that the table PROPERTYTABLE must be included twice in this
SQL to satisfy the query.

The culprits are the  LIST11 and LIST12 columns in the PAGE_TWO table.  Had
 these been implemented properly, these would have been in another table. 

Doing so would simplify the SQL, and also simplify future changes to the
appliction.


The PAGE_TWO table looks like this:

 ----------------------------------------------------- --------
------------------------------------
 CLASS                                                 NOT NULL NUMBER
 CREATE_USER                                                    NUMBER
 DATE01                                                         DATE
...
 DATE05                                                         DATE
 ID                                                    NOT NULL NUMBER
LIST01                                                         NUMBER(10)
...
LIST25                                                         NUMBER(10)
MULTILIST01                                                    VARCHAR2(255)
... 
MULTITEXT35                                                    VARCHAR2(2000)
 NOTES                                                          VARCHAR2(2000)
 TEXT01                                                         VARCHAR2(50)
...
 TEXT25                                                         VARCHAR2(50)

 A much better implementation  would be one where there were separate
tables for each of these repeating groups.

Better as in much easier to query, as well as being more robust for
future enhancements.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l

Other related posts: