RE: Schema Design for Surveys

  • From: <sanjay.khangarot@xxxxxxxxx>
  • To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 11 Jan 2005 00:18:19 +0530

Thanks mark for your mail, to further explain the requirement  I am explaining 
with actual data and relations..

Item master structure
Item Code -- Code of the item
Item name -- Item name
Item weight -- Weight of the components
Operation are performed on the raw material and after an operation the product 
code changes say:
in the first stage polishing operation is performed on item no 1 so the code of 
the resultant product is changed and the entries in the table will look like

item_batch item_name item_weight
1               MB3            20
2               UA              20 ( MB3 is parent)
3               MB1            14  ( No parent)
4               MB2             23
5               MBF            50 final
7               MB3             20 ( This is a new item batch
In the above case MB3 is parent of the UA... I can`t go for an additional 
column like Parent ID to store the Item code of 1 for the UA item... as in the 
subsequent stage a child may have more then one parent...
like for item 5 have 3 parents,

for item MBF  UA , MB1 and MB2 are parents so,

I have created a relation table to store the parent child relation and the 
table structure and the data is

Table name : Item map
Item_batch parent_batch
1                   null
2                    1
3                    null
4                    null
5                    3
5                    2
5                    4

Is it a proper structure to back track what batches were used in the batch no 5 
? or a better alternative exists ?

Thanks and Regards



From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Powell, Mark D
Sent: Mon 1/10/2005 11:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Schema Design for Surveys

why not a shallow table:

Survey and Question could be setup as one or probably two tables then

 survey_no                      inherited from Survey
 question_no            inherited from Question
 response_no            identifier for the response
 response_text          the response itself

 user or survey occurrence no    (to indicate one set of survey results)

Obviously the board does not know all the details and required features but
this came to mind and perhaps it will give you something to think about.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield
Sent: Monday, January 10, 2005 12:41 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Schema Design for Surveys

We have a requirement for a system that will record answers to more
than one survey. Each survey will consist of a number of questions
with responses that can be

multiple choice (eg pick up to 3 preferences from 8)
numeric - single answer

How would people go

one question table, one wide response table
one question table, multiple response tables varying on type of response
some other construct.

This is an internal discussion that we are having here that interests
me (and I do have a preference) and I'd be interested to see what folk
Niall Litchfield
Oracle DBA

Confidentiality Notice

The information contained in this electronic message and any attachments to 
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or 
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or 
Mailadmin@xxxxxxxxx immediately
and destroy all copies of this message and any attachments.

Other related posts: