I am using MQTT for communications for this project. Previously I used bare TCP/IP and serial. 

MQTT works well for one-way communications but for request/response it is a little awkward; by design. But, request response is necessary and implementing it simply means sending an optional return address with a message and using it.

My MQTT components are MQTT client at my sensor, or as close as I can get ittMQTT broker in a less resource constrained device; in my case a small Windows server with 2TB raid storage.

The data from sensor to Broker is comma-separated data starting in column 0 is the data type. for example "raw" is a list of measures from a client; "exception" is a processor exception from a client. There are a few others.  

I have implemented a Java MQTT client that selectively subscribes to MQTT messages (config file) and writes data to a normalized SQL database.  In the config file you can select which sensor nodes to listen to and which types of messages to send to the db.

Here is the .config file:

    <comment>            <values that drive this service, they are of the form:>
    <comment>            <   name   value                                      >
    <comment>            < names are not case sensitive but values may be      >

    <mqttBrokerURI>      <192.168.1.250>
    <mqttBrokerPort>     <1883>
    <mqttClientName>     <MQTTdbWriter>
    <QoS>                <2>
    <node>               <Weather>
    <node>               <NODE01>
    <node>               <NODE02>
    <node>               <Solar01>
    <mqttMessageStart>   <comment>
    <mqttMessageStart>   <raw>
    <mqttMessageStart>   <event>
    <mqttMessageStart>   <exception>
    <mqttMessageStart>   <start>

I am currently receiving data from my Cumulus weather station, solar panel voltage sensor and two temp/humidity nodes,

To get data from Cumulus a Java program reads the Cumulus data files and then sends MQTT messages that are then written to the db.

All of those sources use the same MQTT payload format.  The database tables both specify how to dissect the message and stores the data. 

Example Payload:

raw,,,,,,,DHT22CFH,33.80,92.84,52.90,38.94,22.87,Comfort_TooHot,1

The top-level measure is "DHT22CFH"

it will be broken out to Temperature C, Temperature F, Humidity %, HeatIndex,Dew Point, Comfort Text, Comfort#. Each will be given a common time stamp and the source will be identified too.  The final destination is a table defined as:

and related to the following 3:

Those when joined in SQL form the basis of all reports. They convey: Source of Data, the time (UTC), the measure ID, the measure.

The database design with these 3 and a few more tables has been used without changes for my last 4 projects.  The design has worked without changes.

The full database DDL is available from me.

Summary:

I have implemented an MQTT transport to a SQL server backend that is very suitable for reporting and initiating actuators.  That is further augmented by planning for request/response in the MQTT scheme.

This design has been used for my last 4 projects without requiring change....