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 | |
---|
33 | CONSTRAINT pk_postcode PRIMARY KEY (id), |
---|
34 | CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id) |
---|
35 | ); |
---|
36 | |
---|
37 | -- Bad postcode reports |
---|
38 | CREATE TABLE bad_postcodes ( |
---|
39 | postcode integer NOT NULL, |
---|
40 | ip inet, |
---|
41 | reason character varying, |
---|
42 | reporter_email character varying, |
---|
43 | created_at timestamp without time zone NOT NULL default NOW(), |
---|
44 | |
---|
45 | CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id) |
---|
46 | ); |
---|
47 | |
---|
48 | CREATE INDEX postcode_easting ON postcodes (easting); |
---|
49 | CREATE INDEX postcode_northing ON postcodes (northing); |
---|
50 | CREATE INDEX postcode_outward ON postcodes (outward); |
---|
51 | CREATE INDEX postcode_postcode ON postcodes (outward,inward); |
---|