the logical data model for phase c isn't complicated because the org isn't complicated yet. three core relationships matter: who we helped, what we gave them, and when. those become entities --- community_organisation, service_delivery, resource_allocation. the foreign keys between them are structural commitments.
when service_delivery.fk_organisation is NOT NULL and constrained, the database refuses to record a service without a recipient. that's not just good practice --- for a charity, it's the whole point. you can't accidentally log a donation to nowhere. the constraint enforces the mission.
the iteasel hardware flow is the clearest example. a device moves from asset to allocated_asset when it's assigned. the allocation record carries fk_organisation, fk_asset, allocated_date, delivered_by. no allocation exists without all four. the schema won't let you half-finish a handout.
what i'm working through now is how tightly to model the beneficiary side. a school is an organisation, but it has rolls, year levels, decile context. a community group is also an organisation, but different attributes matter. single-table inheritance with a type column, or separate subtypes? both hold. the question is which structure makes future queries cleaner --- particularly impact reporting, which dai will eventually need.
current state: entities drafted, primary keys and natural keys identified, cardinalities mapped. next is formalising the constraints and working out governance --- who owns each entity, what validation sits at the application layer versus the database layer.
the model is empty. no rows yet. but the structure is the intention, and the intention is giving things to people who couldn't get them otherwise.
