1
Good afternoon, I am developing a system for my learning and I came across a situation where I was in doubt.
I have the table in the bank where the questions are recorded.
Table question : -Idpergunta; -Idusuários;
Both what you asked and what you answered record in this table.
My question is the following, should I create an answer table to record the answers or write in the same Question table ?
2
The answer is: it depends.
A very good tip when designing a table in the database is to ask what its working entities are and what relationships these entities have with each other. This model of abstraction is the modelo entidade relacionamento, MER.
When thinking about a relationship, also think about what the aridity of this relationship. For example, if a user can ask multiple questions and can write multiple answers, each question can have multiple answers, we have the following model:
Note that I did not put the attributes in the model, this is another step.
The primary keys can often be artificial keys; in other cases, we can create the artificial keys to make the connection, respecting only the uniqueness. That article explains here some of what is the artificial key.
After knowing what their entities are and how they relate, we transform the MER in a model closer to the relational database, the Modelo Relacional, MR. The relational model is a way of graphically writing table structures. In phpmyadmin it is possible to create tables using this template through a very fun interface to move. I found that response in the international SO on how to enable this feature on phpmyadmin.
The MR is composed of tables and links/foreign keys primarily. A table is a well-known data collection, a tupla whose positions are named. For example, when it is said that a user needs to have a name and a score, it can be represented by tupla ('Fernando', 64), being the first position of tupla the user name and second position the score of the same user; each column of the table is represented by a position in the tupla.
I like to use the following technique to convert from MER for MR:
1-> se o relacionamento de A--B é de 1 para 1, então coloca os atributos de B em A
2-> se o relacionamento de A--B é de 1 para 0..1, então coloca os atributos de B em A apenas caso exista dados pertinentes, sendo todos nulos caso não exista B
3-> se o relacionamento de A--B é de 1 para 0..n, então coloca em B uma chave estrangeira para A
4-> se o relacionamento de A--B é de 0..n para 0..n, então crio uma tabela de ligação que contém chave estrangeira para A e chave estrangeira para B
5-> toda tabela advinda de uma entidade tem uma chave artificial
6-> todo atributo da entidade vira uma coluna na tabela
Thus the MER posted above turns into the following MR (assuming answer has text, question has text and user has name and points):
PERGUNTA(pk_pergunta, fk_usuario, texto_pergunta)
RESPOSTA(pk_resposta, fk_usuario, fk_pergunta, texto_resposta)
USUARIO(pk_usuario, nome, pontos)
Of course, you could model entities differently. You could say that there are posts, being that a post can be opening or be a post in response, provided that the post if it refers to another post:
That turns into the next MR:
POST(pk_post, fk_usuario, fk_post_original, texto_post)
USUARIO(pk_usuario, nome, pontos)
Browser other questions tagged database
You are not signed in. Login or sign up in order to post.
I don’t know. It depends on what you need to do with this information, it depends on other points in the database, it depends on the exact concept that this means. You have to assess the advantages and disadvantages of doing one way or another.
– Maniero
a doubt that arose while developing at the level of learning ...
– Fernando
Can the questions be answered more than once? If so, it is already a strong indicative that will have to create a new table - if it is to work with normalization, of course. Just like the bigown said, you need to make the necessary withdrawals. In both ways you will be "right".
– Ismael