tables.sql

set client_min_messages to error;
set client_encoding = 'UTF8';

-- country codes used mainly for foreign key constraint on people.country
-- from http://en.wikipedia.org/wiki/iso_3166-1_alpha-2 
-- no need for any api to update, insert, or delete from this table.
create table countries (
	code character(2) not null primary key,
	name text
);

create table people (
	id integer primary key generated by default as identity,
	email text unique check (email ~ '\A\S+@\S+\.\S+\Z'),
	name text not null check (length(name) > 0),
	city text,
	state text,
	country char(2) references countries(code)
);

create table items (
	id serial primary key,
	name text not null unique,
	price numeric,
	weight numeric
);

create table invoices (
	id serial primary key,
	person_id integer not null references people(id),
	order_date date not null default current_date,
	payment_date date,
	payment_info text,
	subtotal numeric,
	shipping numeric,
	total numeric,
	country char(2) references countries(code),
	address text,
	ship_date date,
	ship_info text
);
create index on invoices(person_id);
create index unshipped on invoices(payment_date, ship_date, address);

create table lineitems (
	id serial primary key,
	invoice_id integer not null references invoices(id) on delete cascade,
	item_id integer not null references items(id) on delete restrict,
	quantity smallint not null default 1 check (quantity > 0),
	price numeric,
	unique(invoice_id, item_id)
);
create index on lineitems(invoice_id);

create table shipchart (
	id serial primary key,
	country char(2) references countries(code),
	weight numeric not null, -- up to this (invoice.weight <= shipchart.weight)
	cost numeric not null
);
create index on shipchart(country, weight);