Lesson 1Audit and Change Log table: change_id, element_id, change_type, timestamp, user, reasonPlan the Audit and Change Log table to follow part history, noting who changed what, when, why, so analysts rebuild states, spot oddities, back rules and compliance.
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_tagsSet the Systems table for logic and real BIM systems, holding type, linked parts, performance tags, aiding network, capacity, system performance analysis.
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_dateSet Issues and Clashes table for found problems, linked parts, clash kinds, weight, assignees, state, find date, for structured teamwork and performance reports.
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 main BIM fact hub, grabbing IDs, groups, families, types, shape refs for joins with costs, issues, assets, other analysis 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 and Phasing table for planned start, end, build state per part and phase, for time analysis, progress follow, 4D BIM reports.
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, coordinatesSet Locations table to standard space data from BIM, with builds, wings, floors, rooms, coords for steady space queries, sums, cross-project analysis.
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 money data to BIM parts, holding unit cost, amount, total, money type, source for solid cost guesses, checks, budget gaps.
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_frequencyPlan Assets and Maintenance table for running data on BIM parts, with maker, serials, guarantees, upkeep rate, backing life manage and facility work.
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
