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

[SaranacLake]

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 February 19, 2021 by SaranacLake

[Barand]

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.

[SaranacLake]

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...

[Barand]

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.