1 | -- NPE postcode collector schema |
---|
2 | |
---|
3 | |
---|
4 | -- Different Sources of the Postcode |
---|
5 | CREATE SEQUENCE s_sources; |
---|
6 | CREATE 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 | ); |
---|
11 | INSERT INTO sources VALUES (0, 'NPE Postcode web submission'); |
---|
12 | INSERT INTO sources VALUES (1, 'FreeThePostcode.org Importer'); |
---|
13 | SELECT SETVAL('s_sources',1); |
---|
14 | |
---|
15 | |
---|
16 | -- ID sequence |
---|
17 | -- so we can have a unique way to reference a postcode submission |
---|
18 | CREATE SEQUENCE s_postcodes; |
---|
19 | |
---|
20 | -- Our submitted postcodes |
---|
21 | CREATE 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 |
---|
49 | CREATE 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 |
---|
61 | CREATE 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 | |
---|
69 | CREATE sequence s_delete_reasons; |
---|
70 | |
---|
71 | -- Reasons for deletion |
---|
72 | CREATE TABLE delete_reasons ( |
---|
73 | id integer NOT NULL DEFAULT NEXTVAL('s_delete_reasons'), |
---|
74 | reason character varying |
---|
75 | ); |
---|
76 | |
---|
77 | -- Districts |
---|
78 | CREATE 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 | |
---|
86 | CREATE INDEX postcode_easting ON postcodes (easting); |
---|
87 | CREATE INDEX postcode_northing ON postcodes (northing); |
---|
88 | CREATE INDEX postcode_outward ON postcodes (outward); |
---|
89 | CREATE INDEX postcode_postcode ON postcodes (outward,inward); |
---|