CODEWITHSUNDEEP
mysqlsql
cookies
cookies

Reputation: 347

Correct Relationship

I'm not too sure what tables I should have for this to work.

Basically, I currently have a table which stores information about a a-level physics paper. This paper has questions which need answers to.

To store this in the database I thought something like this would work (where bold represents a primary key and italics a foreign key):

Paper (paper_id, number_of_questions, paper_location, markscheme_location)

Question (paper_id, question_number)

However this wouldn't work as potentially the question could be '1)', '1)a)' or even '1)a)i)' This means I would have to have several tables each holding either the respected parts of the question number and have a relationship linking them all together? however the issue then arises that all questions would have to have all three parts? Thanks

Upvotes: 1

Views: 69

Answers (1)

x01saa
x01saa

Reputation: 460

You need to have a parentId for questions. question table schema seems to be sth like this

Question (question_id, question_number, question_text, parent_id, paper_id)

parent_id is a foreign key related to question_id.

Edit: Question rows:

(1, '2', 'foo', NULL, 4)
(2, 'a', 'bar', 1, 4)
(3, 'i', 'blah', 2, 4)

Results:

2. foo?
2.a. bar?
2.a.i blah?

Upvotes: 1

Related Questions