Jump to content

Constraint on Child-table based on value in Parent-table


Recommended Posts

Hello.

This problem deals with survey questions and their corresponding answer-choices.

Let's say I have a satisfaction scale like this...

	1 - Strongly Disagree
	2 - Disagree
	3 - neither
	4 - Agree
	5 - Strongly Agree
	

 

I created a new table called "answer_group" to bind together a bunch of related choices...

	answer_group -||-----|<- answer
	


Here is what I want to happen...

In the "answer_group" table, for a given "answer group" (record) there would be a column called: "no-of-choices" and it would have a value like "5".

I want MySQL to look at that value of "5" in the parent table/record, and as I add child records in the "answer" table - linked by a foreign key, I want MySQL to prevent the database user from adding/linking more than "5" child records.

Why?

Because if a "Question" has a 5-Choice satisfaction question, then I don't want to accidentally display 7 Choices?!  (Especially since my PHP code paints however many Choices you tell it in the database.)

This is actually a pretty easy data-entry / data-linking mistake to make, so I want MySQL to keep me honest!

 

It seems like I would need either a "trigger" or a "stored procedure" (or maybe both) to do this? 

And I'm not sure how involved something like this is?

But it never hurts to ask, and to start investigating things. 

(For v2.0, another thing I want to do is become proficient with triggers and stored procedures to build a more sophisticated database.)

 

My best guess at how to do this would be to add a "Before Insert" trigger to the (child) "answer" table - on the FK column - and have it count how many new sub-records are created, and once counter >= "answer_group"."no_of_choices" for a FK, then prevent MySQL from adding any more sub-records in the "answer" table.

Just my guess...

 

 

Edited by SaranacLake
Link to post
Share on other sites

I wouldn't bother. It's not as though your users will be creating these "answer" records. It's completely under your control and testing will show any "accidents".

And what if it really is a 5-answer group but you accidentally enter "4" or  "6" in the group table?

It's equivalent to storing a derived total instead of getting the total from the data. Let the number of answers dictate the group size.

Link to post
Share on other sites
10 hours ago, Barand said:

I wouldn't bother. It's not as though your users will be creating these "answer" records. It's completely under your control and testing will show any "accidents".

True, but even I make mistakes, and guard-rails can be nice.

 

10 hours ago, Barand said:

And what if it really is a 5-answer group but you accidentally enter "4" or  "6" in the group table?

It's equivalent to storing a derived total instead of getting the total from the data. Let the number of answers dictate the group size.

Good point, so let me re-state.

I have a "question_type" table that would define a question as say "rating-5pt".  To your point, I could store a "5" in the "question_type" lookup so that as I add in hundreds of questions, I am always pulling that "5" from the lookup table thus eliminating the issue I believe you are pointing out.

To your earlier question...  What could happen?

Well, I could end up with an UN-balanced set of choices (e.g. 1-Strongly Disagree, 2-Disagree, 3-Neither, 4-Agree) with "5-Strongly Agree" missing and thus skewing the question and responses.

 

I assume what i want to do could be fairly easily done coding a trigger, but not having any hnds-on experience with triggers or stored procedures I'm not certain.

Am hoping someone can at least explain if what i want to do is doable...

Link to post
Share on other sites

If you are so error-prone, use your form which maintains those answer_group/answer tables to validate.

If you create a group for 5, only show 5 answer rows.

If editing and you accidentally have 6 answers, display the sixth in say, red, to flag the error.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.