Draw ER Diagram for Assessment App

[bsemot]

We need to have an ER diagram for the assessment application.

• candidates
• reviewers
• questions
• question types
• labels
• sections
• groups
• results

[koraytaylan]

We can consider above as the initial version.

[ilhan-mstf]

Thank you @koraytaylan 👍, it looks great, I have a few suggestions:

• Instead of datetime we can use unix timestamp. datetime is a converted timestamp data according to timezone. Therefore rather than datetime, using timestamp in the db will provide us more flexibility when showing dates in the client since clients may be in different time zones. Furthermore, time zone database in the server needs to be updated periodically since governments may apply exceptions such as president elections, etc. Therefore, this type of exceptions may result in storing wrong data in the db and requires fix and since unix timestamp is not a converted (or processed) information, it provides us more error-free schema.
• Maybe it covered but I couldn't realize that where candidate's response are stored. If it is stored on job_candidate_assessment:data, it is ok, but not sure. Furthermore, where will be review result stored? If it is stored on job_candidate:state, we may need more data than an enum field such as review score etc.
• We may drop job_ prefix from the table names, they will be simpler.

[koraytaylan]

@ilhan-mstf thanks for the feedback,

• Considered the same at first and investigated a little, from the official docs of mysql,

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis.

So it doesn't really matter if we set the timezone while we connect to it from the api.

• Right, I thought we would store it there in a document basis. There will be many different types of questions and many different possible answer types to those which introduces a great deal of complexity from the point of storing them in separate tables and also processing that stored data later on as well. At least for the initial phase I want to keep things as simple as possible.

• At this point, it is true that the first big milestone for the project makes the "job" prefix somewhat verbose. However, future plans for the project is to extend it to cover more than just the assessments so think about it as more like forward thinking for grouping related tables into a scope.

[ilhan-mstf]

I think the document mentions a difference between timestamp and datetime. The data that we are going to store should not depend on other configurations like server's timezone. At least these conditions also need to be stored. For example if we chose datetime, we also need to store time zone of the datetime since it depends on time zone. However, timestamp is always UTC. Every time ensuring server's time zone is a task of devops and timestamp saves us from data generated by misconfigured server since it is always UTC. Consider that we somehow misconfigured the server and for that period datetime values may be stored in different time zone and if we change the time zone these values will be wrong. As far as I search through the internet, datetime is suggested for specific dates such as birth date. I suggest to research more about this topic since time zone issue is a total mess.

[koraytaylan]

It's nothing that complicated fortunately. Documentation says server already stores it in UTC. You specify your time zone from client side as you connect to the server and server adjusts its stored datetime (which is already in UTC format) according to connecting client's zone.

For our case, we will set the timezone to UTC from api as we connect to it. The orm we will be using already does that out of the box by the way. So no worries we won't be having any devops burden or timezone mess.

[tolgaevcimen]

An ambiguity we come across: Can a question belong to multiple sections? (eg. a question can both be in Java and Backend and Web)

In that case we need to have a another table to make the relation between the question and section many-to-many. @bsemot ,@koraytaylan can you confirm?

[tolgaevcimen]

To our understanding, many of the tables do not have a state column but requires one.

• job_opening: opening might be filled, cancelled, etc.
• job_candidate_reviewer: the reviewer can be changed for the candidate, in order to be able to keep the history of reviewers, we should have a state column in this table
• job_assessment: assessments can be removed
• job_assessment_section: sections can be removed
• job_assessment_question: questions can be removed - an outdated questions for example
• job_candidate_assessment: an assessment can be cancelled without declining the candidate all together - state in candidate table is not enough
• job_candidate_comment: a comment can be deleted

I cannot think about the other tables (organization, role, etc.) clearly for the requirement of the state column as I'm not really familiar with the business logic there, but we can consider adding the state column to them in the same manner as well.

Also for the sake of having a better understandable terminology, I suggest naming job_assesment as job_test. This way we can call a group of sections as a test, and the assignment of a test to a candidate as an assessment. This makes better sense to me.

[koraytaylan]

@tolgaevcimen,

I thought of questions as direct children of a section. So I feel like it should be one-to-many. Besides it doesn't make sense for a question to belong java and web to me. Because on many-to-many case we would also end up trying to check and avoid showing same questions to a candidate since we would assign not individual questions but sections instead to an assessment.

I agree a job opening would have a state. However, I'm against defining a state for deleted objects. That's called soft deletion which I consider unnecessary complexity. Something is either deleted or not after all.

To keep track of changing states, I have something different in mind involving an audit table storing each change in json format. This will also cover when an object is deleted.

Test as a keyword somewhat rings the bell for unit tests for me whereas assessment is a spot on name for the purpose.

[tolgaevcimen]

As a result of our direct discussion with @koraytaylan I'd like to point out few change decisions we agreed on.

• adding state to job_candidate_reviewer and job_opening tables.
• creating audit mechanism covering all tables

Another point we agreed upon that doesn't require any schema modification, but I felt like sharing it. We mustn't edit any record on job_assessment, job_assessment_section, and job_assessment_question but create new revisions to not loose any past relation.

Also we couldn't agree on a solution on how to handle "same question in multiple sections" case. Current options are:

• many-to-many relation between section and question
• nested section structure

We are open to any other suggestions on this case as well.

[ilhan-mstf]

Also for the sake of having a better understandable terminology, I suggest naming job_assesment as job_test. This way we can call a group of sections as a test, and the assignment of a test to a candidate as an assessment. This makes better sense to me.

I agree with this issue, they should be different, but doesn't have a name suggestion.

same question in multiple sections

I think this is possible, but it depends on how we structure tests, @bsemot do you have any sample test data? It will be useful to share it with us to go further.

[ilhan-mstf]

Also for the sake of having a better understandable terminology, I suggest naming job_assesment as job_test. This way we can call a group of sections as a test, and the assignment of a test to a candidate as an assessment. This makes better sense to me.

I agree with this issue, they should be different, but doesn't have a name suggestion.

I've just noticed that there is job_candidate_assessment table, I guess that may be enough to make distinction.

[tolgaevcimen]

@ilhan-mstf while discussing with Koray I have realised I have overvalued job_candidate_assessment to the point where I wanted it to have it's own name. Then I backed down :) no need.

[yigityuksel]

As a result of our direct discussion with @koraytaylan I'd like to point out few change decisions we agreed on.

• adding state to job_candidate_reviewer and job_opening tables.
• creating audit mechanism covering all tables

Another point we agreed upon that doesn't require any schema modification, but I felt like sharing it. We mustn't edit any record on job_assessment, job_assessment_section, and job_assessment_question but create new revisions to not loose any past relation.

Also we couldn't agree on a solution on how to handle "same question in multiple sections" case. Current options are:

• many-to-many relation between section and question
• nested section structure

We are open to any other suggestions on this case as well.

I think best option would be many-to-many relation. When we need to query questions by their section, nested structure increases complexity.

[koraytaylan]

We discussed these topics with @bsemot and @tolgaevcimen today.

Turns out we will indeed need to assign questions to multiple sections. @bsemot calls them groups actually. So I thought of introducing another table called question_group which will have a many to many relation with questions and will act like question tags. Then a section will be associated with these questions in many-to-many form. A little more complicated but covers the requirement better.

Assesments will also need draft and published states, and we need to build snapshots for each published version for not losing previous states of an assessment. Versioning questions and sections was something we discussed with @tolgaevcimen earlier.

Please add if I missed anything else important.

[koraytaylan]

Updated schema as shown above. Thought that job openings and also the assessment questions is subject to moderation so they will also be reviewable.

Instead of revisions, I am proposing locking. An assessment after assigning to a candidate will be locked and not editable after that point. This is for ensuring integrity such as an assessment shouldn't change when there is people already taken it.

I was also planning to add auditing on database level (which is the safest) where we would have a table and store json representation of rows changing that will be created by triggers. However in mysql I conclude that it is not possible to convert a row to json at this point. This is something trivial with Postgres in the other hand.