Data structure advice

[se]

I am just looking for advice in setting up the structure of an app. If there are any similar app examples you could reference, that would be sensational.

The app would have a list of 500 people and some additional fields with their details (email, department, etc). Each individual person then needs to be marked on a 5-point progress scale against 150 different criteria goals over a period of time. The criteria would also need to be separated into sub category. Eg. Criteria 1-40 is Category 1, 41 - 80 is Category 2. The data would need to be sorted in this way as well, eg. "what staff are marked 3 on Criteria 3?"

So far the data looks like this, with the data change been done through just editing a UX table view. 

Person	Email	Department	Criteria 1	Criteria 2	Criteria 3	etc.
Jo	jo@gmail.com	Sales	1		2
Fallon	fallo4n@gmail.com	Finance	3	4	5

I've been stretching my brain and can't find an alternative to having 150+ columns (one for each criteria) within the app builder which ideally I would want to avoid. 

I've built an alternative where each person + criteria (populated from an enum) is entered as a new row to limit the data however the big limitation of this is there is no way to then quickly what criteria a person has met/is working on at a glance. 

[WillowMobileSys · Accepted solution]

You will want to have 2 tables - 1 for people and their related details and 1 for the criteria set as a child table to person table.

In the Criteria table all the 150 criteria goals would each be represented as a table row.  An example of this table might be:

ID	Person	Category	Criteria	Result
abc123	<<Person Key>>	1	1	a
def456	<<Person Key>>	1	2	b
ghi789	<<Person Key>>	2	1	c

<<Person Key>> is the row key from the Person table.  Ii creates a link and established ownership of the Criteria row to that person.  You might refer to these two things below to help.

References between tables

App Templates

[se]

Thanks @WillowMobileSys 

I've done some further exploration and set up like advised above with the person key column in the criteria column referencing back to the parent table (people). However, would it then be expected that within the criteria column there is a new row for each person and the corresponding criteria?

For example, if you wanted to track staff perception on a series of foods over time (Category: food type, Criteria: specific item, Score: 1-5 option

ID	Person	Category	Criteria	Score
23423	Person 1	Fruits	Apples	3
sdfsdf	Person 1	Fruits	Oranges	5
5324	Person 1	Vegetables	Potato	4
23234	Person 1	Vegetables	Celery	5
234	Person 2	Fruit	Apples	3
23432	Person 2	Fruit	Oranges	2
53244	Person 2	Vegetables	Potato	1
324342	Person 2	Vegetables	Celery	5

Based on that, 500 people x 150 criteria would equal potentially 75,000 rows?

[WillowMobileSys]

---

@se wrote:

Based on that, 500 people x 150 criteria would equal potentially 75,000 rows?

---

That will be fine.  When it comes to size of a table it is really more about the number of cells - rows and columns. 

In an app like you are describing, each user should probably only see their own set of rows using a Security Filter.  So for the table in question and each user that would only be the 150 rows down loaded.