for the FTP data push service there will be one XML file per group account. Hierarchy is pretty simple:
1. Group Account – has one or more company accounts. You would normally only use multiple companies if you needed separate invoices. For example different construction projects with different job numbers. Doubt you will have more than one company so you can likely ignore the possibility of multiple companies.
a. Company Account – ut_company record includes all company data, ie comp_nbr (primary key), address, phone number etc.. You can probably ignore this level as well.
i. Job Site – ut_company_site record includes site_nbr (primary key), comp_nbr, site_name, site_address etc
1. Gateway – Identified by a unique 32 bit ascii hex gateway ID. Gateways are manually assigned to job sites and can only be on one job site at a time. ut_gateway record includes its gateway_id (primary key), comp_nbr, site_nbr etc.
2. Sensor - Identified by a unique 32 bit ascii hex sensor ID plus port number. Completely uniquified using (sensor_id,port_nbr). Sensors are automatically assigned to job sites if/when they send data through a gateway that is assigned to that site. Sensors can and often will be assigned to more than one job site as new sites are created. Think disaster and flood recovery where systems are installed in home A (job site A) for a week or two and then moved to a new home B (job site B). ut_sensor record includes things like sensor_id, site_nbr, port_nbr, sensor_desc. Primary key is sensor_id, site_nbr, port_nbr.
3. Sensor reading – ut_sensor_reading record includes site_nbr, sensor_id, port_nbr, read_date (date sensor took reading), create_date (date reading was saved in the database), and finally sensor data such as temperature and humidity. Primary key is sensor_id, site_nbr, port_nbr, read_date. You will want to use a clustered index for this table.
Most of the XML tags are self-documenting except for some of the sensor data fields. We can provide tables that map cryptic “data_0” into human readable “CO2 (ppm)” for all of the sensor types you are using.
Note that all data is in ISO units and all timestamps are in GMT.
