Good afternoon, I'm 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 written.
Table asks: -IdQuestion; -Users;
Both the questioner and the respondents record this table.
My question is the following, should I create an answer table to write responses or write to the same question table?
The answer is: it depends.
A very good tip when designing a table in the database is to ask what your work entities are and what relationships these entities have with each other. This abstraction model is the modelo entidade relacionamento , MER .
When thinking about a relationship, also think about the aridity of this relationship. For example, if a user can ask multiple questions and can write multiple answers, each question can have several answers, we have the following model:
NotethatIdidnotputtheattributesinthetemplate,thisisanotherstep.
Primarykeyscanoftenbeartificialkeys;inothercases,wecancreatetheartificialkeystomaketheconnection,respectingonlyoneness. This article explains here a little bit of what the key is artificial.
After knowing what their entities are and how they relate, we transform MER into a model closer to the relational database, the Modelo Relacional ", MR . The relational model is a way of writing table structures graphically. In phpmyadmin it is possible to create tables using this template through a very fun interface to move. I found this answer in the international OS on how to enable this functionality in phpmyadmin .
The MR is composed of tables and links / foreign keys primarily. A table is a well-known collection of data, a tupla whose positions are named. For example, when you say that a user needs to have a name and a punctuation, it can be represented by tupla ('Fernando', 64) , being the first position of tupla the name of the user and the second position the same user's punctuation ; each column of the table is represented by a position in tupla .
I like to use the following technique to convert from MER to 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
So, the MER posted above becomes 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 in a different way. You could say that there are posts , where post can be opening or be a post response, as long as post is referenced to another post :
Which transforms into the following MR :
POST(pk_post, fk_usuario, fk_post_original, texto_post)
USUARIO(pk_usuario, nome, pontos)