A school test practice app

[Noviwan]

Hi,

I want to create an app that is listed as an example in the Private Tables help (https://support.google.com/appsheet/answer/10106508?hl=en) and with literally the requirements described there:

"There is a common shared set of questions, but each student's answers remain private to that student."

I don't need a complicated UI for students, just a simple table view of all the questions. The questions should be from a shared data source that only I can edit (like a Google sheet). When a student enters they should see the list of questions, with an additional column where they can add their Answer, which is unique to each user.

I am new to AppSheet and to be honest it hasn't clicked for me yet. Private tables seem like the right feature, since this specific use case is mentioned there. But I cannot figure out how to set it up; I assume it's a combination of one shared Questions table, a private Answers table, and some kind of table references to connect them. I am experienced in relational databases but could use some advice on the AppSheet way to do this.

[dbaum]

---

@Noviwan wrote:

some kind of table references to connect them

---

ICYMI: References between tables - AppSheet Help

Also, you don't necessarily need separate private tables for each user. You could use a single table and then Limit users to their own data - AppSheet Help as well as Limit users to particular tables, views, and actions - AppSheet Help. 

[Noviwan]

Thanks for the reply.  Not sure single table with views works for my use case ... the questions are a kind of lesson plan, and will change and have additions) over time. So I don't want them duplicated for each user ... maybe I'm thinking too much like a relational database designer? 

I think the references are the way to go, if what I need to do is possible. But since one table is not shared, I can't use the internal database option (which doesn't yet support non-shared tables). In turn, that makes it hard to follow the documentation - how to do Lookup columns for example - since all the examples show it being done with a database source. 

[dbaum · Accepted solution]

---

@Noviwan wrote:

I don't want them duplicated for each user

---

You wouldn't have to duplicate the questions, although you would have repeated references to the source questions. As I assume is occurring to you as a relational database designer, the skeleton of the table design could be along the lines of:

• Users table: User ID, Email, ...
• Questions table: Question ID, Question, Correct Answer, ...
• Responses table: User ID, Question ID, Response, ...

---

@Noviwan wrote:

how to do Lookup columns for example - since all the examples show it being done with a database source

---

I'm not sure what you mean. Regardless of each table's data source (e.g., spreadsheet, database), you can define relationships between the tables within the app. For example, in a Responses table, the User ID and Question ID columns would be Ref type columns linked to the Users and Questions tables, respectively. Functions like LOOKUP and notation from the context of the Responses table like [Question ID].[Correct Answer] are then available.

[Noviwan]

That does make sense to me Relational database brain! Thanks, I will spend some more time learning the ins and outs of this kind of application.