Hotel Management postgresql basic schema

It is a basic  database schema for managing information related to companies, hotels, rooms, features, attributes, customers, and reservations. The SQL script defines the table structure for each entity along with their respective relationships and indexes. Below is a breakdown of the schema:

1. company table: Represents different companies and their information.

2. hotel table: Represents hotels and their information. It has a foreign key company_id that references the id field of the company table, establishing a relationship between hotels and their respective companies.

3. hotelimage table: Represents images associated with hotels. It has a foreign key hotel_id that references the id field of the hotel table.

4. room table: Represents rooms and their information. It has a foreign key hotel_id that references the id field of the hotel table, establishing a relationship between rooms and their respective hotels.

5. roomimage table: Represents images associated with rooms. It has a foreign key room_id that references the id field of the room table.

6. feature table: Represents features that a company may have. It has a foreign key company_id that references the id field of the company table, establishing a relationship between features and their respective companies.

7. attribute table: Represents attributes associated with features. It has a foreign key feature_id that references the id field of the feature table.

8. roomfeature table: Represents the relationship between rooms and features. It has foreign keys room_id and feature_id that reference the id fields of the room and feature tables, respectively.

9. roomattribute table: Represents the relationship between rooms and attributes. It has foreign keys room_id and attribute_id that reference the id fields of the room and attribute tables, respectively.

10. customer table: Represents customers and their information.

11. reservation table: Represents reservations made by customers for specific rooms. It has foreign keys room_id and customer_id that reference the id fields of the room and customer tables, respectively.

Each table has a primary key (id) to uniquely identify each record, and some tables have foreign keys to establish relationships between different entities. Indexes have been created on certain fields to improve query performance.

Overall, this schema seems to be suitable for managing the data related to companies, hotels, rooms, features, attributes, customers, and reservations. However, keep in mind that the specific needs of your application and the scale of your database may require further optimizations or changes.

              
                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);