CREATE SCHEMA address;

CREATE TABLE address.type
	(
 	id				SERIAL PRIMARY KEY,
    value			VARCHAR(20) 
    );
    
CREATE TABLE address.country
	(
	id				SERIAL PRIMARY KEY,
	code			CHAR(2),
	name			VARCHAR,
	postal_code		VARCHAR,
	iso3166_alpha3	CHAR(3)
	);

CREATE TABLE address.canton
	(
	id				SERIAL PRIMARY KEY,
	name			VARCHAR(128),
	name_lu			VARCHAR(128)
	);

CREATE TABLE address.municipality
	(
	id				SERIAL PRIMARY KEY,
	canton_id 		INTEGER,
	name				VARCHAR(128),
    name_lu			VARCHAR(128)
    );
    
ALTER TABLE address.municipality ADD CONSTRAINT fkcanton
      FOREIGN KEY (canton_id) REFERENCES address.canton(id) ON DELETE CASCADE;    
    
    
CREATE TABLE address.locality
	(
	id				SERIAL PRIMARY KEY,
	municipality_id 	INTEGER,
	name				VARCHAR(128),
    name_lu			VARCHAR(128)	
	);
    
ALTER TABLE address.locality ADD CONSTRAINT fkmunicipality
      FOREIGN KEY (municipality_id) REFERENCES address.municipality(id) ON DELETE CASCADE;    

CREATE TABLE address.zip
	(
	id				SERIAL PRIMARY KEY,
	zip				INTEGER,
	locality_id 		INTEGER,
	street			VARCHAR(128),
	parity			CHAR(1),
	first			INTEGER,
	last			INTEGER
	);

ALTER TABLE address.zip ADD CONSTRAINT fklocality
      FOREIGN KEY (locality_id) REFERENCES address.locality(id) ON DELETE CASCADE;    

CREATE TABLE	address.address
	(
	id				SERIAL PRIMARY KEY,
	type				VARCHAR(20),
	street			VARCHAR(128),
	number			VARCHAR(10),
	zip				VARCHAR(10),
	pobox			VARCHAR(10),
	pozip			VARCHAR(10),
	locality			VARCHAR(128),
	country			VARCHAR(128)
	);      



