Table structure in user questions and answers template

Asked

Viewed 1,707 times

2

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

Setting

The system will generate a way to create forms and questions dynamics, in order to be answered by users already registered. The answers should also be stored.

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

Doubt

It is necessary that the form is associated with the user to allow him to answer it, so I created two diagrams. Which one is best used?

Diagram 1

Through the table Usuario_Formulario is associated with the forms that each user can answer and in the table Resposta, will store each answer (referring to the question form) of the user.

DER

Diagram 2

In this second diagram, I remove the direct relationship of Usuario-Resposta and make the relationship appear between Usuario_Formulario-Resposta

DER 2

Is there any other better way to create this structure? It can mess with the relationships and keys of each table.

OBS: Do not take into account the issues of non-key fields, just created a few to give a better view of the problem.

sql sql-server modeling

  • would use diagram 2. Would make some changes to have alternatives in case of objective questions and if necessary use the same question in multiple forms

  • Can the same question participate in more than one form? // Is the numbering of each question general or is it by form? // What is the difference between users and customers?

  • Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

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, an answer should be linked to the form and not to the user. The form is already linked to the user. Of course, I’m just speculating, I don’t know the real problem.

I don’t know, but I think you’re using a primary key composed of the user ID and the form. I think, and I just think, that there should be one ID on the form and UsuarioID be only a foreign key, then in response would only need the form.

0

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

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

The User Relatedform establishes the list of forms that each user must answer. It is a relationship N:M between the entities USER AND FORM.

The ANSWER relationship contains each user’s answers to each question in each form. It is a relationship between the tables USER, FORM and QUESTION where the answer is the relationship attribute.

As for the physical implementation, sketch available in http://sqlfiddle.com/#! 18/f21d5

Browser other questions tagged sql sql-server modeling

You are not signed in. Login or sign up in order to post.