| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Schema Design for Surveys
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 batchIn 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
sanjay
From: oracle-l-bounce_at_freelists.org on behalf of Powell, Mark D
Sent: Mon 1/10/2005 11:45 PM
To: oracle-l_at_freelists.org
Subject: RE: Schema Design for Surveys
why not a shallow table:
Survey and Question could be setup as one or probably two tables then
ques_responses
survey_no inherited from Survey question_no inherited from Question response_no identifier for the response response_text the response itself
user_responses
user or survey occurrence no (to indicate one set of survey results)
survey_no,
question_no,
response_no
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Niall Litchfield
Sent: Monday, January 10, 2005 12:41 PM
To: oracle-l_at_freelists.org
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)
text
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
think.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
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_at_wipro.com immediately
and destroy all copies of this message and any attachments.
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 10 2005 - 12:51:06 CST
 |
 |