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 | 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 | |
---|
35 | CONSTRAINT pk_postcode PRIMARY KEY (id), |
---|
36 | CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id) |
---|
37 | ); |
---|
38 | |
---|
39 | -- Bad postcode reports |
---|
40 | CREATE TABLE bad_postcodes ( |
---|
41 | postcode integer NOT NULL, |
---|
42 | ip inet, |
---|
43 | reason character varying, |
---|
44 | reporter_email character varying, |
---|
45 | created_at timestamp without time zone NOT NULL default NOW(), |
---|
46 | actioned boolean NOT NULL default 'f' |
---|
47 | |
---|
48 | CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id) |
---|
49 | ); |
---|
50 | |
---|
51 | -- Interest in future plans |
---|
52 | CREATE TABLE interest ( |
---|
53 | email character varying, |
---|
54 | scotland boolean default false, |
---|
55 | northernireland boolean default false, |
---|
56 | ip inet, |
---|
57 | created_at timestamp without time zone NOT NULL default NOW() |
---|
58 | ); |
---|
59 | |
---|
60 | CREATE INDEX postcode_easting ON postcodes (easting); |
---|
61 | CREATE INDEX postcode_northing ON postcodes (northing); |
---|
62 | CREATE INDEX postcode_outward ON postcodes (outward); |
---|
63 | CREATE INDEX postcode_postcode ON postcodes (outward,inward); |
---|