Database Design
--
Users entity records users data. user is the core of the application. Every action happened based on user action. User can write a problem, send a submission, participate in contest, participate in training, create announcement, send a clarification, answer a clarification, and other thing that might be too specific.
From the diagram, come up a question, how to differ a user with another user? Such us, who are the manager of a contest, who are the one that can access a problem? Based on that, permission entity created.
Permissions entity records all access a user have, to what context, and which context. contextType is the kind of Contest, Training, Archive, User, Problem, or another abstraction. contextId determines which one of the context that affected. role is what permission that user have.
Problems entity will records the problem entity. It have foreign key to user, as the owner (or writer, or problem setter). This doesn't mean a problem can only be managed by one user. A problem can be edited by another user whom have the permission. The permission controlled by Permission entity.
Submissions entity records all submission. Every submission referred to one submission (which why a foreign key to problem exist) and only submitted by one person (which why a foreign key to user exist).
What contextType and contextId field for? By this design, to search for a submission that submitted for a contest will be a much hassle, where you need to join 3 table to get the result, and it would decrease the performance as in querying this often happen.
To counter the problem, we add those fields, so we only need to do a filter, which hopefully will be much faster than joining a table.
Why not give a foreign key to Contests? Because a problem not only used in contest, but also to training, and archive. If we enforce doing that, the submission table would be bloated with unused field and we don't think it's a good design.
Why not give a foreign key to ProblemSets? We know that ProblemSets is used in contest, training and archive. The reason is because it still doesn't differs much by not giving any foreign key. You still need to join 2 or 3 tables for the results, while we are doing it really frequently.
Hence, we add the contextType field and contextId field.
Solves relations might be not needed, because it's might duplicates and we can get this by querying from tables of Users, Problems, and Submissions. We add this table to do analytics easily and a lot faster. The table will be used to determine user's rank, problem's difficulty, problem's error, and other think. User would like to see their rank updated frequently, which means we need to query the data often, and problem setter would like to analyze the correct answer of a submission, and he/she will probably querying its often on the contest, which might cause problem in a high traffic contest.
ProblemSets records collection of problem(s). To be included in an archive / training / contests, a problem need to be in a problem sets. Because an archive / training / contest can use the same problem sets, we can easily moves them around, and we still can find the submissions for the context easily, because they are not dependant to the contest or problemset or archive or training.
Contest records the contests. Contest is an event for a scheduled times where users participate in the contests. In contest, there would be problems set, announcements, participants, and clarifications. Again, the differents between role each user handled by the Permissions table. Whether a user works as a tester, or can answer a clarification or not, or can add a problem to problem sets used by the contest.
A clarification created by one user, and answered by one user. Each clarification associated with a contest.
Annoucement created by one user, and associated with a contest.
Archives is a collections of collections of problems. Or collections of collections of collections of problems.
We handled this by enabling archive to have sub-archives. The structures works like a tree, where an archive can have associated problem-set, or only another sub-archives, or both.
For a short definition, training is a progressive archive, where you can access next archive if and only if you have finished / completed the current archive. In this case, archive is chapter.
Chapters looks like a tree, but different. You can only traverse it to the first sub-chapter, or to the next-sibling-chapter. And you can only access the next-sibling if you completed the chapter. To complete the chapter, you must complete the sub-chapter first, and the problem-set associated with the chapter (if any)
We are open to all thought and suggestion! Email your idea to [email protected] or [email protected], or we can discuss it here