Forum Discussion

TimEgerton's avatar
TimEgerton
Qrew Trainee
5 years ago

Where to put the Join Table

Hi,

We have three Tables (1) Confirmed Cases, (2) Close Contacts and (3) Locations.

When we look at Confirmed Cases we want to see all their Close Contacts and all the Locations they have been at for a particular period of time. i.e. What Contacts were at the same Location as the Confirmed Case at the same period of time.

Confirmed Cases -> Contacts
Confirmed Cases -> Locations

When we look at Contacts we want to see all associated Confirmed Cases and common Locations.

Contacts -> Confirmed Cases
Contacts -> Locations

When we look at Locations we want to see all Confirmed Cases and all known Contacts who were at the same Location within the same period of time.

Locations -> Confirmed Cases
Locations -> Contacts

I believe there needs to be a join table in there some where (or maybe 2).

Also any suggestion regarding how we address the "at the same period of time" question. Same period of time is on the same day with a time period say 9:00 - 12:00.

I have looked at the COVID19 Contact Tracing app on the QB Exchange however it only addresses the first set of relationships.

Thanks

------------------------------
Tim Egerton
------------------------------
Relationships
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    You'll probably want to have an "Activities" table that could act as a join between Locations and Contacts. As for Contact Tracing, I'd set it up so that "Confirmed Cases" is a child of "Contacts" and just have something on the Contact record show that they are a Confirmed Case. Something like this:

    Regarding the different views that you identified, this would all be done through embedded reports on the various tables. Regarding your "Same Period of Time" question, you'll want to have a couple of fields on the "Activities" table to denote Date and Time of the Contact's visit to that Location. You may also want to have a formula that looks at this information and then flags it as "Morning", "Mid-Day", "Afternoon", etc. You can then use the Date field and this in your Grouping & Sorting on your formulas.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
    • TimEgerton's avatar
      TimEgerton
      Qrew Trainee
      5 years ago
      Hi Blake,

      Thank you very  much for your reply and suggestions they have been very helpful in our thinking.

      With the idea of Confirmed Cases being a child of Close Contacts the whole process starts with Confirmed Case #1 ​which we then need to identify all Confirmed Case #1 Close Contacts and Locations, that's fairly straight forward. However when we get more than one Confirmed Case then it gets a bit more complicated as ​Confirmed Case #2 may or may not have been a Close Contact of Confirmed Case #1, or it's a new Confirmed Case from a separate Location.​​ If they were a Contact of Confirmed Case #1 then they are both a Confirmed Case and maybe a once Contact of other Confirmed Cases.

      A single Location can have multiple Confirmed Cases and multiple Close Contracts. It's an ever widening circle so to speak.

      What are your thoughts about Confirmed Cases also being a parent of Activities?

      Also if a Contact become a Confirmed Case would the best approach be create a new record in the Confirmed Case table?

      Thanks again.

      Tim ​

      ------------------------------
      Tim Egerton
      ------------------------------