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

Last change on this file since 31 was 31, checked in by Nick Burch, 14 years ago

Tidy up schema

File size: 1.2 KB
RevLine 
[31]1-- NPE postcode collector schema
[4]2
[31]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 Inputter');
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
[4]21CREATE TABLE postcodes (
[31]22        id integer NOT NULL DEFAULT NEXTVAL('s_postcodes'),
23    outward character varying(5) NOT NULL,
24    inward character varying(3),
25    raw_postcode character varying(30) NOT NULL,
26    easting integer NOT NULL,
27    northing integer NOT NULL,
[4]28    ip inet,
[31]29    created_at timestamp without time zone NOT NULL default NOW(),
30    source integer NOT NULL,
31
32        CONSTRAINT pk_postcode PRIMARY KEY (id),
33        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id)
[4]34);
35
[31]36CREATE INDEX postcode_easting ON postcodes (easting);
37CREATE INDEX postcode_northing ON postcodes (northing);
38CREATE INDEX postcode_outward ON postcodes (outward);
39CREATE INDEX postcode_postcode ON postcodes (outward,inward);
Note: See TracBrowser for help on using the repository browser.