aidb_deploy.sql

CREATE SCHEMA aidb;




---
--- COUNTRY
---
CREATE TABLE aidb.country (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
    iso TEXT,
    whole_name TEXT,
    nicename TEXT, 
    iso3 TEXT, 
    numcode INTEGER, 
    phonecode INTEGER
    );

COMMENT ON TABLE aidb.country IS 'Countries';

---
--- ADDRESS
---
CREATE TABLE aidb.address (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
    extra_notes TEXT,
    country  INTEGER REFERENCES aidb.country (id),
    town TEXT,
    street TEXT,
    zipcode TEXT,
    longitude DOUBLE PRECISION NOT NULL,
    latitude DOUBLE PRECISION NOT NULL);

COMMENT ON TABLE aidb.location IS 'Location or address of asset/person/company';

---
--- ROOM in building
---
CREATE TABLE aidb.room (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    address  INTEGER REFERENCES aidb.address (id),
    floor TEXT,
    room_number INTEGER,
    room_name TEXT,
    site_name TEXT,
    );

COMMENT ON TABLE aidb.room IS 'Room in building where DC is situated';

---
--- CONTACT
---
CREATE TABLE aidb.contact (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
    name TEXT,
    surname TEXT,
    phone TEXT,
    phone2 TEXT,
    email TEXT,
    email2 TEXT,
    other TEXT ---fax and others
);

COMMENT ON TABLE aidb.contact IS 'Contact to employee or company or vendor';