Hi all!

I have been working on a relational database and have been putting it together as an ER diagram which I have attached (it has no relationships at the moment).

Here are how the tables are related:Each Diary (diaries) has many Questions (questions)
Each Question (questions) has many Responses (responses)
Each Response (responses) has one Value (responses_values)
Each Question (questions) has one Answer (answers)

(The lkp_scales table is a static list for a drop down list)

My problem is, I have contained the primary id's (for the main tables) through out my tables but I'm unsure if this is the correct method to be designing this database.

So I'm looking for any input on how my database is designed and if there is a better or alternative way I should be putting it together?

Thanks in advance!

Attachments ss.jpg 67.32 KB
9 Years
Discussion Span
Last Post by tesuji


What is the primary key of responses, (response_id, range_id)?

To ensure referential integrity all foreign keys (fk) must be set up correctly.
Diaries ---< questions: diary_id is fk in questions.
Questions ---< responses: question_id is fk in responses.
Response ---- response_values: because there is one-to-one relationship, response_id (and range_id, if part of pk) must be pk and fk of response_values and response_value_id must be dropped. If you introduce an own pk response_value_id, you will have a one-to-many relationship, what’s wrong. Furthermore, database is unable to ensure referential integrity, that is you will only get rid of this lack by additional programming efforts despite the problem is most effectively solvable at sql level.
Same is true for questions ---- answers. Question_id of answers must be pk and fk, and answer_id has to be dropped.


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.