Intro

You have to think hard.

  • Think about YOUR stake in this.

  • Testing is the way to assure quality

Design

Rest sound trivial and attractive, but it is not (really). You have to make some design choices.

  • How do I use the status codes.

    • 20x is simple

  • How about when things are not or should not be working, like

    • 400 (Bad Request)

    • 401 (Unauthorized) (not logged in?)

    • 403 (Forbidden)

    • 412 (Precondition Failed)

  • And when you are stupid (as in runtime exception)

    • 500 (Internal Server Error)

  • Or not yet done

    • 501 (Not Implemented)

    • operation is not allowed to role (teller, manager, driver, planner, finance)

    • how to implement roles: (Array of strings appears to be best for now)

Sql design implementation

  • Full stack software engineers. That is what you are here for. That is how we build our name.

  • Let the database do as much work as possible.

  • Choose a naming schema across the application.

    • Use the "Dutch" naming convention.

  • Type validation

    • create domains or enum (e.g. for states).

  • Constraint (including state) validation

  • Referential intergrity

  • nullity

  • overlaps in time ranges. See slides on that subject at wiki postgresql.org

  • Make you sql simpler with modern sql. Watch Markus Winand on this topic on youtube.

  • Test, test tests (and have red tests first!)

    • and then implement as litlle as possible.

sql for creating a case insensitive email type
begin work;
CREATE EXTENSION if not exists citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );
commit;

-- test
SELECT 'foobar@bar.com'::email;
SELECT CAST('foobar@bar.com' AS email);
-- should fail
select 'pit'::email;

Same for postal code. (A regex for dutch or german, even check code are possible)

With this you can do

CREATE TABLE customer (
   id serial primary key not null,
   cemail email not null,
   name text not null,
   address_street text not null,
   address_number integer not null,
   address_annex text, -- nullable
   ...
  );

Range checks

If you want to make sure only valid appointments land in your database

sql ddl ti check no double appointments. (From the postgresql doc, which is in your installation AND on the stick)
CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);