I am on a project to create a new Java EE application (JSF, Hibernate, Spring Security, Informix database). This application will automate the entry of notes for the annual interview of bank employees.
At the very beginning, everything was entered in an Excel file which then generated a report with the various performance graphs (according to the notes entered from 0 to 4).
Now I want to do a fairly optimized database design. I thought of creating the following tables:
Interview with columns (interview_id, interview_date),Competency with columns (competency_id, competency_group, competency_name),Interview_note with columns (interview_note_id, employee_id (FK), interview_id (FK) , competency_id (FK))However, I have some doubts about how to keep it compact and logical. Is this the right way of doing things? Are there any improvements to take into account for more optimization?
In your narrative and draft database schema, I find the following identified entities: Employee, Competency, Interview and Interview_note.
In this regard, only the Employee table is missing, but I'm sure you have it somewhere. Moreover, your design is very flexible, since it allows for several Interview_notes of the same interview, competence and employee. What is perhaps missing therefore, is the id of who made the notes. Alternatively, if there's only one set of notes for an interview, you could consider to identify the interviewer in Interview.
A part from that, and maybe some missing data for the note (points, percentage of satisfaction, or some textual annotations?) your design seems to fulfil its purposes.
The database engine will very well optimize all the joins you'll have to do. Maybe facilitate its job by defining the _id as primary key, if you didn't do it.
I can't see other optimizations: each table clearly represent a different relation (in the relational algebra meaning of the term) and merging any of them would inevitably result in a suboptimal redundant schema.