Model comments

Asked

Viewed 110 times

2

I’m creating a comment structure similar to Mercadolivre, how do I make the modeling contemplate and ensure that:

NOTE: I’m calling comments question and answer since this is basically how the Free Market model works.

In red questions and in green the answers.

inserir a descrição da imagem aqui

  1. There is only one level of answer for a given question.
  2. A question have several answers.
  3. An answer to a single question.
  4. No questions for an answer.
  5. Questions and answers for a single product.
  6. Be possible to be redirected to comment as here on Sopt (en.stackoverflow.com/questions/78765/problems-with-post-Angularjs/78773? noredirect=1#comment173023_78773)

What I’ve already done:

comments
--------

id INT AUTO_INCREMENT PRIMARY KEY,
comment_text VARCHAR(1000) NOT NULL,,
type INT NOT NULL,
user_id INT NOT NULL, #chave estrangeira para usuário que postou o comentario.
product_id INT NOT NULL, #chave estrangeira para o produto a qual os comentários se referem.
posted_to INT #fiquei com dúvida porque uma resposta é postada para uma pergunta mas uma pergunta não é postada para ninguém.
date_posted DATE
sql database modeling

  • What information will be recorded on posted_to? Do you have any specific questions? If I understand it seems to be all right. Some of these rules will obviously be guaranteed in the code and not in the modeling.

  • would be an integer for the product id or question

  • So what do you want to know? If it’s all right?

  • @bigown would like my modeling to cover the 6 points cited in the question and remedy my doubts I posted in modeling (it is possible that others arise so sanitize these)

  • Item 4 I don’t quite understand, someone could just post an answer about something that wasn’t asked, is that it? Item 6 depends on the application to be developed and not on database modeling.

1 answer

4


It seems to be all right according to the information provided. Of course the rules of what you can or cannot do, how to present (including links), will be determined by code (even if inside the database, which I don’t like) and not by modeling.

I’d only wear one NULL (looks like he was prepared for this) or value 0 in posted_to when by a question. There is no reason to duplicate in this column the information that is already in the product_id.

And if I understood what is the type, maybe, it is not necessary, because it can be inferred by the content of posted_to. But I do not know if this economy is worth doing, because in the future it can change something and this is not possible anymore. Of course I could solve the problem easily if is necessary. We can think about YAGNI here, or don’t try to be too Clever (the problem is deciding what is Clever too).

A date_posted was NOT NULL seems to be appropriate as well.

Then I could think of other things that are not included in this basic analysis. For example: the possibility of the comment being removed (but keeping it in the database) requires a column to indicate the state.

  • 1º have several posted_to with NULL or 0 would consume memory yet? 2º if remove the product_id how will I know which product question/answer reference?

  • Yes but this is not important. In many databases the NULL can consume less but is little relevant. There are Sgdbs that it does not consume anything but actually it is because it already consumes in excess otherwise. I don’t even know if any mainstrain today does this. And if you do, again it doesn’t matter. I didn’t tell you to remove anything on product_id, I told you not to repeat him on posted_ to when it’s a question. And I said type is a redundant information in the current form (and not necessarily this is bad), and if I understood what it serves.

  • I think you better read the answer again, I didn’t say NULL in the product_ id. I talked about posted_ to.

  • I was wrong in my comment I would like to ask if I leave product_id with NULL when it is an answer and posted_to with NULL when it is a question

  • Ah, yes, you can do this, but I wouldn’t, because it might make it harder for you to see me. Hypothetically it could merge the two columns into one and the column type define if there is a id product or question. But it would be Clever Moreover, it is not necessary and would make the consultations even more difficult. This kind of thing is what you did in the 60/70’s when you had a lot of resource constraints. And look what you got, this guy millennium bug.

  • Good clarification +25

  • bigown if you choose the option Clever I won’t be able to make the field posted_to is a foreign key because it would contain primary keys from two different tables, there is a way to circumvent?

  • 1

    Not opting for this :) Actually it is possible depending on how your application does. Passkey is not something so rigid if you do not want to as a rule in the database. But you only found one more reason not to choose this option. Do the simple and obvious. I gave as alternative, not as the best alternative.

  • Opting for the simple option how would I know which of my comments have not yet been answered? (the first solution I imagined is very expensive because I would have to search all over BD)

  • There you are already putting new requirements. You will probably have to do sub-query, but I haven’t given it much thought. But anyway modeling should not seek at first the optimization of performance, it should provide the information necessary to solve the problem. If the physical structure will have to be adapted to improve performance is a second step that must be done when measured necessary. In general an index will resolve the issue if there is slowness.

Show 5 more comments

Browser other questions tagged sql database modeling

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