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)
| Code Block | ||
|---|---|---|
| ||
{
{
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)
| Code Block | ||
|---|---|---|
| ||
{
{
{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
| Code Block |
|---|
{
"id": 360001,
"text": "",
"story_category_id": 4745,
"project_id": 3171,
"user_id": 5652,
"charity_id": "",
"send_survey_id": "",
"event_id": "",
"known_beneficiaries_count": 1,
"unknown_beneficiaries_count": 0,
"beneficiaries": [
{
"id": 132017,
"indicators": [
{
"id": 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_ratio_name": "On Progression"
},
{
"id": 5666,
"name": "Young Person NEET/EET Status",
"indicator_type": 2,
"ratio_set_id": 3383,
"indicator_type_id": 2,
"success_direction": "none"
},
{
"id": 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
}
|
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
| Code Block | ||
|---|---|---|
| ||
{
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
} |