MTurk assignment ID is not always unique. When a worker return a HIT, the next worker taking it will have the same assignment ID.
Such being the case, when designing data structures for our own system to deploy tasks on MTurk iframe, it’s important not to use assignment ID as primary key for any table. However, if you already did this, here’s how I fixed it in Django (using PostgreSQL):
- You have an old table (defined in model.py), where you specify the assignment ID column to be primary key.
Create new model object with exactly the same columns, except that: the model name is different (of course!) and the primary_key=True of your assignment ID column is removedExample class Task(models.Model): # other fields assignment_id = models.CharField(max_length=30, primary_key = True) class NewTable(models.Model): # other fields assignment_id = models.CharField(max_length=30) class Result(models.Model): # other fields task = models.ForeignKey(Task) # add the following line to all tables that refers your task object as foreign key newtable = models.ForeignKey(NewTable, null=True)
- run in command line:
python manage.py makemigrations python manage.py migrate
- Now you will have a empty new table with the correct schema constraints, the next thing to do is to copy existing contents to the new table.
-
- You can choose to put your scripts in [yourappname]/management/commans/ folder, then run python manage.py [your filename without .py] to execute your code
- Or you can make your changes directly in command line. Following is an example
python manage.py shell
from [yourappanme].models import * for t in Task.objects.all(): NewTable.objects.create(assignment_id=t.assignment_id, ...[other fields]) for t in Result.objects.all(): nt = NewTable.objects.get(assignment_id=t.taskmeta.assignment_id) t.newtable = nt t.save()
-
- Now the new table is also a foreign key of those tables, you can remove the old task objects in your models.py, and migrate the changes
- Change the model name and field names (as foreign key) of NewTable in your models.py, then migrate the changes
- Done!