Historical design of the Lifenavi / AppGini production tables for the drama-scene library (pro_scene_repo), show segments (pro_segment), produced clips (pro_clip_repo), and the mapping rules between Show Parts (SP), Healing Stages (HS), Healing Functions (HF) and Narrative Themes (NT). Includes discussion of AppGini custom queries versus hooks for dynamic scene filtering.
Boundaries
The goal of this enhancement is to help manage a library of drama scenes (including metadata and video files), and assist in assigning scenes to the appropriate segments of a show. I’ve designed a few mapping rules between drama scenes and show segments, and I want Lifenavi to filter suitable scenes from the library so I can manually select one.
High-level user workflow:
Questions: What do you think of this overall design? What tables are needed to store the mapping rules and support auto-filtering of scenes by HS, HF, and NT simultaneously?
For table naming, the pro_ prefix refers to production-related tables in Lifenavi. I usually name mapping tables with the prefix rel_XXXX for clarity. Table structure should achieve the highest possible level of normalization while observing limitations in AppGini generated app!
pro_scene_repo (raw videos) and pro_clip_repo (produced clips) is logical, supporting your production pipeline.Below is the recommended table design, using the pro_ prefix for production tables and rel_ for mapping tables, achieving 3NF (third normal normal form) while adhering to AppGini’s capabilities.
pro_show_part (Show Parts)id (INT, Auto Increment, Primary Key)part_name (VARCHAR(50), Unique, e.g., "I. Find Joy", "II. Step Back", "III. Grow Within")description (TEXT, Optional)pro_healing_stage (Healing Stages)id (INT, Auto Increment, Primary Key)stage_name (VARCHAR(50), Unique, e.g., "Awareness", "Reflection", "Transformation")description (TEXT, Optional)pro_healing_function (Healing Functions)id (INT, Auto Increment, Primary Key)function_name (VARCHAR(50), Unique, e.g., "Calm", "Inspire")description (TEXT, Optional)pro_narrative_theme (Narrative Themes)id (INT, Auto Increment, Primary Key)theme_name (VARCHAR(50), Unique, e.g., "Growth", "Connection")description (TEXT, Optional)pro_scene_repo (Scene Library)id (INT, Auto Increment, Primary Key)title (VARCHAR(100), Not Null, e.g., scene title)function_id (INT, Foreign Key to pro_healing_function.id, Not Null)theme_id (INT, Foreign Key to pro_narrative_theme.id, Not Null)video_path (VARCHAR(255), Not Null, file storage path)duration (INT, Optional, in seconds)source (VARCHAR(100), Optional, e.g., movie name)notes (TEXT, Optional)pro_segment (Show Segments)id (INT, Auto Increment, Primary Key)episode_id (INT, Foreign Key to a future pro_episode.id, Not Null, if episodes are tracked)part_id (INT, Foreign Key to pro_show_part.id, Not Null)name (VARCHAR(100), Not Null, e.g., segment title)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)scene_id (INT, Foreign Key to pro_scene_repo.id, Nullable, for manual assignment)notes (TEXT, Optional)pro_clip_repo (Produced Clips)id (INT, Auto Increment, Primary Key)segment_id (INT, Foreign Key to pro_segment.id, Not Null)clip_path (VARCHAR(255), Not Null, file storage path)duration (INT, Optional, in seconds)status (VARCHAR(50), Optional, e.g., "Draft", "Finalized")notes (TEXT, Optional)rel_hs_hf (Healing Stage to Healing Function Mapping)id (INT, Auto Increment, Primary Key)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)function_id (INT, Foreign Key to pro_healing_function.id, Not Null)UNIQUE (stage_id, function_id)rel_hs_nt (Healing Stage to Narrative Theme Mapping)id (INT, Auto Increment, Primary Key)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)theme_id (INT, Foreign Key to pro_narrative_theme.id, Not Null)UNIQUE (stage_id, theme_id)pro_episode (Episodes)id (INT, Auto Increment, Primary Key)title (VARCHAR(100), Not Null)air_date (DATE, Optional)notes (TEXT, Optional)pro_segment.episode_id if needed.pro_segment by assigning part_id and stage_id. E.g., “I. Find Joy” might map to “Awareness,” “II. Step Back” to “Reflection,” “III. Grow Within” to “Transformation.”rel_hs_hf and rel_hs_nt, where each HS is linked to 2 HFs (e.g., “Calm,” “Inspire”) and 2 NTs (e.g., “Growth,” “Connection”) for flexibility.stage_id is selected in pro_segment, query rel_hs_hf and rel_hs_nt to retrieve the corresponding function_id and theme_id pairs.pro_scene_repo where function_id IN (HF1, HF2) AND theme_id IN (NT1, NT2), ordering by title.hooks/pro_segment.php) to:stage_id change, query rel_hs_hf and rel_hs_nt to update function_id and theme_id fields (hidden or read-only).scene_id dropdown with a SQL like: SELECT id, title FROM pro_scene_repo
WHERE function_id IN (SELECT function_id FROM rel_hs_hf WHERE stage_id = :stage_id)
AND theme_id IN (SELECT theme_id FROM rel_hs_nt WHERE stage_id = :stage_id)
ORDER BY titlerel_hs_hf, rel_hs_nt) to avoid redundancy, and foreign keys ensure data integrity.stage_id, function_id) for performance.pro_segment.part_id to pro_show_part.id) and adding the custom filter hook.pro_scene_repo, define segments in pro_segment, and test the HS-based filtering.AppGini provides a “Custom Query” option in its interface, accessible via the “View as” or “Quick Search” settings for a table (e.g., pro_segment). This feature allows you to define SQL queries to filter or display data dynamically, which can be linked to fields or user inputs. However, its functionality is somewhat limited compared to custom hooks:
SELECT statement to filter records based on conditions, and it supports parameters (e.g., :field_name) tied to other table fields or user selections.pro_scene_repo based on rel_hs_hf and rel_hs_nt simultaneously) unless the relationships are pre-defined and simple.stage_id to auto-update scene_id options) without additional scripting.Your workflow requires filtering pro_scene_repo scenes based on a stage_id selected in pro_segment, using mapping rules from rel_hs_hf and rel_hs_nt to identify relevant function_id and theme_id pairs. The desired SQL would look like:
SELECT id, title
FROM pro_scene_repo
WHERE function_id IN (SELECT function_id FROM rel_hs_hf WHERE stage_id = :stage_id)
AND theme_id IN (SELECT theme_id FROM rel_hs_nt WHERE stage_id = :stage_id)
ORDER BY titleIN clauses across two junction tables, which AppGini’s custom query can’t handle natively due to its reliance on single-table or pre-joined relationships.stage_id, then filter and order scenes for manual selection—requiring dynamic interaction beyond static queries.pro_segment’s “View as” to display filtered scenes, but it would need a fixed stage_id value (e.g., hardcoded or manually entered), not a dynamic one tied to the current record’s stage_id.SELECT s.id, s.title
FROM pro_scene_repo s
WHERE s.function_id IN (1, 2)
AND s.theme_id IN (3, 4)This works for a specific HS but can’t adapt to the user’s stage_id selection without manual adjustment, making it impractical for your workflow.
hooks/pro_segment.php) allow you to write server-side logic to handle complex queries and update the interface dynamically.dv (detail view) or tv (table view) hook to:stage_id change, query rel_hs_hf and rel_hs_nt to populate hidden fields with valid function_id and theme_id pairs.scene_id dropdown with a filtered list using the SQL above, parameterized with the current stage_id.hooks/pro_segment.php):function pro_segment_dv($selected_id, $memberInfo, &$html, &$args) {
if ($selected_id) {
$stage_id = sqlValue("SELECT stage_id FROM pro_segment WHERE id = '{$selected_id}'");
$functions = $this->db->query("SELECT function_id FROM rel_hs_hf WHERE stage_id = {$stage_id}")->fetch_all(MYSQLI_NUM);
$themes = $this->db->query("SELECT theme_id FROM rel_hs_nt WHERE stage_id = {$stage_id}")->fetch_all(MYSQLI_NUM);
$func_ids = implode(',', array_column($functions, 0));
$theme_ids = implode(',', array_column($themes, 0));
$scenes = $this->db->query("SELECT id, title FROM pro_scene_repo WHERE function_id IN ({$func_ids}) AND theme_id IN ({$theme_ids}) ORDER BY title")->fetch_all(MYSQLI_ASSOC);
// Update scene_id dropdown with $scenes
$html .= "<script>/* Populate dropdown logic */</script>";
}
}stage_id changes or handle the multi-table join logic required. A hook in hooks/pro_segment.php is the best approach to:stage_id changes.rel_hs_hf and rel_hs_nt to get HF and NT pairs.pro_scene_repo and populate the scene_id field (e.g., as a dropdown).stage_id in a custom query view and pre-filter scenes, but this defeats the workflow’s efficiency.The proposed tables (pro_show_part, pro_healing_stage, etc.) remain sufficient, as they support the hook-based filtering. Ensure indexes on stage_id, function_id, and theme_id in the junction tables for performance.
hooks/pro_segment.php, testing with sample data to ensure filtering works.pro_scene_repo, define segments in pro_segment, and verify the scene_id dropdown updates correctly.rel_ table to map Healing Stages (HS) to their most appropriate Show Part (SP) is a crucial addition to ensure the emotional journey aligns with the three-part structure (“I. Find Joy,” “II. Step Back,” “III. Grow Within”). This relationship was implied in the workflow but not explicitly defined as a separate table, which could limit flexibility if HS assignments need to evolve. Let’s update the table design to include this mapping table, maintaining normalization and AppGini compatibility.The existing tables remain solid, but I’ll add the new rel_sp_hs table to explicitly link Show Parts to Healing Stages, reflecting their appropriate emotional progression. Here’s the revised structure:
pro_show_part (Show Parts)id (INT, Auto Increment, Primary Key)part_name (VARCHAR(50), Unique, e.g., "I. Find Joy", "II. Step Back", "III. Grow Within")description (TEXT, Optional)pro_healing_stage (Healing Stages)id (INT, Auto Increment, Primary Key)stage_name (VARCHAR(50), Unique, e.g., "Awareness", "Inspiration", "Reflection", "Release", "Transformation", "Integration")description (TEXT, Optional)pro_healing_function (Healing Functions)id (INT, Auto Increment, Primary Key)function_name (VARCHAR(50), Unique, e.g., "Calm", "Inspire", "Empower", "Connect")description (TEXT, Optional)pro_narrative_theme (Narrative Themes)id (INT, Auto Increment, Primary Key)theme_name (VARCHAR(50), Unique, e.g., "Growth", "Connection", "Resilience", "Purpose")description (TEXT, Optional)pro_scene_repo (Scene Library)id (INT, Auto Increment, Primary Key)title (VARCHAR(100), Not Null)function_id (INT, Foreign Key to pro_healing_function.id, Not Null)theme_id (INT, Foreign Key to pro_narrative_theme.id, Not Null)video_path (VARCHAR(255), Not Null)duration (INT, Optional)source (VARCHAR(100), Optional)notes (TEXT, Optional)pro_segment (Show Segments)id (INT, Auto Increment, Primary Key)episode_id (INT, Foreign Key to a future pro_episode.id, Not Null, if episodes are tracked)part_id (INT, Foreign Key to pro_show_part.id, Not Null)name (VARCHAR(100), Not Null)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)scene_id (INT, Foreign Key to pro_scene_repo.id, Nullable)notes (TEXT, Optional)stage_id will now align with rel_sp_hs mappings.pro_clip_repo (Produced Clips)id (INT, Auto Increment, Primary Key)segment_id (INT, Foreign Key to pro_segment.id, Not Null)clip_path (VARCHAR(255), Not Null)duration (INT, Optional)status (VARCHAR(50), Optional)notes (TEXT, Optional)rel_hs_hf (Healing Stage to Healing Function Mapping)id (INT, Auto Increment, Primary Key)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)function_id (INT, Foreign Key to pro_healing_function.id, Not Null)UNIQUE (stage_id, function_id)rel_hs_nt (Healing Stage to Narrative Theme Mapping)id (INT, Auto Increment, Primary Key)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)theme_id (INT, Foreign Key to pro_narrative_theme.id, Not Null)UNIQUE (stage_id, theme_id)rel_sp_hs (Show Part to Healing Stage Mapping) [NEW]id (INT, Auto Increment, Primary Key)part_id (INT, Foreign Key to pro_show_part.id, Not Null)stage_id (INT, Foreign Key to pro_healing_stage.id, Not Null)UNIQUE (part_id, stage_id)part_id = 1 (“I. Find Joy”) → stage_id = 1 (“Awareness”), stage_id = 2 (“Inspiration”)part_id = 2 (“II. Step Back”) → stage_id = 3 (“Reflection”), stage_id = 4 (“Release”)part_id = 3 (“III. Grow Within”) → stage_id = 5 (“Transformation”), stage_id = 6 (“Integration”)rel_sp_hs, the link between SPs and HSs was assumed in the workflow but not enforced, risking misalignment (e.g., assigning “Transformation” to “I. Find Joy”).stage_id selection in pro_segment.Update the pro_segment_dv hook to consider rel_sp_hs:
function pro_segment_dv($selected_id, $memberInfo, &$html, &$args) {
if ($selected_id) {
$part_id = sqlValue("SELECT part_id FROM pro_segment WHERE id = '{$selected_id}'");
$stage_ids = $this->db->query("SELECT stage_id FROM rel_sp_hs WHERE part_id = {$part_id}")->fetch_all(MYSQLI_NUM);
$stage_id_list = implode(',', array_column($stage_ids, 0));
if ($stage_id_list) {
$functions = $this->db->query("SELECT function_id FROM rel_hs_hf WHERE stage_id IN ({$stage_id_list})")->fetch_all(MYSQLI_NUM);
$themes = $this->db->query("SELECT theme_id FROM rel_hs_nt WHERE stage_id IN ({$stage_id_list})")->fetch_all(MYSQLI_NUM);
$func_ids = implode(',', array_column($functions, 0));
$theme_ids = implode(',', array_column($themes, 0));
$scenes = $this->db->query("SELECT id, title FROM pro_scene_repo WHERE function_id IN ({$func_ids}) AND theme_id IN ({$theme_ids}) ORDER BY title")->fetch_all(MYSQLI_ASSOC);
// Populate stage_id dropdown with $stage_ids and scene_id with $scenes
$html .= "<script>/* JS to update dropdowns */</script>";
}
}
}part_id to fetch valid stage_id options from rel_sp_hs, then filters scenes accordingly.pro_segment, the interface can limit stage_id choices to those mapped to the selected part_id, ensuring alignment with the show’s structure.pro_scene_repo based on HS-derived HF and NT, maintaining your manual selection process.rel_sp_hs in AppGini, populate with the example mappings, and set the unique constraint.rel_sp_hs logic, testing with sample data.