1

I'm having trouble designing a data model that allows for the following:

  • Courses can have many assignments.

  • Assignments can be defined recursively. e.g. "There is reading homework every tuesday".

  • Users can each complete an instance of the recurrent assignment at their own time and with their own grade and their own priority.

So, focusing on the assignment aspect of these requirements I came up with the following partial ER model: (It's missing how users are related to courses, teachers, etc...):

assignments erd

*There's a mistake in the picture, an event is related to 0 or 1 assignment

A teacher would then create an assignment for their course, let's say: 'Hand in math lab every tuesday due for tuesday next week'.

A single assignment will be created along with all the events for each occurrence.

When a user interacts with a specific event instance of the assignment. The assignmentUser will be created with the corresponding data.

Is this a good way to model this? I'm worried about the recurrent events side of this. Maybe it would be better to only have one event per assignment and generate all assignments that fit the recurrence rule on creation?

  • Your model allows users to submit assignments for a course they are not enrolled in, and receive a mark for such a submission; clearly a flaw in the model. You require a UserEnrolledInCourseOffering entity as well as a CourseOffering entity, for starters. – Pieter Geerkens May 29 '15 at 6:03
  • I'm aware, I wanted the question to revolve around the AssignmentUser tabel model, that's why I stated: So, focusing on the assignment aspect of these requirements I came up with the following partial ER model: (It's missing how users are related to courses, teachers, etc...) – RedHusky May 29 '15 at 14:33
  • I'm a bit confused as to what the Event entity is. Can you elaborate on that? – Joe Borysko Jun 1 '15 at 22:41
  • An assignment can be recurrent e.g. 'Happens every tuesday'. The event table contains the startDate, endDate of each specific occurrence of the assignment. If one assignment happens 8 times during the semester, there will be 8 rows for that assignment in the event table. [Event] assignmentId, startDate, endDate, title, description.. – RedHusky Jun 2 '15 at 20:09
1

I like the gist of what you're doing. I think the entity names are a bit confusing though, and I would put some fields in different places. From my understanding of the system you're trying to build, here's a diagram I recommend:

enter image description here

In this model, Assignments have one AssignmentOccurrence for every time it occurs. If an Assignment occurs once, it would have one AssignmentOccurrence.

Take the values of the AssignmentOccurrence with a grain of salt, there's not enough information for me to know what data needs to be stored in there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.