I am building a database to store the assessment records of students, along with other relevant information and functionality like reporting on average scores etc.
Currently my company uses Excel for all their records but this is rapidly showing its limitations, the major ones being no concurrent access and no easy data validation (I know Excel can, but it isn't as flexible as a programmatic solution).
To this end, I am building a database using Postgres with a Java/JavaFX frontend. The problem comes because my boss loves the flexibility of Excel, despite the strain this imposes on day-to-day business. However, I have been told that if I can design a solution that allows enough flexibility and enforces business rules for the majority of users but allows managers to change to modify them, they will take on my solution.
My current sticking point with this is that every so often the assessments will change for a given course as teachers reflect on the pedagogy. This means that I have to offer them some way of changing the assessments in a table, because they don't want to be locked into me doing it in SQL. I could just allow them to add new columns and when they set up a new term and course, associate those particular columns with that term and course, but the table may become very large and when they are selecting which are the pertinent assessments, it may be very tedious.
For example, say I have course 1 running in term 115 with 12 assessments. I then have course 1 running in term 215, but they want to change just 5 of those assessments to something else with different max mark to others etc. To do this, I could allow them to add a column to the table and just associate the 7 original assessments and 5 new ones with course 1 in term 215, while maintaining the ability to look back at marks in previous terms for the same course for the extant assessments at the time.
However, this means when they simply want to carry over the same assessments from the time the course ran in term 215 to term 315, they will have an ever expanding list of columns to pick that they want to associate with a given term (17 presently in this example). There would also be a lot of null values for each row where previous assessments are no longer used. This question reflects my problem but the solution doesn't because there is still an ever expanding list of assessments to carry over into a new table for a new term. There also have to relationships to other tables that would carry over and this approach seems to be considered bad pratice.
This may just be a necessary evil, but the solution I thought of that might be better, is allowing the managers to 'dynamically' create Java beans. For example, say I have a bean for the assessment fields in 115. When the user wants to change 5 assessments, they just load the old one and modify the assessments that are no longer relevant which creates a new bean blueprint. This would be done in such a way that there is no programming for the user (done through GUI). I would then store the objects in a column with another column to tell the frontend which bean type to use to represent the data in the frontend. Thus, there is one column but with potentially a few different bean types associated with each course and term. When they want to just carry over the same assessments to a new term, they use the previous term's bean. However, storing objects like this seems to be considered bad practice, as well. In addition, it is difficult to generate Java beans dynamically and I might have to use Python, which adds additional complexity to this solution. In short, am I better off just allowing the user to create tables/columns dynamically or should I go this route? Are there any other solutions? Sorry for the long explanation.