You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
80 lines
2.0 KiB
SQL
80 lines
2.0 KiB
SQL
CREATE TABLE company (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR NOT NULL,
|
|
street VARCHAR,
|
|
house_number VARCHAR,
|
|
zip_code VARCHAR,
|
|
city VARCHAR
|
|
);
|
|
|
|
CREATE TABLE "user" (
|
|
id SERIAL PRIMARY KEY,
|
|
login VARCHAR NOT NULL,
|
|
password VARCHAR NOT NULL,
|
|
full_name VARCHAR,
|
|
email VARCHAR,
|
|
admin bool NOT NULL default false,
|
|
get_emails bool NOT NULL default false,
|
|
active bool NOT NULL DEFAULT true
|
|
);
|
|
|
|
CREATE TYPE slot_type AS ENUM ('Quarter', 'Half', 'Hour', 'Day');
|
|
|
|
CREATE TABLE property (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR NOT NULL,
|
|
description VARCHAR,
|
|
price NUMERIC(9, 2) NOT NULL,
|
|
slot slot_type NOT NULL default 'Hour',
|
|
allow_multi BOOLEAN NOT NULL default true,
|
|
active BOOLEAN NOT NULL default true
|
|
);
|
|
|
|
CREATE TYPE message_type AS ENUM ('NewReservation', 'NewReservationCust', 'ReservationApp', 'ReservationCanceled');
|
|
|
|
CREATE TABLE message (
|
|
id SERIAL PRIMARY KEY,
|
|
msg_type message_type NOT NULL ,
|
|
subject VARCHAR NOT NULL ,
|
|
"text" TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE opening_hour (
|
|
id SERIAL PRIMARY KEY,
|
|
day INTEGER NOT NULL ,
|
|
"from" TIME NOT NULL ,
|
|
"to" TIME NOT NULL,
|
|
discount INTEGER
|
|
);
|
|
|
|
CREATE TABLE customer (
|
|
id SERIAL PRIMARY KEY,
|
|
full_name VARCHAR NOT NULL,
|
|
email VARCHAR NOT NULL,
|
|
phone VARCHAR NOT NULL,
|
|
discount INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TYPE reservation_state AS ENUM ('New', 'Approved', 'Canceled');
|
|
|
|
CREATE TABLE reservation_sum (
|
|
id SERIAL PRIMARY KEY,
|
|
uuid uuid NOT NULL,
|
|
date DATE NOT NULL,
|
|
customer INTEGER REFERENCES customer(id) NOT NULL,
|
|
price NUMERIC(9, 2) NOT NULL,
|
|
state reservation_state NOT NULL DEFAULT 'New',
|
|
date_create DATE NOT NULL,
|
|
edited_by INTEGER REFERENCES "user"(id) ON DELETE SET NULL,
|
|
note VARCHAR
|
|
);
|
|
|
|
|
|
CREATE TABLE reservation (
|
|
id SERIAL PRIMARY KEY,
|
|
"from" TIME NOT NULL,
|
|
"to" TIME NOT NULL,
|
|
property INTEGER REFERENCES property(id) NOT NULL,
|
|
summary INTEGER REFERENCES reservation_sum(id) NOT NULL
|
|
);
|