Postgres 4 Planners : Developer Contributions – Flexible Database Structure for accurate Policy Record maintenance

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

SG on Developers Contributions and Infrastructure Delivery Final Draft for Consultation June 2024Download

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 am suggesting 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.

Using Castlebrae as an example. Here I normalise some of the information from the document into the three tables.

In t001costcode – contains a new record everytime there is a new costcode value which should always be unique. The contribution_catchment is the bounding area that all developers within that area will incur liability to contribute. My thinking is that each costcode mirrors a project focus which will be described by the costcodename. In this way each costcode will mirror the big projects. The projects may change in nature – the location , the location etc but the idea that there is that general costcode remains. Changes in nature will be captured in t002costcodesubgroup – in this way the costcodename like the costcode should not really be repeated.

  • gid : 1
  • costcode : example number eg 243
  • costcodename : New 14 class primary school (Greendkyes)
  • contribution_catchment : castlebrae_c1

And a record in t002costcodesubgroup – these records will remain constant unless significant factors about the above project changes e.g costs may be updated or the the unit rates may be replaced with new rates. By replacing this value with new rates and the children we can update the unit rates on a costcode while maintaining an audit trail to previous rates. In local government items projects frequently change. It is designed to be able to cope with contribution rates being updated. The costcode number might not change but the costs can change / and or the unit rates in which case this needs to be recorded. So for instance if there was a option #2 or option #3 this would be added to this table. I am thinking that I will use slow changing dimensions to keep on option as the lead option and that will migrated to the top by the value in datevalidfrom.

  • gid :2
  • gidt001 : 1
  • costcode : 243
  • costcodename : New 14 class primary school committed option #1
  • costbase : £23,967,312
  • housedenominator : 1038
  • flatdenominator : 860

An example record in t003costcodesubroupunitrates – this table holds rates

  • gid : 1
  • gidt002 : 2
  • unit : flats 1>bed
  • rate : £6464

and the other record in t003 will be

  • gid : 2
  • gidt002: 2
  • unit : house
  • rate : 17443

SQL
CREATE TABLE t001costcodes (
    gid integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    costcode varchar(50), -- unique subjectively defined
    costcodeaccounts varchar(50), -- organisation account costcode unknown
    costcodename varchar(100), -- generally unique eg Wincburgh ND Secondary
    contribution_catchment varchar(100), -- eg town of development
    costcodedescription varchar(1000), 
    ccstatus varchar(20),
    datevalidfrom date,
    dateretired date, -- only used when costcodes reach end of life
    tenant_id varchar(50),
    pkid_polygon integer, -- if there is a separate polygon table default null
    geom geometry(polygon, 27700),
    softdelete boolean, -- default false
    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.

SQL
CREATE TABLE t002costcodesubgroup (
    gid integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gidt001 integer, -- effectively a foreign key although referential integrity not enforced
    category varchar(60), 
    costcode varchar(100), -- will act as an alternate foriegn key to t001
    costcodedescription varchar(1000), -- eg option 1    
    exemptions text,
    policybasis text,
    discounts text,
    costbase decimal(18, 2), --
    costsource text,
    costbasedate date,
    costbasesource varchar(50),
    housedenominator integer,
    flatdenominator integer,
    policydocument text,
    geographicarea varchar(300),
    costindex varchar(20),
    costindexdenominator decimal(8, 4),
    costindexdenominatorchecked date,
    policydoclink varchar(1000), -- url to the actual document if known
    policydoclinkchecdate date,
    datevalidfrom date,
    dateretired date,
    tenant_id varchar(50),
    softdelete boolean, -- default false
    updated timestamp DEFAULT NOW(),
    created timestamp DEFAULT NOW()
);

And below is the table for the blow by blow costs

SQL
CREATE TABLE t003costcodesubgroupunitrates (
    gid integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gidt002 integer, -- foreign key to t002
    costcode varchar(100), -- duplicate fk to t002_costcode but might not be needed
    unit varchar(50), -- eg per flat or per house most common
    costperunit decimal(18, 2),
    tenant_id varchar(50),
    softdelete boolean,
    updated timestamp DEFAULT NOW(),
    created timestamp DEFAULT NOW()
);

And here is setting up the unique constraint on the t001

SQL
ALTER TABLE t001costcodes
  ADD CONSTRAINT uq_t001costcodes_costcode UNIQUE (costcode);

I asked ChatGPT to go off and analyse City of Edinburgh’s document and come and create an insert statement of the contributions. It gave me the following SQL file – this is for my own reference – I quickly loaded it into a database and looks broadly correct.

combined_contributions_insertsDownload

END