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, capturing who changed what, when, and why, so analysts can reconstruct model states, detect anomalies, and support governance and compliance needs.
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 to represent logical and physical BIM systems, storing system_type, connected_elements, and performance_tags, enabling analysis of networks, capacities, and system-level performance.
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 the Issues and Clashes table to record detected problems, related elements, clash types, severity, assignees, and status, enabling structured coordination workflows and performance reporting across 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 the Core Elements table as the central BIM fact-like entity, capturing element identifiers, categories, families, types, and geometry references to support joins with costs, issues, assets, and other analytical 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 the Schedules and Phasing table to capture planned_start, planned_end, and construction_status per element and phase, enabling time-based analytics, progress tracking, and 4D BIM reporting.
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 the Locations table to standardise spatial data from BIM models, including buildings, wings, floors, rooms, and coordinates, enabling consistent spatial queries, rollups, and cross-project location analytics.
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 the Costs table to link financial data to BIM elements, storing unit_cost, quantity, total_cost, currency, and cost_source, enabling reliable cost estimation, benchmarking, and budget variance analysis.
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 the Assets and Maintenance table to track operational data for BIM elements, including manufacturer, serial numbers, warranties, and maintenance frequency, supporting lifecycle management and facility operations.
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
