Columns used:
Name of the DB table | Columns Used | Is this column used for join? (Key) |
|---|---|---|
Story | ID, Change ID, Project ID, created_at, updated_at, User ID, charity ID, Story Category ID, Latitude, Longitude, Custom Fields, Send Survey ID, Event ID, Know Beneficiaries Count, Unkown Beneficiaries Count, Ward ID | ID (Story ID) |
Story Indicator Beneficiaries | Beneficiary ID, indicaor ID, Indicator Type, Outcome ID, Sub Ratio ID, | |
Story Change Beneficiaries | Beneficiary ID, Change ID | |
Sub Ratios | Name, User ID | |
Indicators | Name, Indicator ID, Ratio Set ID, Owner ID, Owner Type, Success Direction | |
Changes | Name | ID (Change ID) |
Beneficiary | Name | ID (beneficiary ID) |
Custom Fields | Name | ID (Custom Field ID) |
Columns not used:
Name of the table | Columns not used |
|---|---|
JSON Structure:
Way 1: (Story as unique value perspective)
{
{
story {id:1
},
indicator { id10,id20,id30
},
change { id100,id200
},
beneficiary {001,0012,00123
},
custom fields {
}
},
{
story {id:2
},
indicator { id10,id20,id33
},
change { id100,id200
},
beneficiary {001,0012,00123
},
custom fields {
}
}
}
Way 1: (Story as unique value perspective) (Different Structure)
{
{
{id:360001, text: "", story caregory id:4745, indicator {
5666: { }, 5667, 5668
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
},
{
{id:1, text: "The rainy season is taking its toll on our ambulance! We got stuck in the mud on our way to our clinic yesterday!", story caregory id:7574, indicator {
id10,id20,id30
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
},
{
{id:1, text: "i", story caregory id:7574, indicator {
id10,id20,id30
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
},
{
{id:1, text: "i", story caregory id:7574, indicator {
id10,id20,id30
}, change { id100,id200
}, beneficiary {001,0012,00123
}, custom fields { 112:"Hello",113:"Hi"
}, latitude:110,longitude:130
}
}
Example of single box
{
"id": 360001,
"text": "",
"story caregory id": 4745,
"project ID":3171,
"user ID":5652,
"charity ID":"",
"send survey ID":"",
"event ID":"",
"known beneficiaries count": 1,
"unknown beneficiaries count": 0,
"beneficiary": {
"132017": {
"indicator": {
"5667": {
"name": "Young Person programme/service Status",
"indicator type": 2,
"Ratio Set ID": 3383,
"Indicator Type ID": 2,
"Success Direction": "none",
"outcome ID": 2283,
"owner type": "charity",
"owner ID": 793,
"sub ratio ID": 14151,
"sub raiio name": "On Progression"
},
"5666": {
"name": "Young Person NEET/EET Status",
"indicator type": 2,
"Ratio Set ID": 3383,
"Indicator Type ID": 2,
"Success Direction": "none"
},
"5668": {
"name": "Evidence of Young person status",
"indicator type": 2,
"Ratio Set ID": 3383,
"Indicator Type ID": 2,
"Success Direction": "none"
}
},
"change": {},
"custom fields": {}
}
},
"latitude": 110,
"longitude": 130,
"location": ""
}
Way 2: (Individual response (It could be indicator or change) as unique value (One story can have multiple responses))
Downside: The same data needs to be sent multiple times
{
story id:1,
indicator id:01,
},
{
story_id:2,
change_id:002
},
{
story_id:1,
indicator id:02
},
{
story_id:1,
change id:002
}
{
story_id:1,
indicator id:02
}