Ideal sql database structure for "letter of statement request" system

Question

I'm trying to build a letter of statement request system using MySQL as its DBMS. I don't know if "letter of statement request" is the appropriate term, but the system is meant to provide simplicity for college students to request a letter of statement from their faculty or university; such as active student certificate, research permit, etc. I have started to make the system but now doubt whether the database structure (just part of it) is ideal.

Here's the overall flow of how the system works:

• Student sends a request for a letter of statement to the system. There are many available types of letters, but they can only request one type at a time.
• Each type of letter will require different data to input. For example, an active student certificate requires the data of the current semester of the student and the destination institute where the certificate will be used; while a research permit requires the data of research title, the institute/place where the research will be held, time of research, research subjects, etc. This is where the confusion and doubt hit me.
• The requested letter will be then gets verified by officers and will be sent to the student if gets approved.

Here's the (partial) database structure in question (Tailored for simplicity)

letter_type

• type_id (primary key)
• description

letters

• id (primary key )
• letter_type (foreign key)
• submitted_at
• necessity
• letter_position
• status

active_student

• letter_id (primary key, foreign key)
• semester
• destination_institute

research_permit

• letter_id (primary key, foreign key)
• title
• institute
• duration
• subject

• The letters table is used to record the overall data of the letter, including the id of the student, type of the letter, submission date, etc.
• The two other tables, active_student and research_permit, are used to record the 'detail' data of the letter. Meaning that the data of a request for an active student certificate will be written in letters and active_student, while a request for a research permit is written in letters and research_permit
• letters table will use its letter_type field to determine which table it should be referencing.

Finally, here's the question:

Is my database structure ideal? If not, what's the better approach available?

Additional context

I'm making the system using Laravel 8. The code for creating a new record of letters is easy because each type of letter is handled by a different controller. The difficulty comes when I want to make the code for retrieving a set of letters' records (for example letters sent by a student with id 'X001'). What makes it difficult is because I need to retrieve records from the letters table along with its 'detail' data in the referencing table of each record (there are more than just two types of letters actually). Actually, I would like to ask about how to do this in Laravel. But before that, I want to make sure that my database structure is correct.

Answer 1

This is a very good question in my opinion. You are worried that in order to enter the data for a particular request you need a table for that request with all its obligatory (and maybe optional) columns. Every time you want a new request type in your system, you'll have to add a table for this and change the software.

This is one of the few cases where a key/value table might be an appropriate choice. Here is an example:

• letter_type (letter_type_no, name)
• letter_type_field (letter_type_no, field_name, is_obligatory)
• letter_request (letter_type_id, student_no, date, status)
• letter_request_field (letter_type_id, student_no, field_name, field_value)

Key/value tables are a nuisance to work with. If you have just one table for them the values must be strings for instance and dates and numbers must be stored in an agreed format. Validity checks are hard to implement. The list goes on. But for a new letter type, you just add that type to the letter_type table and list all required fields in the letter_type_field table, and all your queries and software can work with this.

Another and probably better approach, though, may be to use a NoSQL approach. E.g. store the field list in an XSD and use this in your app to have the student fill in a form that you store as XML. It's simpler, and there will be a person looking at the request anyway, so they can point out missing or wrong data.