Re: Schema Design for Surveys
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Katz.C@xxxxxxxxxxxx
- Date: Mon, 17 Jan 2005 18:25:02 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
