Lesson 1Audit and Change Log table: change_id, element_id, change_type, timestamp, user, reasonDesign the Audit and Change Log table to track element history, who changed what, when, why, so analysts reconstruct states, spot anomalies, support governance in Kenyan BIM projects.
Defining change_id and linking to element_idModeling change_type and lifecycle eventsTimestamp precision and time zone handlingCapturing user identity and change reasonsPartitioning and retention of large audit logsLesson 2Systems table: system_id, system_type, connected_elements, performance_tagsDefine the Systems table for logical/physical BIM systems, storing type, connected elements, performance tags, enabling network, capacity, system performance analysis in hospitals.
Designing system_id and system_type fieldsModeling connected_elements relationshipsStoring performance_tags and KPIsHandling nested and overlapping systemsQuerying systems for impact analysisLesson 3Issues and Clashes table: issue_id, element_ids, clash_type, severity, assigned_to, status, detection_dateDefine Issues and Clashes table for problems, elements, types, severity, assignees, status, enabling coordination workflows and performance reporting across Kenyan BIM projects.
Designing issue_id and element_ids linkageRepresenting clash_type and severity scalesTracking status, assigned_to, and due fieldsStoring detection_date and resolution datesIntegrating with clash detection toolsLesson 4Core Elements table: element_id, model_source, category, family/type, UUID, geometry_refSpecify Core Elements table as central BIM entity, capturing IDs, categories, families, types, geometry refs for joins with costs, issues, assets in analytics tables.
Choosing stable element_id and external keysStoring model_source and version metadataModeling category, family, and type attributesReferencing geometry via geometry_ref fieldsIndexing for fast element-level analyticsLesson 5Schedules/Phasing table: phase_id, element_id (FK), planned_start, planned_end, construction_statusSpecify Schedules/Phasing table for planned start/end, status per element/phase, enabling time analytics, progress tracking, 4D BIM reporting in Kenyan construction.
Defining phase_id and element_id foreign keysModeling planned_start and planned_end datesTracking construction_status and milestonesAligning phases with project calendarsLinking schedule data to external toolsLesson 6Locations table: location_id, building, wing, floor, room_id, coordinatesDefine Locations table to standardise spatial BIM data: buildings, wings, floors, rooms, coordinates for consistent queries, rollups, cross-project analytics in Kenya.
Primary keys and surrogate location_id strategyNormalizing building, wing, floor, and room codesStoring and indexing 2D and 3D coordinatesHandling multi-building and campus hierarchiesLinking locations to BIM views and gridsLesson 7Costs table: cost_id, element_id (FK), unit_cost, quantity, total_cost, currency, cost_sourceSpecify Costs table linking finance to BIM elements: unit cost, quantity, total, currency, source for reliable estimation, benchmarking, budget variance in projects.
Defining cost_id and element_id foreign keyModeling unit_cost, quantity, and total_costHandling multi-currency and exchange ratesCapturing cost_source and estimation methodsAggregating costs by location and systemLesson 8Assets and Maintenance table: asset_id, element_id (FK), manufacturer, serial, warranty_end, maintenance_frequencyDesign Assets/Maintenance table for operational BIM data: manufacturer, serials, warranties, frequency, supporting lifecycle management in Kenyan facilities.
Defining asset_id and element_id relationshipsCapturing manufacturer and model metadataStoring serial, warranty_end, and documentsModeling maintenance_frequency and tasksAligning assets with CMMS and CAFM systems
