CREATE TABLE companies (
  co_id serial,
  co_name varchar(64),
  co_postcode varchar(16),
  co_lastchg timestamp
);
CREATE INDEX co_name_idx ON companies (co_name);

CREATE TABLE products (
  pr_code varchar(6) PRIMARY KEY,
  pr_desc varchar(64)
);

CREATE TABLE orders (
  ord_id serial,
  ord_company int4 REFERENCES companies(co_id),
  ord_product varchar(6) REFERENCES products(pr_code),
  ord_qty int4,
  ord_placed date,
  ord_delivered date,
  ord_paid date
);
CREATE INDEX ord_placed_idx ON orders(ord_placed);
CREATE INDEX ord_paid_idx ON orders(ord_paid);

CREATE TABLE diary (
  dy_id serial,
  dy_company int4 REFERENCES companies(co_id),
  dy_timestamp timestamp DEFAULT now(),
  dy_type varchar(8),
  dy_notes text
);
CREATE INDEX dy_company_idx ON diary(dy_company);
CREATE INDEX dy_timestamp_idx ON diary(dy_timestamp);
CREATE INDEX dy_cots_idx ON diary(dy_company,dy_timestamp);