Jump to content

Schema design for small project...


This topic is 2472 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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
 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 2472 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.