Tables

• customers
• customer_id (Primary Key)
• Name
• Surname
• adress
• registered_since
• users
• user_id (Primary Key)
• username
• selectedcustomer
• worked
• customer_id (Foreign Key)
• date
• minutesworked
• price
• worked_id (Primary Key)
• payment
• customer_id (Foreign Key)
• date
• amount
• payed_id (Primary Key)

Relations

• customers (1) -> (n) worked
• customers (1) -> (n) payment
• users (placeholder)

create Tables

• create table customers (
  name varchar(30) NOT NULL,
  surname varchar(50) NOT NULL,
  adress varchar(200),
  registered_since date default now(),
  customer_id serial primary key,
  CONSTRAINT UC_Customer UNIQUE (name, surname, adress) );
• create table worked (
  customer_id integer,
  date date NOT NULL,
  minutesWorked integer NOT NULL,
  price money default '15.00' NOT NULL,
  worked_id serial primary key,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );
• create table payment (
  customer_id integer,
  date date NOT NULL,
  amount money NOT NULL,
  payed_id serial primary key,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );
• create table users (
  UserID serial primary key,
  username varchar(30),
  selectedCustomer integer );

Inserts

• insert into customers values('name', 'surname', 'adress');
• insert into worked (customer_id, date, minutesworked, price)
  select (select customer_id from customers
  where customer_id = all (
  select selectedcustomer from users
  where userid = 1)),
  'date', minutesworked, price;
• insert into payment (customer_id, date, amount)
  select (select customer_id from customers
  where customer_id = all (
  select selectedcustomer from users
  where userid = 1)),
  'date', amount;

Join

• select name, surname,
  COALESCE((SELECT sum(amount) from customers as C
  left join payment as P
  on C.customer_id = P.customer_id
  where C.customer_id = (SELECT selectedcustomer
  FROM users as U WHERE U.userid = 1)),
  CAST(0 as money))
  -
  COALESCE((SELECT sum(price) from customers as C
  left join worked as W
  on C.customer_id = W.customer_id
  where C.customer_id = (SELECT selectedcustomer
  FROM users as U WHERE U.userid = 1)),
  CAST(0 as money))
  as openaccount
  from customers
  where customer_id = (SELECT selectedcustomer
  FROM users as U WHERE U.userid = 1);