Lesson 1Audit and Change Log table: change_id, element_id, change_type, timestamp, user, reasonDesign di Audit an Change Log table fi track element history, capturing who change what, when, an why, so analysts can reconstruct model states, detect anomalies, an support governance an 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 di Systems table fi represent logical an physical BIM systems, storing system_type, connected_elements, an performance_tags, enabling analysis of networks, capacities, an 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 di Issues an Clashes table fi record detected problems, related elements, clash types, severity, assignees, an status, enabling structured coordination workflows an performance reporting cross 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 di Core Elements table as di central BIM fact-like entity, capturing element identifiers, categories, families, types, an geometry references fi support joins wid costs, issues, assets, an 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 di Schedules an Phasing table fi capture planned_start, planned_end, an construction_status per element an phase, enabling time-based analytics, progress tracking, an 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 di Locations table fi standardize spatial data from BIM models, including buildings, wings, floors, rooms, an coordinates, enabling consistent spatial queries, rollups, an 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 di Costs table fi link financial data to BIM elements, storing unit_cost, quantity, total_cost, currency, an cost_source, enabling reliable cost estimation, benchmarking, an 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 di Assets an Maintenance table fi track operational data fi BIM elements, including manufacturer, serial numbers, warranties, an maintenance frequency, supporting lifecycle management an 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
