RelationalDesign : DDL.sql

/*
drop table project.artist_acts_theatre;
drop table project.artist_exhibits_gallery;
drop table project.admins;
drop table project.artist;
drop table project.customer; 
drop table project.organizer; 
drop table project.ticket;
drop table project.seat;
drop table project.transactions;
drop table project.review;
drop table project.cart;
drop table project.gallery; 
drop table project.theatre;
drop table project.museum;
drop table project.events;
drop table project.users;
*/


create table project.users
(
    id_user      serial4 primary key,
    username     varchar(50) not null,
    email        varchar(50) not null,
    surname      varchar(50) not null,
    "name"       varchar(50) not null,
    phone_number varchar(50) not null,
    "password" bpchar(40) not null
);


create table project.artist
(
    id_user        integer primary key,
    type_of_artist varchar(50) not null,
    id_user_admin  integer,
    constraint fk_user_artist foreign key (id_user) references project.users (id_user)
        on delete cascade on update cascade,
    constraint fk_user_artist_admin foreign key (id_user_admin) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.admins
(
    id_user integer primary key,
    constraint fk_user_admin foreign key (id_user) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.organizer
(
    id_user integer primary key,
    constraint fk_user_organizer foreign key (id_user) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.customer
(
    id_user             integer primary key,
    transaction_account varchar(50) not null,
    constraint fk_user_customer foreign key (id_user) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.events
(
    id_event          serial4 primary key,
    "name"            varchar(50) not null,
    price             integer     not null,
    city              varchar(50) not null,
    "date"            timestamp   not null,
    phone_number      varchar(20) not null,
    id_user_artist    integer,
    id_user_organizer integer,
    constraint fk_user_artist foreign key (id_user_artist) references project.users (id_user)
        on delete cascade on update cascade,
    constraint fk_user_organizer foreign key (id_user_organizer) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.gallery
(
    id_event integer primary key,
    style    varchar(50) not null,
    constraint fk_gallery_event foreign key (id_event) references project.events (id_event)
        on delete cascade on update cascade
);


create table project.theatre
(
    id_event integer primary key,
    director varchar(50) not null,
    genre    varchar(50) not null,
    duration integer     not null,
    constraint fk_theatre_event foreign key (id_event) references project.events (id_event)
        on delete cascade on update cascade
);


create table project.museum
(
    id_event   integer primary key,
    history    varchar(500) not null,
    department varchar(50)  not null,
    workshop   varchar(100),
    constraint fk_museum_event foreign key (id_event) references project.events (id_event)
        on delete cascade on update cascade
);


create table project.review
(
    id_review        serial4 primary key,
    "content"        varchar(500) not null,
    rating           integer      not null,
    created_at       timestamp    not null,
    id_user_artist   integer,
    id_user_customer integer,
    constraint fk_user_artist_review foreign key (id_user_artist) references project.users (id_user)
        on delete cascade on update cascade,
    constraint fk_user_customer_review foreign key (id_user_customer) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.seat
(
    id_event integer,
    id_seat  serial4 primary key,
    "number" integer not null,
    constraint fk_seat_event foreign key (id_event) references project.events (id_event)
        on delete cascade on update cascade
);


create table project.cart
(
    id_user_customer integer,
    id_cart serial4 primary key,
    total integer not null,
    constraint fk_user_customer foreign key (id_user_customer) references project.users (id_user)
        on delete cascade on update cascade
);


create table project.transactions
(
    id_transaction   serial4 primary key,
    id_invoice       serial4     not null,
    edited_at        timestamp   not null,
    "name"           varchar(50) not null,
    created_at       timestamp   not null,
    id_user_customer integer,
    id_cart          integer,
    constraint fk_transaction_cart foreign key (id_cart) references project.cart (id_cart)
        on delete cascade on update cascade
);


create table project.ticket
(
    id_ticket        serial4 primary key,
    price            integer     not null,
    "date"           timestamp   not null,
    "location"       varchar(50) not null,
    type_of_event    varchar(50) not null,
    id_event         integer,
    id_user_customer integer,
    id_seat          integer,
    id_cart          integer,
    constraint fk_ticket_event foreign key (id_event) references project.events (id_event)
        on delete cascade on update cascade,
    constraint fk_ticket_customer foreign key (id_user_customer) references project.users (id_user)
        on delete cascade on update cascade,
    constraint fk_ticket_seat foreign key (id_seat) references project.seat (id_seat)
        on delete cascade on update cascade,
    constraint fk_ticket_cart foreign key (id_cart) references project.cart (id_cart)
        on delete cascade on update cascade
);


create table project.artist_exhibits_gallery
(
    id_event_gallery integer,
    id_user_artist   integer,
    constraint fk_artist_event_gallery foreign key (id_event_gallery) references project.events (id_event)
        on delete cascade on update cascade,
    constraint fk_artist_user_gallery foreign key (id_user_artist) references project.users (id_user)
        on delete cascade on update cascade,
    constraint pk_artist_exhibits_gallery primary key (id_event_gallery, id_user_artist)
);


create table project.artist_acts_theatre
(
    id_event_theatre integer,
    id_user_artist   integer,
    constraint fk_artist_event_theatre foreign key (id_event_theatre) references project.events (id_event)
        on delete cascade on update cascade,
    constraint fk_artist_acts_user foreign key (id_user_artist) references project.users (id_user)
        on delete cascade on update cascade,
    constraint pk_artist_acts_theatre primary key (id_event_theatre, id_user_artist)
);