I'm trying to implement the equivalent of an SQL table with a unique key on both the user and an "ID" column. The ID, let's call it "test number", is based on a fixed list of options.
For example: For two students Alice and Bob, each can create a record with test number 1 (out of 5). However, Once Alice adds her test 1 record, she cannot add another record for test 1; she should only be able to add an entry for test 2, 3, 4, or 5.
(Some additional constraints: the database needs to be write-only for students. After they add their record, they can no longer edit or delete it.)
Initially, I was planning on making each test a record (as is standard for normalized tables). After discovering that you can't set a unique column in either the Database activity or this Dataform plugin however, I realized that this wouldn't guard against the possibility of duplicates.
My other idea was to implement a single user record that captures the data from all 5 tests. The problem there is the loss of fine grained control. Students will be adding a new record as they receive the respective test's info, which are days apart on average. With this method, I'd have to unlock the whole record for editing, losing the guarantee that they can't edit previous data.
How best could such a database design be implemented? Any help would be much appreciated; Thanks.