Member Avatar for schone

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!

ss.jpg 67.32 KB
  • 2 Contributors
  • 1 Reply
  • 147 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

Hello

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.

krs,
tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.