New model for tags

[peterdesmet]

@jreubens and I discussed the data model on the phone and we think it should be updated so that:

1. Table tag_device should only contain unique serial numbers: one device = one serial number = one row
2. (New) table tags contains one or more identifiers associated with the tag_device. It is a combination of the current tables acoustic.tags and sensor.tags, but also allows to store acoustic archival tags. This table also contains the type (archival/acoustic) and subtype, which are no longer associated with tag_device (since a tag_device can have more than one type).

Examples:

• Acoustic tag: 1 tag_device row - 1 tag row, with type=acoustic and an acoustic_tag_id that can be found in detections.
• Archival tag: 1 tag_device row - 2 tag rows, with sensor_type=depth and sensor_type=temp and both with type=archival. There are no associated detections, only associated readings.
• Acoustic archival tag: 1 tag_device row - 4 tag rows, 2 archival ones (for both sensors) and 2 acoustic ones, with different acoustic_tag_ids. There are associated detections (for both acoustic_tag_id) and readings.

Schema:

                  ┌ tag (depth)
animal - tag_device
                  └ tag (depth)

@aubrivliz Note that this is just an idea that should be discussed further. It should not be implemented yet.

[PieterjanVerhelst]

Small question (or correction?): the type = acoustic under Archival tag is probably wrong, as it is an archival tag and not acoustic? Or am I missing something?

[peterdesmet]

Correct. Updated.

[peterdesmet]

Important to note: acoustic.tags currently also contains records for which the tag_device_type = sensor-tag (i.e. archival). Another reason why acoustic.tags and archive.sensor should be merged.

dbGetQuery(con, glue_sql("
  SELECT acoustic_tag.tag_full_id, tag_type.name
  FROM acoustic.tags AS acoustic_tag
    LEFT JOIN common.tag_device AS tag_device
      ON acoustic_tag.tag_device_fk = tag_device.id_pk
      LEFT JOIN common.tag_device_type AS tag_type
        ON tag_device.tag_device_type_fk = tag_type.id_pk
  WHERE tag_type.name = 'sensor-tag'
"))
           tag_full_id       name
1       A69-9006-10572 sensor-tag
2       A69-9002-16200 sensor-tag
3           A69-1105-1 sensor-tag
4           A69-1105-2 sensor-tag
5           A69-1105-3 sensor-tag
6           A69-1105-4 sensor-tag
7        A69-9002-6814 sensor-tag
8       A69-9002-13731 sensor-tag
9       A69-9002-13730 sensor-tag
10        A69-1105-211 sensor-tag
11        A69-1105-212 sensor-tag

[PieterjanVerhelst]

Here I am a bit confused, because an acoustic tag can have sensors as well. So is the mentioned sensor-tag here an acoustic transmitter with sensors or an archival tag (basically only existing of sensors, as it has not an 'emitting' part; it is not a transmitter, but a datalogger).

[peterdesmet]

@PieterjanVerhelst the confusion is exactly why the model should be adapted. Currently we have 2 tables which both contain mixed types. We need a single table with all the tag (for lack of a better word) "identifiers", and what that identifier carries, e.g. for a single (super) tag:

• logs temperature
• logs pressure
• broadcasts temperature on acoustic_tag_id A
• broadcasts pressure on acoustic_tag_id B
• broadcasts ping on acoustic_tag_id C

[PieterjanVerhelst]

Aha, I'll start to get it. Indeed, the field type will be crucial. What is the field subtype? Or do you mean the sensor type with this?

Your suggestion can work, but would replace 'broadcasts' by 'emits'. At this time, I can't come up with something better. Can it be integrated that certain fields are not returned following the type? E.g. the fields logs temperature and logs pressure are redundant for acoustic transmitters. Or do you want to keep all fields but fill in NA when relevant?

[peterdesmet]

@PieterjanVerhelst, there are 3 type fields for a tag:

• tag_type: acoustic, archival, acoustic-archival
• tag_subtype: animal, built-in, range, sentinel
• sensor_type: pressure, temperature (both currently also contains uncontrolled T, A, M, etc.)

The current design choice is to have a single get_tags() function, that always returns the same amount of columns, so we would "keep all fields but fill in NA when relevant". The reason to keep a single function is that tags can be a mix of things, and it's easier to get that info in context. You can filter on type and subtype though: https://inbo.github.io/etn/reference/get_tags.html

[PieterjanVerhelst]

ok, that is clear. No further questions or suggestions, except that it would indeed be good common practice to fill in the full sensor type (so e.g. 'pressure' instead of 'P') to make it more self-explanatory.

[PietrH]

@peterdesmet Is it ok to close this?

[peterdesmet]

Yeah, I think so