Changeset 31


Ignore:
Timestamp:
Oct 12, 2006, 9:54:23 PM (13 years ago)
Author:
Nick Burch
Message:

Tidy up schema

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/schema_postgres.sql

    r12 r31  
     1-- NPE postcode collector schema
    12
     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
    221CREATE TABLE postcodes (
    3     outward character varying(5),
    4     inward character varying(5),
    5     raw_postcode character varying(30),
    6     easting integer,
    7     northing integer,
     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,
    828    ip inet,
    9     created_at timestamp without time zone,
    10     source integer
     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)
    1134);
    1235
    13 
     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 TracChangeset for help on using the changeset viewer.