aidb.sql

CREATE SCHEMA aidb;

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
    );

CREATE TABLE aidb.location (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
    long POINT,
    lat POINT
)

COMMENT ON TABLE aidb.location IS 'Location/GPS coordinates of asset/IoT/device.. these could be without address!';

CREATE TABLE aidb.address (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
    description TEXT,
    country  INTEGER REFERENCES aidb.country (id),
    town TEXT,
    street TEXT,
    zipcode TEXT);

COMMENT ON TABLE aidb.address IS 'Address of asset/person/company';

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

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