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

-- NPE postcode collector schema


-- Different Sources of the Postcode
CREATE SEQUENCE s_sources;
CREATE TABLE sources (
        id integer NOT NULL DEFAULT NEXTVAL('s_sources'),
        name character varying(30) NOT NULL,
        CONSTRAINT pk_sources PRIMARY KEY (id)
);
INSERT INTO sources VALUES (0, 'NPE Postcode web submission');
INSERT INTO sources VALUES (1, 'FreeThePostcode.org Importer');
SELECT SETVAL('s_sources',1);


-- ID sequence
--  so we can have a unique way to reference a postcode submission
CREATE SEQUENCE s_postcodes;

-- Our submitted postcodes
CREATE TABLE postcodes (
        id integer NOT NULL DEFAULT NEXTVAL('s_postcodes'),
    outward character varying(5) NOT NULL,
    inward character varying(3),
    raw_postcode_outward character varying(30) NOT NULL,
    raw_postcode_inward character varying(30),
    easting integer NOT NULL,
    northing integer NOT NULL,
    ip inet,
    created_at timestamp without time zone NOT NULL default NOW(),
    source integer NOT NULL,
    deleted boolean NOT NULL default 'f',

        CONSTRAINT pk_postcode PRIMARY KEY (id),
        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id)
);

-- Bad postcode reports
CREATE TABLE bad_postcodes (
    postcode integer NOT NULL,
    ip inet,
    reason character varying,
    reporter_email character varying,
    created_at timestamp without time zone NOT NULL default NOW(),

    CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id)
);

-- Interest in future plans
CREATE TABLE interest (
    email character varying,
    scotland boolean default false,
    northernireland boolean default false,
    ip inet,
    created_at timestamp without time zone NOT NULL default NOW()
);

CREATE INDEX postcode_easting ON postcodes (easting);
CREATE INDEX postcode_northing ON postcodes (northing);
CREATE INDEX postcode_outward ON postcodes (outward);
CREATE INDEX postcode_postcode ON postcodes (outward,inward);