Schema design for small project...

[Ocean West]

I am trying to decide on a schema approach to a small add-on to a vendor/supplier evaluation process, we will have this 'survey' done at intervals, and I am deciding to either put data as a fields or as a table with a key value pair which would require a scripted creation of the data set.

Or storing the data as a JSON array and pivot it out to a virtual list. The jury is still out if we need to have aggregate data across all vendors to get a tally if say one of these items is a no. If data is stored in an array computing the aggregate across dozens of records can present its own challenge.

Curious to entertain some concepts and suggestions..

 

During the evaluation period:

These questions have Y / N / NA 

Quality
1.     Were the correct materials shipped?
2.     Did received materials arrive in good condition?
3.     Did all purchased materials met specifications and requirements?
4.     Did all purchased materials maintain the quality standards for products?
5.     Did we receive prior notification of any changes to purchased materials?

Service
1.     Were orders processing correctly and efficiently?
2.     Were orders received on time and undamaged?
3.     Were payment requests handled correctly, efficiently and courteously?
4.     Was customer service courteous and helpful?

Value
1.     Have product prices remained unchanged?
2.     Have shipping and handling costs remained unchanged?
3.     Was prior notification received for any pricing changes?

(no answers above require a text explanations field)

Resulting actions (can be more than one):
1.     No action required
2.     Inform supplier of concerns
3.     Submit a formal complaint to supplier
4.     Request CAPA from supplier
5.     Move to APPROVED | PROVISIONAL | NOT APPROVED list
 

[Fitch]

I'd lean towards using a table for the Y/N/NA items, but with separate (boolean) fields for Y/N/NA.

And the actions in the parent record, again with separate booleans for each. 

Makes the reporting dead simple.

[comment]

I would have a table for Questions (12 permanent records) and a table for Responses (12 records for each person surveyed).  Optionally a table of Respondents and a table of Surveys.

The actions part is not clear. Is an action required as the result of a single response? Or as a result of combination of responses from a single respondent? Or from multiple respondents?

 

48 minutes ago, Fitch said:

with separate (boolean) fields for Y/N/NA.

I am not sure that's required, but in any case those would better be calculation fields. Otherwise you open the door to having more then one checked.

 

Edited June 22, 2017 by comment