如何使用MySQL和PHP进行问卷调查表结构？

Question

i'm currently working on a nutricional evaluation project and, i've come to a point, where i'm failing to see how to do some tables relationship.

What i want to do: The nutricionist can create questionnaires to facilitate his nutricional evaluation about his pacients. Others nutricionists would be allowed to use questionnaires that were created by other nutricionists. (So these questionnaires would be public to use).

These questionnaires would have 5 to 10 questions tiped by the nutricionist, and the anwser for these questions would be a text tiped by the nutricionist as well.

Program Flow for New pacient: nutricionist register a new pacient(name, high, weight etc) --> Nutricionist chooses or creates one or many questionnaire to help on his evaluation --> nutricionist asks the questions for his pacient --> nutricionist saves the pacient anwsers --> Program continues...

Program Flow for old pacient: Nutricionist finds pacient --> Nutricionist see the questionnaires related to that pacient (at this point, there could be questionnaires that were make by another nutricionist) --> Nutricionist can create a New questionnaire or see the questionnaires already related to that pacient (cannot erase or alter any questionnaire) --> Program continues....

Main question: how could i do the tables relationship​ of nutricionist, questionnaire and pacient? Create tables for the questions and awnsers? I'm seriously Lost.

Obs: English is not my native language, but o tried my best.

Answer 1

Well, you can create your tables in this format:

1. Patient's Table
Store details like the id (unique value to search for the patient in another table, doing an *AUTO_INCREMENT* would be perfect), name, weight, height, etc

2. Nutritionist's Table
Store details like id (unique value also), name, etc

3. Questionnaire Table
Store details like id (same as others above), patient_id, question, answer, etc

For your program flow:

Program Flow for New pacient: nutricionist register a new pacient(name, high, weight etc)

These Details will be entered into the patient's table

Nutricionist chooses or creates one or many questionnaire to help on his evaluation

These details will be stored or selected from the questionnaire table

nutricionist asks the questions for his pacient

These details will be FETCHED from the questionnaire table

nutricionist saves the pacient anwsers

These details will be saved (stored) in the questionnaire table

Nutricionist finds pacient

Select from patients table

Nutricionist see the questionnaires related to that pacient (at this point, there could be questionnaires that were make by another nutricionist)

Select from questionnaire table where the current patient id is equal to the one already stored in the table

Nutricionist can create a New questionnaire or see the questionnaires already related to that pacient (cannot erase or alter any questionnaire)

Insert into questionnaire table OR query from the questionnaire table

Hope this helps.