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_registration
Editor is loading...
Leave a Comment