Structure of tables in user question and answer template

2

I have a question in the structure of some tables and I hope you can clarify in detail below.

Scenario

The system will generate a way to create dynamic forms and questions in order to be answered by users already registered. Responses should be stored too.

A form can be associated with N clients, in the same way that a client can be associated with N forms. Only the forms associated with the user can be answered. The forms will have a set of questions and another table should be provided for each user's response.

Doubt

The form must be associated with the user to allow him to respond, so I created two diagrams. Which one is the best one to use?

Diagram 1

The Usuario_Formulario table is associated with the forms that each user can respond to and in the Resposta table, will store each user's answer (referring to the form-question).

Diagram2

Inthisseconddiagram,IremovethedirectrelationshipfromUsuario-RespostaandmaketherelationshipappearbetweenUsuario_Formulario-Resposta

Is there any better way to create this structure? You can tweak the relationships and keys of each table.

OBS: Do not take into account non-key field issues, just create some to give a better view of the problem.     

asked by anonymous 19.02.2018 / 01:03

2 answers

1

Your question is above average, but it is still difficult to state something without knowing the needs.

My primary understanding is that 2 is better, a response should be bound to the form and not to the user. The form is already linked to the user. Of course, I'm just speculating, I do not know the real problem.

I do not know, but I think you're using a primary key made up of the user ID and form. I think, and I just think, that I should have a ID in the form and UsuarioID be just a foreign key, in response I would only need the form.

    
19.02.2018 / 04:04
0

Data modeling should be independent of the database manager that will be used. Only in the physicalization stage is the database structure defined (tables, columns, primary keys, indexes etc).

Assuming that each question can only belong to a single form, what I realize is the presence of 3 entities: USER, FORM and QUESTION. In addition, two relationships: USERFORMULARY and RESPONSE.

The USER-FRAME relationship establishes the list of forms that each user must respond to. It is a N: M relationship between the USER and FORM entities.

The RESPONSE relationship contains the responses of each user to each question on each form. It is a relationship between the USER, FORM, and QUESTION tables where the answer is the relationship attribute itself.

As for the physical implementation, sketch available at link

    
19.02.2018 / 14:57