MyEMS Database Design: 13 Databases
Table of Contents
- Database architecture design
- Detailed database description
- 1. System Database (myems_system_db)
- 2. Historical Database (myems_historical_db)
- 3. Energy Database (myems_energy_db)
- 4. Billing Database (myems_billing_db)
- 5. Carbon Database (myems_carbon_db)
- 6. Energy Baseline Database (myems_energy_baseline_db)
- 7. Energy Model Database (myems_energy_model_db)
- 8. Energy Plan Database (myems_energy_plan_db)
- 9. Energy Prediction Database (myems_energy_prediction_db)
- 10. FDD Database (myems_fdd_db)
- 11. User Database (myems_user_db)
- 12. Reporting Database (myems_reporting_db)
- 13. Production Database (myems_production_db)
- Data flow relationship
- Table structure design specification
Database architecture design
MyEMS uses 13 MySQL databases. Install scripts live in database/install. Each script creates one database with utf8mb4 / utf8mb4_unicode_ci. Tables use BIGINT primary keys, UTC DATETIME columns, and DECIMAL(21, 6) for measured and aggregated values.
| Database | Tables | Purpose |
|---|---|---|
myems_system_db | 166 | Master data: spaces, meters, points, equipment, plants, tariffs, and associations |
myems_historical_db | 12 | Raw point values and uploaded cost, offline-meter, repair, and plan files |
myems_energy_db | 39 | Hourly energy aggregation |
myems_billing_db | 39 | Hourly cost aggregation, same table set as energy |
myems_carbon_db | 39 | Hourly carbon aggregation, same table set as energy |
myems_energy_baseline_db | 39 | Hourly baseline, same table set as energy |
myems_energy_model_db | 39 | 8,760-hour typical year, same object set as energy |
myems_energy_plan_db | 39 | Hourly plan, same table set as energy |
myems_energy_prediction_db | 39 | Hourly prediction, same table set as energy |
myems_fdd_db | 9 | Fault rules and email, SMS, web, and WeChat messages |
myems_user_db | 10 | Users, privileges, sessions, API keys, and audit logs |
myems_reporting_db | 4 | Report definitions, templates, generated files, and report emails |
myems_production_db | 8 | Products, teams, shifts, and hourly production counts |
Design concept
| Database | Responsibility | Writer |
|---|---|---|
myems_system_db | Configuration and topology | Web UI through myems-api |
myems_historical_db | Point time series and uploaded workbooks | Data acquisition and file import |
myems_energy_db | Hourly energy | Normalization and aggregation |
myems_billing_db | Hourly cost | Billing, from energy values and tariffs |
myems_carbon_db | Hourly emissions | Carbon calculation, from energy values and emission factors |
myems_energy_baseline_db | Hourly baseline | Baseline service |
myems_energy_model_db | One value for each hour of a non-leap year | Modeling service |
myems_energy_plan_db | Hourly plan | Planning service |
myems_energy_prediction_db | Hourly forecast | Prediction service |
myems_fdd_db | Rules and outbound notifications | FDD service |
myems_user_db | Identity and privileges | API |
myems_reporting_db | Scheduled reports | Reporting service |
myems_production_db | Output counts used with energy intensity | Production import and aggregation |
Configuration objects reference each other by id. Cross-database tables store that same id and do not declare foreign keys to another database.
Naming conventions
| Object | Rule | Example |
|---|---|---|
| Database | myems_ prefix, _db suffix | myems_system_db |
| Table | tbl_ prefix | tbl_meters |
| Association table | tbl_{owner}_{child} | tbl_spaces_meters |
| Hourly fact table | {object}_{measure}_hourly | tbl_meter_hourly |
| Model fact table | same object set, _8760 suffix | tbl_meter_8760 |
| Primary key | id | id BIGINT NOT NULL AUTO_INCREMENT |
| Business key | uuid CHAR(36) on configuration objects | meter, space, equipment |
| Time | *_datetime_utc or utc_date_time | start_datetime_utc |
| Index | {table}_index_{n} | tbl_meters_index_1 |
Detailed database description
1. System Database (myems_system_db)
myems_system_db holds master data. It has 166 tables. Configuration objects usually carry id, name, uuid, and description. Association tables only store the two foreign keys (plus id). Component tables under a plant store one row per physical device and a companion *_points table.
Energy classification
| Table | Key fields | Description |
|---|---|---|
tbl_energy_categories | name, uuid, unit_of_measure, kgce, kgco2e | Energy category and default conversion factors |
tbl_energy_items | name, uuid, energy_category_id | Item under a category |
kgce and kgco2e are default factors on the category. Time-bounded prices and emission factors live in the tariff and emission-factor tables.
Meters and points
| Table | Key fields | Description |
|---|---|---|
tbl_meters | energy_category_id, energy_item_id, cost_center_id, master_meter_id, is_counted, is_enabled, hourly_low_limit, hourly_high_limit | Online meter |
tbl_offline_meters | same limits and category fields, no master_meter_id | Manually entered meter |
tbl_virtual_meters | equation (LONGTEXT), energy_category_id, energy_item_id, is_counted, is_enabled | Meter calculated from an equation |
tbl_variables | name, virtual_meter_id, meter_type, meter_id | Variable binding inside a virtual-meter equation |
tbl_points | data_source_id, object_type, units, high_limit, low_limit, higher_limit, lower_limit, ratio, offset_constant, is_trend, is_virtual, address, faults, definitions | Data point. address, faults, and definitions are JSON text. Points have no uuid |
tbl_points_set_values | point_id, utc_date_time, set_value, is_set | Commanded set value for a point |
tbl_meters_points | meter_id, point_id | Meter-to-point binding |
tbl_meters_commands | meter_id, command_id | Meter-to-command binding |
higher_limit, lower_limit, and is_in_alarm on tbl_points are used by the FDD service. ratio and offset_constant scale the raw value.
Data acquisition
| Table | Key fields | Description |
|---|---|---|
tbl_gateways | name, uuid, token, last_seen_datetime_utc | Acquisition gateway |
tbl_data_sources | gateway_id, protocol, connection (LONGTEXT), process_id, last_seen_datetime_utc | Connection under a gateway. connection is JSON |
tbl_protocols | name, code | Protocol catalog |
tbl_sensors | name, uuid | Sensor |
tbl_sensors_points | sensor_id, point_id | Sensor-to-point binding |
tbl_iot_sim_cards | iccid, imsi, operator, status, used_traffic, total_traffic | SIM card inventory. This table has no uuid |
Spaces and cost centers
| Table | Key fields | Description |
|---|---|---|
tbl_spaces | parent_space_id, area, number_of_occupants, timezone_id, contact_id, cost_center_id, latitude, longitude, is_input_counted, is_output_counted | Space tree |
tbl_cost_centers | name, uuid, external_id | Cost center |
tbl_contacts | name, uuid, email, phone | Contact |
tbl_timezones | name, utc_offset | Time zone |
tbl_working_calendars | name | Working calendar |
tbl_working_calendars_non_working_days | working_calendar_id, date_local, description | Non-working day |
A space links to child objects through association tables, not through a space_id column on those objects:
tbl_spaces_meters, tbl_spaces_offline_meters, tbl_spaces_virtual_meters, tbl_spaces_points, tbl_spaces_sensors, tbl_spaces_equipments, tbl_spaces_combined_equipments, tbl_spaces_commands, tbl_spaces_shopfloors, tbl_spaces_stores, tbl_spaces_tenants, tbl_spaces_charging_stations, tbl_spaces_microgrids, tbl_spaces_photovoltaic_power_stations, tbl_spaces_energy_storage_power_stations, tbl_spaces_wind_farms, tbl_spaces_distribution_systems, tbl_spaces_energy_flow_diagrams, tbl_spaces_working_calendars.
Equipment
| Table | Key fields | Description |
|---|---|---|
tbl_equipments | is_input_counted, is_output_counted, is_enabled, cost_center_id, efficiency_indicator, svg_id, camera_url | Equipment |
tbl_combined_equipments | same fields as tbl_equipments | Combined equipment |
tbl_combined_equipments_equipments | combined_equipment_id, equipment_id | Members of a combined equipment |
Both equipment types bind meters, offline meters, virtual meters, parameters, data sources, and commands:
tbl_equipments_meters,tbl_equipments_offline_meters,tbl_equipments_virtual_meters,tbl_equipments_parameters,tbl_equipments_data_sources,tbl_equipments_commandstbl_combined_equipments_meters,tbl_combined_equipments_offline_meters,tbl_combined_equipments_virtual_meters,tbl_combined_equipments_parameters,tbl_combined_equipments_data_sources,tbl_combined_equipments_commands
Shopfloors, stores, and tenants
| Table | Key fields | Description |
|---|---|---|
tbl_shopfloors | area, is_input_counted, is_enabled, contact_id, cost_center_id | Shopfloor |
tbl_stores | address, latitude, longitude, area, store_type_id, is_input_counted, is_enabled, contact_id, cost_center_id | Store |
tbl_store_types | name, uuid, simplified_code | Store type |
tbl_tenants | buildings, floors, rooms, area, tenant_type_id, is_input_counted, is_enabled, is_key_tenant, lease_number, lease_start_datetime_utc, lease_end_datetime_utc, is_in_lease, contact_id, cost_center_id | Tenant |
tbl_tenant_types | name, uuid, simplified_code | Tenant type |
Each of these three objects has association tables for meters, offline meters, virtual meters, points, sensors, commands, and working calendars. Shopfloors also have tbl_shopfloors_equipments.
Photovoltaic power stations
tbl_photovoltaic_power_stations stores station_code, address, latitude, longitude, latitude_point_id, longitude_point_id, rated_capacity, rated_power, contact_id, cost_center_id, five SVG slots (svg_id through svg5_id), is_cost_data_displayed, phase_of_lifecycle, and commissioning_date.
Child tables:
| Table | Role |
|---|---|
tbl_photovoltaic_power_stations_invertors / _invertors_points | Inverters and their points |
tbl_photovoltaic_power_stations_grids / _grids_points | Grid connections and their points |
tbl_photovoltaic_power_stations_loads / _loads_points | Loads and their points |
tbl_photovoltaic_power_stations_data_sources | Data sources |
tbl_photovoltaic_power_stations_users | Bound users |
Energy storage
tbl_energy_storage_containers stores rated_capacity, rated_power, contact_id, and cost_center_id. Component tables, each with a companion *_points table where listed:
| Component table | Points table |
|---|---|
tbl_energy_storage_containers_batteries (soc_point_id, power_point_id, charge_meter_id, discharge_meter_id) | — |
tbl_energy_storage_containers_power_conversion_systems | tbl_energy_storage_containers_pcses_points |
| — | tbl_energy_storage_containers_bmses_points (bms_id, point_id; no BMS master table) |
tbl_energy_storage_containers_dcdcs | tbl_energy_storage_containers_dcdcs_points |
tbl_energy_storage_containers_stses | tbl_energy_storage_containers_stses_points |
tbl_energy_storage_containers_grids | tbl_energy_storage_containers_grids_points |
tbl_energy_storage_containers_loads | tbl_energy_storage_containers_loads_points |
tbl_energy_storage_containers_hvacs | tbl_energy_storage_containers_hvacs_points |
tbl_energy_storage_containers_firecontrols | tbl_energy_storage_containers_firecontrols_points |
Also: tbl_energy_storage_containers_commands, tbl_energy_storage_containers_data_sources, tbl_energy_storage_containers_schedules.
tbl_energy_storage_power_stations uses the same station fields as a photovoltaic station (address, coordinates, rated capacity and power, contact, cost center, five SVGs, lifecycle). It groups containers in tbl_energy_storage_power_stations_containers and users in tbl_energy_storage_power_stations_users.
Microgrids
tbl_microgrids adds postal_code and serial_number to the station field set. Child tables:
| Component | Points |
|---|---|
tbl_microgrids_batteries | — |
| — | tbl_microgrids_bmses_points (bms_id, point_id; no BMS master table) |
tbl_microgrids_power_conversion_systems | tbl_microgrids_pcses_points |
tbl_microgrids_photovoltaics | tbl_microgrids_pvs_points |
tbl_microgrids_evchargers | tbl_microgrids_evchargers_points |
tbl_microgrids_generators | tbl_microgrids_generators_points |
tbl_microgrids_grids | tbl_microgrids_grids_points |
tbl_microgrids_heatpumps | tbl_microgrids_heatpumps_points |
tbl_microgrids_loads | tbl_microgrids_loads_points |
Also: tbl_microgrids_commands, tbl_microgrids_data_sources, tbl_microgrids_schedules, tbl_microgrids_sensors, tbl_microgrids_users.
Other plants and integrators
| Table | Key fields | Description |
|---|---|---|
tbl_charging_stations | address, coordinates, rated_capacity, rated_power, contact, cost center, five SVGs, lifecycle | Charging station |
tbl_wind_farms | address, coordinates, rated_power, contact, cost center, five SVGs | Wind farm |
tbl_virtual_power_plants | cost_center_id, balancing_price_point_id, svg_id | Virtual power plant |
tbl_virtual_power_plants_microgrids | virtual_power_plant_id, microgrid_id | Microgrids in a virtual power plant |
tbl_power_integrators | power_point_id, result_point_id, is_enabled | Integrate power into energy |
tbl_fuel_integrators | model, power_point_id, result_point_id, is_enabled | Fuel integrator |
tbl_heat_integrators | high_temperature_point_id, low_temperature_point_id, flow_point_id, heat_capacity, liquid_density, coefficient, result_point_id, is_enabled | Heat integrator |
Commands and control
| Table | Key fields | Description |
|---|---|---|
tbl_commands | topic, payload (LONGTEXT), set_value | Command definition |
tbl_control_modes | name, uuid, is_active | Control mode |
tbl_control_modes_times | control_mode_id, start_time_of_day, end_time_of_day, power_value, power_point_id, power_equation | One period inside a control mode |
Tariffs and emission factors
| Table | Key fields | Description |
|---|---|---|
tbl_tariffs | energy_category_id, tariff_type, unit_of_price, valid_from_datetime_utc, valid_through_datetime_utc | Tariff. tariff_type is timeofuse |
tbl_tariffs_timeofuses | tariff_id, start_time_of_day, end_time_of_day, peak_type, price DECIMAL(23, 8) | Price of one daily period. peak_type is toppeak, onpeak, midpeak, offpeak, or deep |
tbl_cost_centers_tariffs | cost_center_id, tariff_id | Tariff binding |
tbl_emission_factors | energy_category_id, unit_of_factor, valid_from_datetime_utc, valid_through_datetime_utc | Time-bounded emission factor |
tbl_emission_factors_timeofuses | emission_factor_id, start_time_of_day, end_time_of_day, factor | Factor of one daily period |
tbl_cost_centers_emission_factors | cost_center_id, emission_factor_id | Factor binding |
Diagrams, menus, files, and work orders
| Table | Key fields | Description |
|---|---|---|
tbl_distribution_systems | name, uuid, svg_id | Distribution system |
tbl_distribution_circuits | distribution_system_id, distribution_room, switchgear, peak_load, peak_current, customers, meters | Circuit |
tbl_distribution_circuits_points | distribution_circuit_id, point_id | Circuit points |
tbl_energy_flow_diagrams | name, uuid | Energy-flow diagram |
tbl_energy_flow_diagrams_nodes | energy_flow_diagram_id, name | Node |
tbl_energy_flow_diagrams_links | energy_flow_diagram_id, source_node_id, target_node_id, meter_uuid | Link |
tbl_svgs | name, uuid, source_code (LONGTEXT) | SVG graphic |
tbl_menus | name, route, parent_menu_id, is_hidden | Menu tree. No uuid |
tbl_knowledge_files | file_name, uuid, upload_datetime_utc, upload_user_uuid, file_object (LONGBLOB) | Knowledge-base file |
tbl_work_orders | number, category, title, priority, status, belong_to_object_type, belong_to_object_id, source_type, source_id, creator_user_id, assignee_user_id, scheduled and actual timestamps | Work order |
tbl_work_order_logs | work_order_id, user_id, action, from_status, to_status, comment, created_datetime_utc | Status log |
tbl_work_order_files | work_order_id, file_name, file_path, file_size, uploaded_by_user_id | Attachment path. File bytes are stored on disk |
tbl_versions | version, release_date | Schema version |
2. Historical Database (myems_historical_db)
myems_historical_db stores raw point values and uploaded workbooks. It has 12 tables.
Each value family has a history table and a latest table. History rows include is_bad and is_published. Latest rows keep point_id, utc_date_time, and actual_value only. Both are indexed by (point_id, utc_date_time) and by utc_date_time.
| Table | actual_value type | Description |
|---|---|---|
tbl_analog_value, tbl_analog_value_latest | DECIMAL(21, 6) | Analog |
tbl_energy_value, tbl_energy_value_latest | DECIMAL(21, 6) | Cumulative energy |
tbl_digital_value, tbl_digital_value_latest | INT | Digital |
tbl_text_value, tbl_text_value_latest | LONGTEXT | Text |
Uploaded files share file_name, uuid, upload_datetime_utc, status (new, done, error), and file_object (LONGBLOB):
| Table | Purpose |
|---|---|
tbl_cost_files | Cost workbook |
tbl_offline_meter_files | Offline-meter workbook |
tbl_data_repair_files | Data-repair workbook |
tbl_energy_plan_files | Energy-plan workbook |
3. Energy Database (myems_energy_db)
myems_energy_db stores hourly actual energy. It has 39 tables and no daily, monthly, or yearly tables. Reports roll hours up in queries.
Every table has id, a parent object id, start_datetime_utc, and actual_value DECIMAL(21, 6). Category tables also have energy_category_id. Item tables also have energy_item_id. The composite index is (object_id, [category or item id], start_datetime_utc).
| Group | Tables |
|---|---|
| Meters | tbl_meter_hourly, tbl_offline_meter_hourly, tbl_virtual_meter_hourly |
| Equipment | tbl_equipment_input_category_hourly, tbl_equipment_input_item_hourly, tbl_equipment_output_category_hourly |
| Combined equipment | tbl_combined_equipment_input_category_hourly, tbl_combined_equipment_input_item_hourly, tbl_combined_equipment_output_category_hourly |
| Space | tbl_space_input_category_hourly, tbl_space_input_item_hourly, tbl_space_output_category_hourly |
| Shopfloor | tbl_shopfloor_input_category_hourly, tbl_shopfloor_input_item_hourly |
| Store | tbl_store_input_category_hourly, tbl_store_input_item_hourly |
| Tenant | tbl_tenant_input_category_hourly, tbl_tenant_input_item_hourly |
| Energy storage container | tbl_energy_storage_container_{charge,discharge,grid_buy,grid_sell,load}_hourly |
| Energy storage power station | tbl_energy_storage_power_station_{charge,discharge,grid_buy,grid_sell,load}_hourly |
| Microgrid | tbl_microgrid_{charge,discharge,evcharger,grid_buy,grid_sell,load,photovoltaic}_hourly |
| Photovoltaic power station | tbl_photovoltaic_power_station_{generation,grid_buy,grid_sell,load}_hourly |
Meter, offline-meter, virtual-meter, storage, microgrid, and photovoltaic tables have no energy-category column. The category is implied by the parent object.
4. Billing Database (myems_billing_db)
myems_billing_db has the same 39 hourly tables and the same columns as myems_energy_db. actual_value is cost in the tariff currency. Billing reads hourly energy and the tariff bound to the cost center.
5. Carbon Database (myems_carbon_db)
myems_carbon_db has the same 39 hourly tables. actual_value is emissions. Calculation uses hourly energy together with tbl_energy_categories.kgco2e or the time-of-use rows in tbl_emission_factors.
6. Energy Baseline Database (myems_energy_baseline_db)
myems_energy_baseline_db has the same 39 hourly tables. actual_value is the baseline for that hour.
7. Energy Model Database (myems_energy_model_db)
myems_energy_model_db has the same 39 object groups. Table names end in _8760 instead of _hourly, and the time column is hour_of_year (INT) instead of start_datetime_utc. Example: tbl_meter_8760(meter_id, hour_of_year, actual_value).
8. Energy Plan Database (myems_energy_plan_db)
myems_energy_plan_db has the same 39 hourly tables as myems_energy_db. actual_value is the planned quantity. Plan workbooks land first in myems_historical_db.tbl_energy_plan_files.
9. Energy Prediction Database (myems_energy_prediction_db)
myems_energy_prediction_db has the same 39 hourly tables. actual_value is the predicted quantity.
10. FDD Database (myems_fdd_db)
myems_fdd_db has 9 tables.
| Table | Key fields | Description |
|---|---|---|
tbl_rules | name, uuid, category, fdd_code, priority, channel, expression, message_template, is_enabled, last_run_datetime_utc, next_run_datetime_utc, is_run_immediately | Fault rule. expression is JSON. message_template is plain text with $ substitutions |
tbl_email_servers | host, port, requires_authentication, user_name, password, from_addr | SMTP server |
tbl_email_messages | rule_id, recipient_email, subject, message, attachment_file_object, scheduled_datetime_utc, status | Outbound email |
tbl_text_messages_outbox | rule_id, recipient_mobile, message, acknowledge_code, status | Outbound SMS |
tbl_text_messages_inbox | sender_mobile, message, received_datetime_utc, status | Inbound SMS |
tbl_web_messages | rule_id, user_id, subject, category, priority, message, status, reply, belong_to_object_type, belong_to_object_id | Web message |
tbl_wechat_configs | api_server, app_id, app_secret, access_token, expires_datetime_utc | WeChat app |
tbl_wechat_messages_outbox | rule_id, recipient_openid, message_template_id, message_data, status | Outbound WeChat |
tbl_wechat_messages_inbox | sender_openid, message, received_datetime_utc, status | Inbound WeChat |
tbl_rules.category is one of REALTIME, SYSTEM, SPACE, METER, TENANT, STORE, SHOPFLOOR, EQUIPMENT, COMBINEDEQUIPMENT. priority is CRITICAL, HIGH, MEDIUM, or LOW. channel is WEB, EMAIL, SMS, WECHAT, or CALL. fdd_code identifies the rule, for example REALTIME01 or SPACE02.
11. User Database (myems_user_db)
myems_user_db has 10 tables.
| Table | Key fields | Description |
|---|---|---|
tbl_users | name, uuid, display_name, email, phone, salt, password, is_admin, is_read_only, privilege_id, account_expiration_datetime_utc, password_expiration_datetime_utc, failed_login_count | User. password is the salted hash (VARCHAR(256)) |
tbl_privileges | name, data (LONGTEXT, JSON) | Privilege set |
tbl_sessions | user_uuid, token, utc_expires | Login session |
tbl_logs | user_uuid, request_datetime_utc, request_method, resource_type, resource_id, request_body | API audit log. request_body is JSON |
tbl_api_keys | name, token, created_datetime_utc, expires_datetime_utc | API key. id is INT |
tbl_new_users | name, uuid, display_name, email, salt, password | Pending registration |
tbl_email_messages | recipient_email, subject, message, scheduled_datetime_utc, status | User mail such as password reset. status is new, sent, or timeout |
tbl_email_message_sessions | recipient_email, token, expires_datetime_utc | Token for a mail action |
tbl_verification_codes | recipient_email, verification_code, created_datetime_utc, expires_datetime_utc | Email verification code. id is INT |
tbl_notifications | user_id, created_datetime_utc, status, subject, message, url | In-app notification. status is unread, read, or archived |
12. Reporting Database (myems_reporting_db)
myems_reporting_db has 4 tables.
| Table | Key fields | Description |
|---|---|---|
tbl_reports | name, uuid, expression, is_enabled, last_run_datetime_utc, next_run_datetime_utc, is_run_immediately | Report schedule. expression is JSON |
tbl_template_files | uuid, report_id, file_name, file_type, file_object | Template bound to a report |
tbl_reports_files | uuid, create_datetime_utc, file_name, file_type, file_object | Generated file |
tbl_email_messages | recipient_email, subject, message, attachment_file_object, scheduled_datetime_utc, status | Report delivery mail. This table has no rule_id |
13. Production Database (myems_production_db)
myems_production_db has 8 tables. Hourly product counts share start_datetime_utc, product_id, and product_count.
| Table | Key fields | Description |
|---|---|---|
tbl_products | name, uuid, unit_of_measure, tag, standard_product_coefficient | Product |
tbl_teams | name, uuid, description | Team |
tbl_shopfloors_products | shopfloor_id, product_id | Products made on a shopfloor |
tbl_shopfloors_teams | shopfloor_id, team_id | Teams on a shopfloor |
tbl_shifts | shopfloor_id, team_id, product_id, product_count, start_datetime_utc, end_datetime_utc, reference_timestamp | Shift record |
tbl_equipment_hourly | equipment_id, start_datetime_utc, product_id, product_count | Hourly output of one equipment |
tbl_shopfloor_hourly | shopfloor_id, start_datetime_utc, product_id, product_count | Hourly output of a shopfloor |
tbl_space_hourly | space_id, start_datetime_utc, product_id, product_count | Hourly output rolled up to a space |
Data flow relationship
Processing order:
- Gateways write point values into
myems_historical_db. Uploaded workbooks land in the file tables of the same database. - Normalization turns point values into meter readings, using
ratioandoffset_constantfromtbl_points. - Aggregation writes hourly rows in
myems_energy_dbfor meters and for each parent object (equipment, space, store, tenant, shopfloor, storage, microgrid, photovoltaic power station). - Billing multiplies those hours by
tbl_tariffs/tbl_tariffs_timeofusesand writesmyems_billing_db. - Carbon multiplies those hours by
kgco2eortbl_emission_factorsand writesmyems_carbon_db. - Baseline, model, plan, and prediction services write their own databases. The model database stores
hour_of_yearinstead of a timestamp. - FDD evaluates
tbl_rules.expressionagainst point limits and aggregated values, then enqueues email, SMS, web, or WeChat messages. - Reporting reads energy, billing, and carbon to fill
tbl_template_filesand stores the result intbl_reports_files. - Production hourly counts in
myems_production_dbare the denominator for energy intensity.
Table structure design specification
Common fields
Configuration tables in myems_system_db usually include:
| Field | Type | Description |
|---|---|---|
id | BIGINT NOT NULL AUTO_INCREMENT | Primary key |
name | VARCHAR(128) or VARCHAR(255) | Display name |
uuid | CHAR(36) NOT NULL | Stable business key |
description | VARCHAR(255) | Optional description |
Time-series and association tables do not follow that set. Fact tables use id plus foreign keys and actual_value. Many association tables are only id and two foreign keys. tbl_points, tbl_menus, and tbl_iot_sim_cards have no uuid.
Data types
| Use | Type |
|---|---|
| Primary and foreign keys | BIGINT (INT on tbl_api_keys and tbl_verification_codes) |
| Energy, cost, carbon, limits, area | DECIMAL(21, 6) |
| Tariff period price | DECIMAL(23, 8) |
| Latitude | DECIMAL(12, 10) |
| Longitude | DECIMAL(13, 10) |
| Timestamps | DATETIME, stored in UTC |
| Calendar dates | DATE |
| Time of day | TIME |
| Flags | BOOL |
| Short codes and status | VARCHAR |
JSON documents (address, faults, definitions, connection, rule and report expression, privilege data, request_body) | LONGTEXT |
Equations (tbl_virtual_meters.equation, power_equation) | LONGTEXT |
| Digital point value | INT |
| Files | LONGBLOB |
Indexes
Secondary indexes are named {table_name}_index_{n}.
| Pattern | Example |
|---|---|
| Name lookup | tbl_meters_index_1 (name) |
| Parent plus time | tbl_meter_hourly_index_1 (meter_id, start_datetime_utc) |
| Category plus time | tbl_space_input_category_hourly_index_1 (space_id, energy_category_id, start_datetime_utc) |
| Point history | tbl_analog_value_index_1 (point_id, utc_date_time), tbl_analog_value_index_2 (utc_date_time) |
| Tariff validity | tbl_tariffs_index_2 (energy_category_id, valid_from_datetime_utc, valid_through_datetime_utc) |
The scripts do not declare foreign keys. Referential integrity is enforced by the API.