This is my attempt at creating a Postgres database schema that can be used by any authority or planning agency to record all development contribution policies. It is more likely to appeal to planning authorities to track new policies when they kick in and when and where old policies existed. It will work with any type of Developer Contribution policy but is separate from the actual recording of contributions listed within legal agreements.

A good demonstration of the problem relates to City of Edinburgh Council’s recent draft supplementary guidance on developer contributions a significant document that extends to 264 pages. Figures are distributed irregularly throughout the document and although laid out with great care it is difficult to access and find the information or sort it in any way independent of the document

And here is an example of a single contribution policy that you would want to record

I have been working towards maintaining a record of contribution policies in a properly normalized table structure I suggest three tables, each table has a specific role. The first table, t001costcodes, acts as the foundational layer, where a new row is added whenever a new cost code is created. This represents the inception of a completely new project, providing a unique identifier and general attributes associated with it. The second table, t002, is designed to handle updates or changes to the policies and contributions associated with a given cost code. This allows the system to maintain a detailed history or current status of policies without altering the original cost code data, thus preserving integrity and traceability. Finally, the third table, t003, records the specific units or components of the cost code that are newly created, updated, or changed. By breaking down cost code data into these three interrelated tables, the database avoids redundancy, ensures that changes are accurately reflected across related datasets, and supports efficient queries and reporting, even as the volume of data grows over time. In our city of Edinburgh Council example with the listed calculations it would be necessary to recalculate some of the unit rates if you were to maintain the below table structure. Below for the exert shown – in table 1 there would be 5 entries corresponding to Greendykes / Brunstane / St Francis / St John and Additional Secondary Capacity. t002 would mirror the entries from t001 as there is not a new policy and then t003 for Greendykes would consist of two entries – although the exact rates are difficult to determine exactly but roughly 860 x flat £ 6,464 rate + 1038 x house £ 17,735 rate should equal £23.9 million.

CREATE TABLE t001costcodes (
    gid integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    costcode varchar(50),
    costcodename varchar(100),
    costcodedescription varchar(1000),
    status varchar(20),
    datevalidfrom date,
    dateretired date,
    gidt020authority integer,
    geom geometry(polygon, 27700)
    updated timestamp DEFAULT NOW(),
    created timestamp DEFAULT NOW()
);

Next the table that will be added to altered whenever a new policy of a costcode is created.

CREATE TABLE t002costcodesubgroup (
    gid integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gidt001 integer,
    retired varchar(1),
    costcode varchar(50),
    costcodename varchar(100),
    category varchar(60),
    exemptions text,
    policybasis text,
    discounts text,
    costbase decimal(18, 2),
    costsource text,
    costbasedate date,
    costbasesource varchar(50),
    housedenominator integer,
    policydocument text,
    geographicarea varchar(300),
    costindex varchar(20),
    costindexdenominator decimal(8, 4),
    costindexdenominatorchecked date,
    policydoclink varchar(1000),
    policydoclinkchecdate date,
    datevalidfrom date,
    dateretired date,
    gidt020authority integer,
    updated timestamp DEFAULT NOW(),
    created timestamp DEFAULT NOW()
);

And below is the table for the blow by blow costs

CREATE TABLE t003costcodesubgroupunitrates (
    gid integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gidt002 integer,
    unit varchar(50),
    costperunit decimal(18, 2),
    gidt020authority integer,
    updated timestamp DEFAULT NOW(),
    created timestamp DEFAULT NOW()
);

And next we setup the referential integrity between the above tables no I prevent cascade on delete

ALTER TABLE t002costcodesubgroup
ADD CONSTRAINT fk_t002_t001
FOREIGN KEY (gidt001)
REFERENCES t001costcodes(gid)
ON DELETE NO ACTION;

And more referential integrity.

ALTER TABLE t003costcodesubgroupunitrates
ADD CONSTRAINT fk_t003_t002
FOREIGN KEY (gidt002)
REFERENCES t002costcodesubgroup(gid)
ON DELETE NO ACTION;

END