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

-- 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');
INSERT INTO sources VALUES (2, 'NPEMap Mirror Script');
SELECT SETVAL('s_sources',2);


-- 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),

        -- Constraints ensure we get a matching pair
    easting integer,
    northing integer,
        ie_easting integer,
        ie_northing integer,

    ip inet,
    created_at timestamp without time zone NOT NULL default NOW(),
    source integer NOT NULL,
    deleted boolean NOT NULL default 'f',
    delete_reason integer,
    user_agent character varying,

        CONSTRAINT pk_postcode PRIMARY KEY (id),
        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id),
        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),
        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),
        CONSTRAINT c_postcode_same_en CHECK ( (easting IS NULL AND northing IS NULL) OR (easting IS NOT NULL AND northing IS NOT NULL) )
);

-- 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(),
    actioned boolean NOT NULL default 'f',

    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 sequence s_delete_reasons;

-- Reasons for deletion
CREATE TABLE delete_reasons (
    id integer NOT NULL DEFAULT NEXTVAL('s_delete_reasons'),
    reason character varying
);

-- Districts
CREATE TABLE districts (
    outward character varying(4) NOT NULL,
    district character varying,
    county character varying,

    CONSTRAINT pk_district PRIMARY KEY (outward)
);

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);