 -- Each mailing address can be one of several types
 CREATE TABLE address_address_type (
     address_id integer REFERENCES addresses NOT NULL, -- Address identifier
-    address_type_id integer REFERENCES address_types NOT NULL -- Address type
-) WITH OIDS;
+    address_type_id integer REFERENCES address_types NOT NULL, -- Address type
+    PRIMARY KEY (address_id, address_type_id)
+) WITH OIDS;
+CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id);
+CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id);
 
 CREATE VIEW address_address_types AS
 SELECT address_id,

CREATE VIEW view_addresses AS
SELECT
addresses.address_id,
addresses.site_id,
addresses.line1,
addresses.line2,
addresses.line3,

LEFT JOIN address_types USING (address_type_id)
GROUP BY address_id;

CREATE TABLE site_address (
    site_id integer REFERENCES sites NOT NULL, -- Site identifier
    address_id integer REFERENCES addresses NOT NULL, -- Address identifier
    PRIMARY KEY (site_id, address_id)
) WITH OIDS;
CREATE INDEX site_address_site_id_idx ON site_address (site_id);
CREATE INDEX site_address_address_id_idx ON site_address (address_id);

CREATE VIEW site_addresses AS
SELECT site_id,
array_to_string(array_accum(address_id), ',') AS address_ids
FROM site_address
GROUP BY site_id;

--------------------------------------------------------------------------------
