How to model a table in a relational database where all attributes are foreign keys to another table?

I've been working on the data model for a healthcare mobile app that will collect the user's symptoms and will book an appointment with a doctor if requested by the user.

I was thinking the app would list the available symptoms and the patient could choose from them, and then the system would estimate some illness based on the chosen symptoms and perhaps this Illness record could be a foreign key in the Diagnosis table. Then, when booking an appointment, there would be a foreign key to this Diagnosis record in the Appointments table. Later, this Diagnosis tuple could be updated by the doctor during the appointment. The problem with this approach is that this way in the Diagnosis table we are only storing some estimated illness instead of the actual symptoms of the patient that were present when booking the appointment.

I'm having trouble figuring out the model for the Diagnosis table, and also what to include in the Appointments table (besides doctor name, appointment date etc. which are not relevant to the question) regarding the diagnosis provided by the app.

What do you suggest should constitute the Diagnosis table? Should I store the symptoms of the patient instead of the estimated illness and refer to this in the Appointments table rather than some illness which later might get overwritten by a doctor anyway? If so, this would mean that we don't know how many attributes (symptoms) one record of the Diagnosis table could have, but they would all be just foreign keys to the Symptoms table.

Also, do you think I can model and do this in a relational database or should I be thinking of a NoSQL database?
I'm only listing the tables that I think are relevant to the question:

```
symptoms: illnesses: diagnosis: appointments:

id name id name FK_symptoms? id ? ? id FK_patient_name FK_diagnosis
1 fever 1 pneumonia

Frame challenge: unless this is a toy project not intended for a real-life application I suggest you study existing models that represent healthcare data instead of reinventing the wheel. There are more complexities in this area than you might think. One example would be the HL7 FHIR model, which, incidentally, has multiple open-source server implementations that are ready to be used.

This intro on Coursera might be helpful too.

StackExchange Database Administrators Q#291425, answer score: 12