source: trunk/npemap.org.uk/schema_postgres.sql @ 352

Last change on this file since 352 was 331, checked in by Dominic Hargreaves, 14 years ago

Add districts table with script to populate it.

File size: 2.4 KB
Line 
1-- NPE postcode collector schema
2
3
4-- Different Sources of the Postcode
5CREATE SEQUENCE s_sources;
6CREATE TABLE sources (
7        id integer NOT NULL DEFAULT NEXTVAL('s_sources'),
8        name character varying(30) NOT NULL,
9        CONSTRAINT pk_sources PRIMARY KEY (id)
10);
11INSERT INTO sources VALUES (0, 'NPE Postcode web submission');
12INSERT INTO sources VALUES (1, 'FreeThePostcode.org Importer');
13SELECT SETVAL('s_sources',1);
14
15
16-- ID sequence
17--  so we can have a unique way to reference a postcode submission
18CREATE SEQUENCE s_postcodes;
19
20-- Our submitted postcodes
21CREATE TABLE postcodes (
22        id integer NOT NULL DEFAULT NEXTVAL('s_postcodes'),
23    outward character varying(5) NOT NULL,
24    inward character varying(3),
25    raw_postcode_outward character varying(30) NOT NULL,
26    raw_postcode_inward character varying(30),
27    easting integer NOT NULL,
28    northing integer NOT NULL,
29    ip inet,
30    created_at timestamp without time zone NOT NULL default NOW(),
31    source integer NOT NULL,
32    deleted boolean NOT NULL default 'f',
33    delete_reason integer,
34    user_agent character varying,
35
36        CONSTRAINT pk_postcode PRIMARY KEY (id),
37        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id)
38);
39
40-- Bad postcode reports
41CREATE TABLE bad_postcodes (
42    postcode integer NOT NULL,
43    ip inet,
44    reason character varying,
45    reporter_email character varying,
46    created_at timestamp without time zone NOT NULL default NOW(),
47    actioned boolean NOT NULL default 'f',
48
49    CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id)
50);
51
52-- Interest in future plans
53CREATE TABLE interest (
54    email character varying,
55    scotland boolean default false,
56    northernireland boolean default false,
57    ip inet,
58    created_at timestamp without time zone NOT NULL default NOW()
59);
60
61CREATE sequence s_delete_reasons;
62
63-- Reasons for deletion
64CREATE TABLE delete_reasons (
65    id integer NOT NULL DEFAULT NEXTVAL('s_delete_reasons'),
66    reason character varying
67);
68
69-- Districts
70CREATE TABLE districts (
71    outward character varying(4) NOT NULL,
72    district character varying,
73    county character varying,
74
75    CONSTRAINT pk_district PRIMARY KEY (outward)
76);
77
78CREATE INDEX postcode_easting ON postcodes (easting);
79CREATE INDEX postcode_northing ON postcodes (northing);
80CREATE INDEX postcode_outward ON postcodes (outward);
81CREATE INDEX postcode_postcode ON postcodes (outward,inward);
Note: See TracBrowser for help on using the repository browser.