Datenmodell

Table of Contents

1. General Approach
2. Implementation

General Approach

Implementation

Election Results
Constituency 1<->n ElectionResult n<->1 Party
ElectionResult(Constituency_ID, Party_ID, election_ID, primaryVotes, secondaryVotes)

CREATE TABLE electionresult
(
  constituencyid integer NOT NULL,
  partyid integer NOT NULL,
  "primaryVotes" integer,
  "secondaryVotes" integer,
  election_electionid bigint,
  CONSTRAINT constituency_fk FOREIGN KEY (constituencyid)
      REFERENCES constituency (gid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT election_fk FOREIGN KEY (election_electionid)
      REFERENCES election (electionid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT party_fk FOREIGN KEY (partyid)
      REFERENCES party (partyid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Party(Party_ID, Name)

CREATE TABLE party
(
  partyid serial NOT NULL,
  name character(200),
  color character varying(255),
  partyname character varying(255),
  CONSTRAINT party_pkey PRIMARY KEY (partyid)
)

Election
Election(election_ID, date, year)

CREATE TABLE election
(
  electionid bigint NOT NULL,
  date timestamp without time zone,
  year integer NOT NULL,
  CONSTRAINT election_pkey PRIMARY KEY (electionid)
)

Constituency Data
Constituency 1<->n ConstituencyData n <-> 1 ConstituencyDataKeys ConstituencyData(Constituency_ID, Key_ID, Value)
ConstituencyDataKeys(Key_ID, name)

CREATE TABLE public.constituency_data
(
  gid BIGINT NOT NULL,
  key CHARACTER VARYING(255),
  value BIGINT NOT NULL,
  CONSTRAINT belongs_to_constituency FOREIGN KEY (gid)
      REFERENCES constituency (gid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

County Data
County 1 <-> n CountyData n <-> 1 CountyDataKeys CountyData(County_ID, Key_ID, Value)
CountyDataKeys(Key_ID, name)

CREATE TABLE public.county_data
(
  gid BIGINT NOT NULL,
  key CHARACTER VARYING(255),
  value BIGINT NOT NULL,
  CONSTRAINT belongs_to_county FOREIGN KEY (gid)
      REFERENCES county (gid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

County
County(County_ID, Name, Nr, coordinates)

Constituency
Constituency(Constituency_ID, Name, Nr, coordinates)