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

Last change on this file since 625 was 625, checked in by Nick Burch, 12 years ago

More indicies

File size: 3.0 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');
13INSERT INTO sources VALUES (2, 'NPEMap Mirror Script');
14SELECT SETVAL('s_sources',2);
15
16
17-- ID sequence
18--  so we can have a unique way to reference a postcode submission
19CREATE SEQUENCE s_postcodes;
20
21-- Our submitted postcodes
22CREATE TABLE postcodes (
23        id integer NOT NULL DEFAULT NEXTVAL('s_postcodes'),
24    outward character varying(5) NOT NULL,
25    inward character varying(3),
26    raw_postcode_outward character varying(30) NOT NULL,
27    raw_postcode_inward character varying(30),
28
29        -- Constraints ensure we get a matching pair
30    easting integer,
31    northing integer,
32        ie_easting integer,
33        ie_northing integer,
34
35    ip inet,
36    created_at timestamp without time zone NOT NULL default NOW(),
37    source integer NOT NULL,
38    deleted boolean NOT NULL default 'f',
39    delete_reason integer,
40    user_agent character varying,
41
42        CONSTRAINT pk_postcode PRIMARY KEY (id),
43        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id),
44        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),
45        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),
46        CONSTRAINT c_postcode_same_en CHECK ( (easting IS NULL AND northing IS NULL) OR (easting IS NOT NULL AND northing IS NOT NULL) )
47);
48
49-- Bad postcode reports
50CREATE TABLE bad_postcodes (
51    postcode integer NOT NULL,
52    ip inet,
53    reason character varying,
54    reporter_email character varying,
55    created_at timestamp without time zone NOT NULL default NOW(),
56    actioned boolean NOT NULL default 'f',
57
58    CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id)
59);
60
61-- Interest in future plans
62CREATE TABLE interest (
63    email character varying,
64    scotland boolean default false,
65    northernireland boolean default false,
66    ip inet,
67    created_at timestamp without time zone NOT NULL default NOW()
68);
69
70CREATE sequence s_delete_reasons;
71
72-- Reasons for deletion
73CREATE TABLE delete_reasons (
74    id integer NOT NULL DEFAULT NEXTVAL('s_delete_reasons'),
75    reason character varying
76);
77
78-- Districts
79CREATE TABLE districts (
80    outward character varying(4) NOT NULL,
81    district character varying,
82    county character varying,
83
84    CONSTRAINT pk_district PRIMARY KEY (outward)
85);
86
87CREATE INDEX postcode_easting ON postcodes (easting);
88CREATE INDEX postcode_northing ON postcodes (northing);
89CREATE INDEX postcode_outward ON postcodes (outward);
90CREATE INDEX postcode_postcode ON postcodes (outward,inward);
91
92CREATE INDEX i_postcode_source ON postcodes (source);
93CREATE INDEX i_bad_postcodes_postcode ON bad_postcodes (postcode);
Note: See TracBrowser for help on using the repository browser.