Faculty of Engineering and Mathematical Sciences 
Not logged in (login)

help3403


This forum is provided to promote discussion amongst students enrolled in Agile Web Development (CITS3403).
 
Options:
RSS cloud
Jump to:

Database Schema

6 of 419 articles shown, currently no other people reading this forum.
photo
From: ANONYMOUS
Date: Tue 5th May 2020, 3:35pm
Actions: 
        Login-to-reply

 

Hello,

Are we expected to create a relational database schema or a non-relational database 
schema?

Database Schema

photo
From: Takoyaki N.
Date: Wed 6th May 2020, 10:59am
Actions: 
        Login-to-reply

 

I think this is really up to you to determine what schema would best suit the application.  
If you can justify why one schema would be more efficient/better than another then use it. 

Database Schema

photo
From: Timothy F.
Date: Wed 6th May 2020, 12:13pm
Actions: 
        Login-to-reply

 

ANONYMOUS wrote:

> Hello,
> 
> Are we expected to create a relational database schema or a non-relational database 
> schema?

A relational database schema using sqlite is prefered. Using other databases creates 
extra dependencies for the markers.

Database Schema

photo
From: ANONYMOUS
Date: Mon 11th May 2020, 10:02am
Actions: 
        Login-to-reply

 

Hey Tim,

I'm trying to implement such schema so that Question can have many Options and 
correct_option_id can only have 1 correct answer. But this gave me an error:

"Could not determine join condition between parent/child tables on relationship 
Question.options - there are multiple foreign key paths linking the tables.  Specify the 
'foreign_keys' argument, providing a list of those columns which should be counted as 
containing a foreign key reference to the parent table."

Do I need many-to-many relationship?

class Question(db.Model):
    __tablename__ = 'question'
    id = db.Column(db.Integer, primary_key=True)
    set_id = db.Column(db.Integer, db.ForeignKey('set.id'))
    question_text = db.Column(db.Text)
    correct_option_id = db.Column(db.Integer, db.ForeignKey('option.id'))
    
    options = db.relationship('Option', backref='question', lazy='dynamic')
class Option(db.Model):
    __tablename__ = 'option'
    id = db.Column(db.Integer, primary_key=True)
    question_id = db.Column(db.Integer, db.ForeignKey('question.id'))
    option_text = db.Column(db.Text)

Database Schema

photo
From: Takoyaki N.
Date: Wed 13th May 2020, 12:37pm
Actions: 
        Login-to-reply

 

I'm not 100% sure but your schema does look a little strange.

What is the point of the set_id field in the Questions model.  If you want one question to 
have multiple options listed in the options set just query the Options table where 
question_id is equal to the id of the question.  Not sure why you would then bother to have a 
separate table (set).  

TLDR: Try comment out the line     set_id = db.Column(db.Integer, db.ForeignKey('set.id'))
 and see if you get the same issue.

Database Schema

photo
From: Timothy F.
Date: Wed 13th May 2020, 1:16pm
Actions: 
        Login-to-reply

 

ANONYMOUS wrote:

> Hey Tim,
> 
> I'm trying to implement such schema so that Question can have many Options and 
> correct_option_id can only have 1 correct answer. But this gave me an error:
> 
> "Could not determine join condition between parent/child tables on relationship 
> Question.options - there are multiple foreign key paths linking the tables.  Specify the 
> 'foreign_keys' argument, providing a list of those columns which should be counted as 
> containing a foreign key reference to the parent table."
> 
> Do I need many-to-many relationship?
> 
> class Question(db.Model):
>     __tablename__ = 'question'
>     id = db.Column(db.Integer, primary_key=True)
>     set_id = db.Column(db.Integer, db.ForeignKey('set.id'))
>     question_text = db.Column(db.Text)
>     correct_option_id = db.Column(db.Integer, db.ForeignKey('option.id'))
>     
>     options = db.relationship('Option', backref='question', lazy='dynamic')
> class Option(db.Model):
>     __tablename__ = 'option'
>     id = db.Column(db.Integer, primary_key=True)
>     question_id = db.Column(db.Integer, db.ForeignKey('question.id'))
>     option_text = db.Column(db.Text)

The db.relationship normally automtically determines how to join two tables, but in this case it finds it ambiguous 
(probably because question.correct_option_id references option.id, and  option.question_id references question.id).

To remove the ambiguity you can use the foreign keys field:
options = db.relationship('Option', backref='question', foreign_keys='Option.question_id', lazy='dynamic')
This Page


Program written by: [email protected]
Feedback welcome
Last modified: 11:27am Sep 21 2020