Re: a common sense approach to data-types?


Can anyone help me get into the mindset of data-types? I have created a wireframe for user function and experience. Now (trying to) translate to data-types. Seems that this bit is the hardest and likely the most valuable bit of the design process.

I have three main clusters of data:

  1. USERS

Everything below relates to a single PROJECT. For info… a HOTSPOT represents a potential problem area of a PROJECT.

The main APP objectives are:

  1. for one or more USER to propose a HOTSPOT (and independently rank each of their HOTSPOT)
  2. once all HOTSPOTS are submitted, ALL USERS score/rank ALL other (not their own) HOTSPOTS
  3. the maximum FIVE highest ranked HOTSPOTS become PRIORITY status (from CANDIDATE status)
  5. current vs. historical SCORING is available via a reporting dashboard

My challenge is how to cluster the data TYPES and FIELDS, to everything runs optimally as the USERS, PROJECTS and HOTSPOTS (and associated data) grows.

Common sense suggests I create three DATA TYPES. One for PROJECTS, one for USERS and another for HOTSPOTS…

Thanks in advance for your help!

Hi Chris,

you could equip the HOTSPOT structure with the following fields and lists:

a) the USER that initiated the hotspot
b) the list of all USERs participating in the hotspots project (of which the initiator is one)
c) the list of all rankings by all admissible users
d) the ranking total as a sum of list c)

The admissible users c) are obtained from a filtering of b) except a) and except all users who left the app and put their status to absent. For the latter I tend to give each user a boolean field “active”, which is switched to off when he leaves the app. Since lists can grow to infinity, you won’t have any problems with the approach a) - d) as long as no user data structures are ever deleted but only switched off. So common sense is basically not to dispose of data.

Hope this helps, all the best,


Hi Ondrej. Now it’s beginning to make sense.
Am I right in thinking that the ‘list’ feature seems to be the way to store same type of data captured repeatedly, and may for example be involved in a calculation that is for example an average of all the captured-over-time data?
Thanks again Ondrej.
All the best.

What is the relationship between USERS and PROJECTS ?

When you say “all USERS” do you mean all users for a PROJECT or every USER ?

My usual advice is to work backwards from the “aha” screen - so the one that shows your product in all its glory. And think about how to structure the data for that. Starting at “the beginning” - so data input - can make life more complicated later on. Optimise for the thing you do a lot - look at data - rather than the thing you do occasionally - add data. You can always hack the data in initially via the Data Tab or a scrappy admin page.

PROJECTS just seem to be categorisation, so I think that is fine.

The real complexity (the aha) comes in the scoring.

A HOTSPOT has many USERS that provide a SCORE. A USER can SCORE many HOTSPOTS. Classic many-to-many relationship.

So I would suggest USER SCORE is another data type. A score field for one HOTSPOT by one USER.

This makes the total simple, it is the sum of all the USER SCORES for a particular hotspot.

Now, here is the issue. Bubble won’t sort on derived fields. So whilst you can display all of your HOTSPOTS with the score, you can’t sort them on that total SCORE.

So I think this then means you need a “TOTAL SCORE” field on HOTSPOT, that is updated every time a score is given or rescored. At that time you can create a little SCORE HISTORY data type for the HOTSPOT with the before, after and USER SCORE.

This is not particularly “relational databasey” in that we are storing the derived data as well as the underlying data, and it could get out of sync. But what you could do is run a scan every so often to check that Total score = added up score. The “SCORE HISTORY” is then quite useful for debugging.

1 Like

Nigel, thanks for your thought, and in response to your points…

The relationship between USERS and PROJECTS:

each PROJECT at the outset is assigned specific (pre-registered) USERS
only USERS assigned to a PROJECT may propose and score HOTSPOTS that are (through a high enough score) assigned to a PROJECT

So, by 'all USERS’ I mean all USERS assigned to that PROJECT. That make sense?

Reason I am so fixated on getting the data structure as right as possible up-front, is that within a PROJECT, data is added on a weekly or bi-weekly basis through repeated all HOTSPOT scoring from all USERS. Volume of data mostly depends on the number of USERS assigned to a PROJECT (as the maximum number of HOTSPOTS to be scored is five).

To you point about ‘scoring being the complex area’ - I thought as much. That’s the real aha :smiley:.

In addition to a HOTSPOT having many users, USERS will re-score each hotspot on a weekly/bi-weekly basis.

USER SCORE as an additional data type… interesting. Am I right in thinking that this means:
field - USER
field - HOTSPOT
field - SCORE* (where FIELD is a LIST to accommodate historical and current score)

*actually there are four repeated USER SCORES - I assume this would simply mean four appropriately named SCORE (list) fields?

Re: sorting derived fields… pre-calculating the sorting field and placing it as an existing field makes sense. Thanks for the heads-up.

Hope all this makes sense.

Thanks again Nigel.
All the best.

Actually, it might now we have “GROUP BY”.