Re: design question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 13 Dec 2006 19:49:40 -0000
Message-ID: <57WdnaAbS57NxB3YnZ2dnUVZ8tu3nZ2d@bt.com>

<vnr1995_at_gmail.com> wrote in message
news:1166026518.651945.189310_at_t46g2000cwa.googlegroups.com...
>I have a table: questions(question_id, content)
>
> I am in the process of creating another table, titled as test, which
> should map a unique test id to multiple questions.
>
> There are two possibilities to create this table:
>
> 1. test(test_id, a set of question_ids); the set of questions is stored
> as a nested table.
>
> 2. test(surrogate_key, test_id, question_id)
>
> Which alternative is better? Or is there any other alternative that is
> better than the above two?
>
> Thanks
>

It looks like you should have three tables:

questions (question_id, etc.)
tests(test_id, etc.)
questions_in_test(test_id, question_id, etc.)

Why have you introduced a surrogate key ?

Don't touch nested tables - it just encourages Oracle to do (approximately) the above, with extra overheads, without telling you what's actually going on.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Wed Dec 13 2006 - 13:49:40 CST