Untitled
unknown
plain_text
4 months ago
8.9 kB
2
Indexable
CREATE TABLE program_v1 (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
program VARCHAR(255) NOT NULL, -- Program identifier or code
program_type_id INT NOT NULL, -- Foreign key referencing program_type_v1
name VARCHAR(255) NOT NULL, -- Name of the program
description TEXT, -- Description of the program
start_date DATE, -- Start date of the program
start_time TIME, -- Start time of the program
end_date DATE, -- End date of the program
end_time TIME, -- End time of the program
no_of_session INT, -- Number of sessions in the program
registration_start_date DATE, -- Registration start date
registration_start_time TIME, -- Registration start time
registration_end_date DATE, -- Registration end date
registration_end_time TIME, -- Registration end time
meta JSON, -- Additional metadata (stored in JSON format)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record is created
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Timestamp when the record is updated
);
ALTER TABLE program_v1
ADD CONSTRAINT fk_program_type
FOREIGN KEY (program_type_id) REFERENCES program_type_v1 (id);
-- Adding indexes
CREATE INDEX idx_program_type_id ON program_v1 (program_type_id);
CREATE INDEX idx_start_date ON program_v1 (start_date);
CREATE INDEX idx_registration_start_date ON program_v1 (registration_start_date);
CREATE INDEX idx_registration_end_date ON program_v1 (registration_end_date);
CREATE TABLE program_session (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key (automatically indexed)
program_id INT NOT NULL, -- Foreign key referencing program_v1
name VARCHAR(255) NOT NULL, -- Name of the session
description TEXT, -- Description of the session
start_date DATE, -- Start date of the session
start_time TIME, -- Start time of the session
end_date DATE, -- End date of the session
end_time TIME, -- End time of the session
registration_start_date DATE, -- Registration start date for the session
registration_start_time TIME, -- Registration start time for the session
registration_end_date DATE, -- Registration end date for the session
registration_end_time TIME, -- Registration end time for the session
meta JSON, -- Additional metadata (stored in JSON format)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record is created
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Timestamp when the record is updated
);
-- Adding foreign key constraint for program_id
ALTER TABLE program_session
ADD CONSTRAINT fk_program
FOREIGN KEY (program_id) REFERENCES program_v1 (id);
-- Adding explicit index on id (redundant due to PRIMARY KEY but included for clarity)
CREATE INDEX idx_id ON program_session (id);
CREATE TABLE webinar (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key (automatically indexed)
title VARCHAR(255) NOT NULL, -- Title of the meeting
program_session_id INT NOT NULL, -- Foreign key referencing program_session
webinar_id_ext VARCHAR(255), -- External meeting ID (e.g., Zoom/Teams ID)
password VARCHAR(255), -- Meeting password
start_date DATE, -- Start date of the meeting
start_url TEXT, -- URL to start the meeting
duration INT, -- Duration of the meeting in minutes
end_date DATE, -- End date of the meeting
started_at TIMESTAMP, -- Timestamp when the meeting started
started_by VARCHAR(255), -- User who started the meeting
created_by VARCHAR(255), -- User who created the meeting
registration_starts_at TIMESTAMP, -- Registration start timestamp
registration_ends_at TIMESTAMP, -- Registration end timestamp
meta JSON, -- Additional metadata (stored in JSON format)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record is created
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Timestamp when the record is updated
);
-- Adding foreign key constraint for program_session_id
ALTER TABLE webinar
ADD CONSTRAINT fk_program_session
FOREIGN KEY (program_session_id) REFERENCES program_session (id);
-- Adding explicit index on id (redundant due to PRIMARY KEY but included for clarity)
CREATE INDEX idx_id ON webinar (id);
INSERT INTO program_type_v1 (program_type, name, description)
VALUES ('HDB', 'Hyper Deeper Beyond', 'A personality development program');
INSERT INTO program_v1 (program, program_type_id, name, description, start_date, start_time, end_date, end_time, no_of_session, registration_start_date, registration_start_time, registration_end_date, registration_end_time, meta)
VALUES (
'P001',
1,
'HDB Program',
'An introductory program for personal development',
'2024-12-20',
'09:00:00',
'2024-12-21',
'17:00:00',
2,
'2024-12-10',
'08:00:00',
'2024-12-19',
'20:00:00',
'{"difficulty": "beginner"}'
);
INSERT INTO program_session (program_id, name, description, start_date, start_time, end_date, end_time, registration_start_date, registration_start_time, registration_end_date, registration_end_time, meta)
VALUES (
1,
'Session 1: Introduction',
'First session focusing on fundamentals of personal development',
'2024-12-20',
'09:00:00',
'2024-12-20',
'12:00:00',
'2024-12-10',
'08:00:00',
'2024-12-19',
'20:00:00',
'{"focus_area": "self-awareness"}'
);
select * from users
select * from webinar
alter table webinar add column deleted_at date
alter table webinar add column max_video_limit integer
alter table webinar add column max_non_video_limit integer
alter table webinar rename column started_at to start_at
select * from public.infini_path_meeting_attendee
CREATE TABLE webinar_attendee (
id SERIAL PRIMARY KEY,
webinar_id INT NOT NULL,
user_id INT NOT NULL,
related_user_id INT,
mode_of_joining VARCHAR(50),
face_verification_status BOOLEAN DEFAULT FALSE,
attendance_time TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- Foreign key constraints
CONSTRAINT fk_webinar_attendee_webinar
FOREIGN KEY (webinar_id) REFERENCES webinar(id) ON DELETE CASCADE,
CONSTRAINT fk_webinar_attendee_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT fk_webinar_attendee_related_user
FOREIGN KEY (related_user_id) REFERENCES users(id) ON DELETE SET NULL
);
-- Indexes for improved query performance
CREATE INDEX idx_webinar_attendee_webinar_id ON webinar_attendee (webinar_id);
CREATE INDEX idx_webinar_attendee_user_id ON webinar_attendee (user_id);
CREATE INDEX idx_webinar_attendee_related_user_id ON webinar_attendee (related_user_id);
CREATE INDEX idx_webinar_attendee_mode_of_joining ON webinar_attendee (mode_of_joining);
select * from public.webinar_attendee
select * from public.infini_path_user_meeting
CREATE TABLE webinar_registration (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
webinar_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
join_url VARCHAR(255),
is_panelist BOOLEAN DEFAULT FALSE,
registration_id_ext VARCHAR(255),
-- Foreign key constraints
CONSTRAINT fk_webinar_registration_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT fk_webinar_registration_webinar
FOREIGN KEY (webinar_id) REFERENCES webinar(id) ON DELETE CASCADE
);
-- Indexes for optimization
CREATE INDEX idx_webinar_registration_user_id ON webinar_registration (user_id);
CREATE INDEX idx_webinar_registration_webinar_id ON webinar_registration (webinar_id);
CREATE INDEX idx_webinar_registration_registration_id_ext ON webinar_registration (registration_id_ext);
select * from public.webinar_registrationEditor is loading...
Leave a Comment