Postgresql

create table company(
    id serial primary key not null,
    name varchar(200),
    image varchar(200) null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
    is_published BOOLEAN NOT NULL default False,
    is_active BOOLEAN NOT NULL default True
);

create index  idx_company_order company("order");


create table hotel(
    id serial primary key not null,
    company_id int references company(id),
    name varchar(200),
    image varchar(200) null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
    is_published BOOLEAN NOT NULL default False,
    is_active BOOLEAN NOT NULL default True
);

create index  idx_hotel_company_id hotel(company_id);
create index  idx_hotel_order hotel("order");


create table hotelimage(
    id serial primary key not null,
    hotel_id int references hotel(id),
    image varchar(200) null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
    is_published BOOLEAN NOT NULL default False,
    is_active BOOLEAN NOT NULL default True
);

create index  idx_hotelimage_hotel_id hotel(hotel_id);
create index  idx_hotelimage_order hotelimage("order");


create table room(
    id serial primary key not null,
    hotel_id int references hotel(id),
    name varchar(200),
    image varchar(200) null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
    is_published BOOLEAN NOT NULL default False,
    is_active BOOLEAN NOT NULL default True
);

create index  idx_room_hotel_id room(hotel_id);
create index  idx_room_order room("order");


create table roomimage(
    id serial primary key not null,
    room_id int references room(id),
    image varchar(200) null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
    is_published BOOLEAN NOT NULL default False,
    is_active BOOLEAN NOT NULL default True
);

create index  idx_roomimage_room_id room(room_id);
create index  idx_roomimage_order roomimage("order");



create table feature(
    id serial primary key not null,
    company_id int references company(id),
    name varchar(200),
    image varchar(200) null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
    is_published BOOLEAN NOT NULL default False,
    is_active BOOLEAN NOT NULL default True
);

create index  idx_feature_company_id feature(company_id);
create index  idx_feature_order feature("order");


create table attribute(
    id serial primary key not null,
    feature_id int references feature(id),
    name text,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0
);


create index  idx_attribute_feature_id attribute(feature_id);
create index  idx_attribute_order attribute("order");



create table roomfeature(
    id serial primary key not null,
    room_id int references room(id),
    feature_id int references feature(id),
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
);

create index  idx_roomfeature_feature_id roomfeature(feature_id);
create index  idx_roomfeature_room_id roomfeature(room_id);
create unique index idx_roomfeature_room_feature roomfeature(feature_id,room_id)
create index  idx_roomfeature_order roomfeature("order");



create table roomattribute(
    id serial primary key not null,
    room_id int references room(id),
    attribute_id int references attribute(id),
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    "order" int not null default 0,
);

create index  idx_roomattribute_attribute_id roomattribute(attribute_id);
create index  idx_roomattribute_room_id roomattribute(room_id);
create unique index idx_roomattribute_room_attribute roomattribute(attribute_id,room_id)
create index  idx_roomattribute_order roomattribute("order");



create table customer(
    id serial primary key not null,
    name varchar(200),
    surname varchar(200),
    email varchar(200),
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
);


create table reservation(
    id serial primary key not null,
    room_id int references room(id),
    customer_id int references customer(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),

);

create index  idx_reservation_customer_id reservation(customer_id);
create index  idx_reservation_room_id reservation(room_id);
create index  idx_reservation_start_date reservation(start_date);
create index  idx_reservation_end_date reservation(end_date);

SELECT pg_size_pretty( pg_database_size('database_name') );

Optimizing PostgreSQL in a Debian server involves several steps, including tuning PostgreSQL configuration, optimizing server hardware resources, and optimizing queries. Here are some general steps that you can follow:

Tune PostgreSQL configuration:
a. Locate the PostgreSQL configuration file. By default, it is located at /etc/postgresql/<version>/main/postgresql.conf
b. Adjust the following parameters according to your hardware configuration and workload:

shared_buffers: This parameter controls the amount of memory that PostgreSQL uses for caching data in memory. It should be set to a reasonable value based on the available system memory.
work_mem: This parameter controls the amount of memory that PostgreSQL uses for sorting and other temporary operations. It should be set to a reasonable value based on the size of your largest tables and the available system memory.
effective_cache_size: This parameter tells PostgreSQL how much memory is available for caching data. It should be set to a value that reflects the amount of available system memory.
maintenance_work_mem: This parameter controls the amount of memory that PostgreSQL uses for performing maintenance tasks, such as vacuuming. It should be set to a reasonable value based on the size of your database and the available system memory.
checkpoint_completion_target: This parameter controls how much time is allowed for checkpoint completion. It should be set to a value that balances checkpoint overhead with performance.
Optimize server hardware resources:
a. Make sure that your server has enough memory and CPU resources to support your PostgreSQL workload.
b. Use a high-performance storage device such as an SSD to improve disk I/O performance.
c. Consider using RAID to improve data availability and performance.

Optimize queries:
a. Use EXPLAIN to analyze query performance and identify slow queries.
b. Rewrite slow queries to use indexes, if possible.
c. Avoid using SELECT * in queries and instead specify the required columns explicitly.
d. Use LIMIT and OFFSET to limit the amount of data returned by a query.
e. Use prepared statements to avoid repeated parsing of SQL statements.

Monitor PostgreSQL performance:
a. Use a tool such as pgAdmin to monitor PostgreSQL performance metrics, such as CPU usage, memory usage, and disk I/O.
b. Use a tool such as Nagios to monitor PostgreSQL availability and performance.

These are just general steps that you can follow to optimize PostgreSQL in a Debian server. The exact steps may vary depending on your specific hardware and workload. It is always a good idea to consult PostgreSQL documentation and seek expert advice when optimizing PostgreSQL for production environments.

create table public.user(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_active BOOLEAN NOT NULL default True
);

create table public.group(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table public.permission(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table public.usergroups(
user_id int references public.user(id),
group_id int references public.group(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(user_id, group_id)
);

create table public.userpermissions(
user_id int references public.user(id),
permission_id int references public.permission(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(user_id, permission_id)
);

create table public.grouppermissions(
group_id int references public.user(id),
permission_id int references public.permission(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(group_id, permission_id)
);

create table country(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table vat(
id serial primary key not null,
name varchar(200),
vat int not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table countryvat(
country_id int references country(id),
vat_id int references vat(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(country_id, vat_id)
);

create table customer(
id serial primary key not null,
country_id int references country(id),
vat_id int references vat(id),
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table customeraddress(
id serial primary key not null,
customer_id int references customer(id),
address varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table banner(
id serial primary key not null,
location int not null default 1,
code text null,
url text null,
banner_image text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table bannerstatistic(
id serial primary key not null,
banner_id int references banner(id),
session_id text null,
created_at timestamp not null default now()
);


create table appointment(
id serial primary key not null,
description text null,
start_at timestamp not null default now(),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table pressrelease(
id serial primary key not null,
description text null,
filepath text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table media(
id serial primary key not null,
media_path text not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table event(
id serial primary key not null,
name varchar(200),
description text null,
start_at timestamp not null default now(),
end_at timestamp not null default now(),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table eventmedia(
event_id int references event(id),
media_id int references media(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(event_id, media_id)
);


create table supplier(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table tag(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table brand(
id serial primary key not null,
name varchar(200),
logo_image text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table supplierbrand(
brand_id int references brand(id),
supplier_id int references supplier(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(brand_id, supplier_id)
);


create table category(
id serial primary key not null,
hero_image text null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table relatedcategory(
from_category_id int references category(id),
to_category_id int references category(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(from_category_id, to_category_id)
);

create table feature(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table featureattribute(
id serial primary key not null,
feature_id int references feature(id),
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table categoryfeature(
feature_id int references feature(id),
category_id int references feature(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(feature_id, category_id)
);


create table product(
id serial primary key not null,
brand_id int null references brand(id),
name varchar(200),
hero_image text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table relatedproduct(
from_product_id int references product(id),
to_product_id int references product(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(from_product_id, to_product_id)
);


create table productsupplier(
product_id int references product(id),
supplier_id int references supplier(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, supplier_id)
);

create table productprice(
product_id int references product(id),
vat_id int references vat(id),
country_id int references country(id),
supplier_id int references supplier(id),
price NUMERIC(6, 4) not null default 0,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_default BOOLEAN NOT NULL default True,
PRIMARY KEY(product_id, country_id,vat_id, supplier_id)
);

create table productcustomerprice(
product_id int references product(id),
vat_id int references vat(id),
country_id int references country(id),
customer_id int references customer(id),
price NUMERIC(6, 4) not null default 0,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_default BOOLEAN NOT NULL default True,
PRIMARY KEY(product_id, country_id, vat_id, customer_id)
);


create table productcategory(
product_id int references product(id),
category_id int references category(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, category_id)
);

create table producttag(
product_id int references product(id),
tag_id int references tag(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, tag_id)
);

create table productmedia(
product_id int references product(id),
media_id int references media(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, media_id)
);

create table productattribute(
product_id int references product(id),
feature_id int references feature(id),
featureattribute_id int references featureattribute(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, feature_id, featureattribute_id)
);

create table offer(
id serial primary key not null,
name varchar(200),
description text null,
start_at timestamp not null default now(),
end_at timestamp not null default now(),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table offerdetail(
product_id int references product(id),
offer_id int references offer(id),
price NUMERIC(6, 4) not null default 0,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, offer_id)
);

create table shoppingcartitem(
id serial primary key not null,
product_id int references product(id),
quantity int not null,
cart_id text not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table "order"(
id serial primary key not null,
total NUMERIC(6, 4) not null,
quantity int not null,
email text not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);


create table orderdetail(
order_id int references "order"(id),
product_id int references product(id),
quantity int not null,
price NUMERIC(6, 4) not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(order_id, product_id)
);