Search code examples
databasedata-modelingdata-warehouse

Am I modelling my warehouse tables the right way?

I'm designing a website where users answer surveys. I need to design a data warehouse to aggregate their responses. So far in my model I have:

  1. A dim table for Users.
  2. A dim table for Questions.
  3. A fact table for UserResponses. <= This is where I'm having the problem.

So the problem I have is that additional comments can be added to their responses. For example, somebody may come in and make 2 comments against a single response. How should I model this in the database?

I was thinking of creating another fact table for "Comments", and linking it to a record in UserResponses. Is this the right thing to do? This additional table would have something like the below columns:

  1. CommentText
  2. Foreign key relationship to fact.UserResponses.
Solution

  • Yes, your idea to create another table is correct. I would typically call it a "child" table rather than calling it another fact table.

    The key thing that you didn't mention is that the table comments still needs an ID field. A table without an ID would be bad design (although it is indeed possible to create the table with no ID) since you would have no simple way to refer to individual comments.