Plant Variety IP & Royalty Licensing Management Platform

Business context

Organisations that manage plant variety intellectual property — registering breeders' rights, licensing cultivars to growers, and collecting royalties — depend on accurate, well-governed data across the full IP lifecycle: from importation and quarantine, through propagation and block management, to licensing, sales, and royalty reconciliation.

This case study models a common real-world scenario: a growing IP-management operation had outgrown its original Microsoft Access database. Management needed an independent review of whether the existing system could keep supplying reliable information for IP-management decisions, and a plan for what should replace it.

This is a synthetic, independently-built recreation for portfolio purposes. All organisation names, growers, cultivars, and figures shown are fictional. It is not derived from, and does not reproduce, any real employer's or client's data or systems.

Systems & business analysis approach

Business process mapping. Walked the end-to-end IP lifecycle — importation & quarantine, registration, propagation planning, virus testing, licensing, nursery orders, and royalty collection — to identify where the current system created risk or manual rework.

Current-state systems audit. Assessed the existing Microsoft Access database against the business processes it was meant to support: data integrity controls, concurrent-access limits, reporting capability, and audit trail gaps.

Requirements elicitation. Consolidated functional requirements per subject area (licensing, royalties, quarantine, nursery orders) into a target data model, validated against the mapped business processes.

Target architecture & migration design. Designed a normalised PostgreSQL schema with referential integrity, indexing for the identified reporting workloads, and a phased Access-to-PostgreSQL migration approach.

Requirements & stakeholder documentation

The professional deliverables produced during requirements gathering and stakeholder sign-off:

Business Requirements Document

Objectives, stakeholder map, and 9 prioritised business requirements (BR-01 to BR-09).

Open BRD →

Requirements Traceability Matrix

Each requirement traced to its data model entity, migration phase, and verification method.

Open matrix →

As-Is / To-Be Process Flow

Business process diagrams comparing the legacy manual reconciliation flow to the target automated flow.

Open process flow →

Stakeholder Presentation

7-slide steering committee deck: problem, approach, findings, recommendation, and sign-off ask.

Open presentation →

Key findings & recommendations

Findings

Business-critical relationships (licence ↔ royalty ↔ block) were maintained manually across linked tables, with no enforced referential integrity.
Royalty reconciliation relied on ad-hoc queries rerun each cycle, with no single source of truth for payment status.
Quarantine and pathology test results were not consistently linked back to the blocks and permits they governed.
Single-file Access architecture limited concurrent multi-user access and complicated backup/version control.

Recommendations

Migrate to a normalised relational schema (PostgreSQL) with enforced foreign keys across the licensing, royalty, and block-management subject areas.
Introduce a royalty_transactions ledger with explicit payment-status tracking to replace recalculated reports.
Link quarantine and pathology records directly to blocks and permits to close the audit-trail gap.
Phase the migration by subject area, starting with licensing and royalty (highest business risk).

Target data model — 8 subject areas

The requirements were consolidated into a normalised schema spanning:

CultivarsPlant/variety master data, IP ownership, release status

StakeholdersGrowers, licensors, nurseries, customers

LicencesIP licensing agreements per cultivar/stakeholder

BlocksNucleus, foundation, mother & production block tracking

Pathology testsVirus indexing & testing results per block

Royalty contracts & transactionsRates, calculation basis, payment ledger

Nursery ordersPropagation & order fulfilment

Import/export permits & quarantineCross-border movement & clearance tracking

Full DDL: schema.sql | Sample data: sample_data.sql | Interactive ERD →

Skills demonstrated

Business process analysis Systems audit & gap analysis Requirements elicitation Entity-relationship modelling Data governance design PostgreSQL / SQL DDL Legacy system migration planning Stakeholder-facing documentation