CREATE TABLE path_line(
    gid serial NOT NULL,
    geom geometry(Linestring,28992),
    name character varying(50) NOT NULL,
    CONSTRAINT path_line_pkey PRIMARY KEY (gid),
    CONSTRAINT name_unique UNIQUE (name)
);
CREATE INDEX path_line_geom_idx ON location USING gist (geom);

CREATE TABLE path_type(
    sid serial NOT NULL,
    path_line_gid integer,
    type character varying(50) NOT NULL,
    CONSTRAINT path_type_pkey PRIMARY KEY (sid),
    CONSTRAINT path_line_gid_fkey FOREIGN KEY (path_line_gid)
        REFERENCES path_line (gid) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX fki_path_line_gid_fkey ON path_type USING btree (path_line_gid);

CREATE VIEW view_path AS
    SELECT path_type.sid, path_line.geom, path_type.path_line_gid, path_type.type
    FROM path_line
    LEFT JOIN path_type ON path_type.path_line_gid = path_line.gid;
