Non-Spatial Database Tables

The following database tables are used in this discussion of non-spatial editing.

Standalone Tables

In addition to the public.staff table, the following lookup table will be edited as a standalone table:

DROP TABLE IF EXISTS public.visit_purpose;

CREATE TABLE public.visit_purpose (
  id serial primary key,
  purpose varchar(10) not null
);

INSERT INTO public.visit_purpose (purpose) values 
('Initial'),
('Repair'),
('Confirm');

Entity Table

The following table stores the (optional) address at which a piece of graffiti is located: it is an example of an entity table.

DROP TABLE IF EXISTS public.graffiti_address;

CREATE TABLE public.graffiti_address (
  id			 serial not null primary key, 
  graffiti_id    int unique,
  street_address varchar(100),
  city_suburb    varchar(100),
  postcode       varchar(4),
  state          varchar(50)
);
/* graffiti_id will be same as graffiti id.
   Unique constraint ensures only 1 address record per graffiti item */

ALTER TABLE public.graffiti_address 
        ADD CONSTRAINT address_graffiti_fk
        FOREIGN KEY (graffiti_id)
        REFERENCES public.graffiti (id);

Related Table

The following table records the different dates a person visits a graffiti site: it is an example of a related table.

DROP TABLE IF EXISTS public.graffiti_visit;

CREATE TABLE public.graffiti_visit (
  id          serial primary key,
  graffiti_id int not null,
  visitor     int not null,
  visit_date  date not null,
  purpose     int not null
);

ALTER TABLE public.graffiti_visit 
  ADD CONSTRAINT GRAFFITI_VISIT_ONE_MANY_FK 
     FOREIGN KEY (graffiti_id) 
     REFERENCES public.graffiti (id);

ALTER TABLE public.graffiti_visit
  ADD CONSTRAINT GRAFFITI_VISIT_STAFF_FK 
     FOREIGN KEY (visitor) 
     REFERENCES public.staff (id);
	 
ALTER TABLE public.graffiti_visit
  ADD CONSTRAINT GRAFFITI_VISIT_PURPOSE_FK 
     FOREIGN KEY (purpose) 
     REFERENCES public.visit_purpose (id);

<< Previous

Next >>