Coding of Public / Private Schools

[Brian Lewis]

In issue #353

Does School Type (i.e. Public/Private go in schClass? This is where we expect it)

Currently, it is being read from schLandOwner, but in the data model, this really should be determined by the Authority that runs the school ( stored in SchAuth, and historically in ssSchAuth on School Survey)

It seems we have all the authorities created, but they are not assigned to schools anywhere. Since the bulk of these ( ie all marked Public) can probably be safely assigned to their relevant State authority (ie all public Chuuk schools => Chuuk DOE) , this could be relatively straightforward to do, with only the exception cases ( church run schools?) to code by hand)

I think going this way is the path of least resistance to having this public/private separation available in the data warehouse, which treats Authority as one of the principal disaggregations ( along with district and school type).

Assigned to @ghachey for consideration!

[Brian Lewis]

Script to apply Authorities to public schools (with a rollback):

begin transaction

Select schNo, schAuth, schLandOwner, schClass  , dName
, authCode, authName
from Schools
    INNER JOIN lkpIslands I
ON Schools.iCode = I.iCode
    INNER JOIN Authorities A
        ON A.authName = I.dName + ' DOE'



UPDATE Schools
SET schAuth = authCode
from Schools
    INNER JOIN lkpIslands I
ON Schools.iCode = I.iCode
    INNER JOIN Authorities A
        ON A.authName = I.dName + ' DOE'
WHERE schLandOwner = 'Public'

UPDATE SchoolSurvey
SET ssAuth = schAuth
FROM SchoolSurvey
    INNER JOIN Schools
        ON SchoolSurvey.schNo = Schools.schNo

Select schNo, schAuth, schLandOwner, schClass  , dName
, authCode, authName
from Schools
    INNER JOIN lkpIslands I
ON Schools.iCode = I.iCode
    INNER JOIN Authorities A
        ON A.authName = I.dName + ' DOE'

Select schNo, schName , schLandOwner, schClass, schType
from Schools
WHERE schAuth is null

rollback

[Brian Lewis]

I think we should derive Public/Private for the school in this way, quote it in the workbook, but not allow it to be editable there?

[Ghislain Hachey]

A few things here.

Did you see the issue "Missing Authority and Region/District/Electoral Division after workbook data load." from the issue #353?

So if we have authority in the warehouse as principal disaggregation (in addition to nation and district) then we will have an easy access column with public/private data? Or more specifically as it is configured now in lkpAuthorityTypes (i.e. Church, National, Other, Private, State)? And will we have another easy access columns for lkpAuthorityGovt (i.e. Government, Non-Government)?

I have a script to reapply all the authority lost during workbook loads. Not to worry about this. We just need to fix the behavior.

I think it does not need to be in the workbook at all. Adds unecessary complexity for something not meant to change often if at all. If this needs changing they should go to the web UI. I don't the the added complexity is worth it. No need to track changing authorities through surveys.

[Brian Lewis]

Just a comment on our correspondence from yesterday - while we can produce data disaggregated by Authority and Authority type, we won't get enrolment Ratios by authority.... this is because there is no way to get a population figure related to an authority - in contrast to District and National. So there will not be a warehouse.ClassLevelERAuthority

[Brian Lewis]

No need to track changing authorities through surveys.

Agreed. It does occasionally happen in Solomons that a school run by a church authority is taken over by a provincial education authority - but obviously this is a pretty high level policy operation, and we shouldn't be relying on the survey to know this has happened.

[Brian Lewis]

@ghachey is this resolved?

[Ghislain Hachey]

Yeah, we can get enrolments, repeaters, etc (not the actual indicators of course) directly from tableEnrol and link to the authority to get public/private disaggregation.