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

Last change on this file since 507 was 507, checked in by Nick Burch, 13 years ago

Support storing GB or Irish eastings and northings, but always one matching set

File size: 2.8 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
28        -- Constraints ensure we get a matching pair
29    easting integer,
30    northing integer,
31        ie_easting integer,
32        ie_northing integer,
33
34    ip inet,
35    created_at timestamp without time zone NOT NULL default NOW(),
36    source integer NOT NULL,
37    deleted boolean NOT NULL default 'f',
38    delete_reason integer,
39    user_agent character varying,
40
41        CONSTRAINT pk_postcode PRIMARY KEY (id),
42        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id),
43        CONSTRAINT c_postcode_easting CHECK (CASE WHEN easting IS NULL then 1 else 0 END + CASE WHEN ie_easting IS NULL then 1 else 0 END = 1),
44        CONSTRAINT c_postcode_northing CHECK (CASE WHEN northing IS NULL then 1 else 0 END + CASE WHEN ie_northing IS NULL then 1 else 0 END = 1),
45        CONSTRAINT c_postcode_same_en CHECK ( (easting IS NULL AND northing IS NULL) OR (easting IS NOT NULL AND northing IS NOT NULL) )
46);
47
48-- Bad postcode reports
49CREATE TABLE bad_postcodes (
50    postcode integer NOT NULL,
51    ip inet,
52    reason character varying,
53    reporter_email character varying,
54    created_at timestamp without time zone NOT NULL default NOW(),
55    actioned boolean NOT NULL default 'f',
56
57    CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id)
58);
59
60-- Interest in future plans
61CREATE TABLE interest (
62    email character varying,
63    scotland boolean default false,
64    northernireland boolean default false,
65    ip inet,
66    created_at timestamp without time zone NOT NULL default NOW()
67);
68
69CREATE sequence s_delete_reasons;
70
71-- Reasons for deletion
72CREATE TABLE delete_reasons (
73    id integer NOT NULL DEFAULT NEXTVAL('s_delete_reasons'),
74    reason character varying
75);
76
77-- Districts
78CREATE TABLE districts (
79    outward character varying(4) NOT NULL,
80    district character varying,
81    county character varying,
82
83    CONSTRAINT pk_district PRIMARY KEY (outward)
84);
85
86CREATE INDEX postcode_easting ON postcodes (easting);
87CREATE INDEX postcode_northing ON postcodes (northing);
88CREATE INDEX postcode_outward ON postcodes (outward);
89CREATE INDEX postcode_postcode ON postcodes (outward,inward);
Note: See TracBrowser for help on using the repository browser.