title: How to properly design a database for rating system with multiple rating criterias
body: <p>I am doing rating system on my current django project and I need to implement database for rating. Database would be simple, if I didn't have multiple rating criteries, now I have to ask:</p>
  <p>How would you design a database with these objects:
  User
  RatedObject
  Rating</p>
  <p>there will ofcourse be multiple users and objects to be rated AND multiple rating criterias.</p>
  <p>Now my current idea would be to go for few separate tables with each of the objects such as:</p>
  <pre><code>USER(pk=id, fk= rating.id)
  RATED_OBJECT(pk=id, fk= rating.id, overall_attribute_1, overall_attribute_2, overall_rating)
  RATING(pk=user.id, pk=rated_object.id, fk=rated_attribute1, fk=rated_attribute2)
  RATED_ATRIBUTE(pk=id, fk=type, value)
  TYPE(pk=id, name) - 2 types since we have 2 attributes to be rated
  </code></pre>
  <p>(now overal_rating will be average of all overall attributes and each overal attribute will be average of all attributes of one type from ratings, where the id of rated object will be same)</p>
  <p>I have a bad feeling about doing this 'multiple-FK-to-one-PK' operation. Would it make more sence to make table for each rated attribute? Or maybe say **** it and have values in the RATING itself and screw RATED_ATTRIBUTE and TYPE table? What do you guys think?</p>
  <p>EDIT: IF my guess was right- meaning I could connect RATING with RATED_ATTRIBUTE multiple times (multiple fk to 1 pk), I need to set constraints, so when rating happens, it all goes to fields of RATING correctly
  (when rating attribute one, in RATING i need to ensure that in RATED_ATTRIBUTE the type is exactly the one like in RATING). Now, I cannot get my head arround, how to do that whidj Django's CheckConstraint, any ideas?
  snippet:</p>
  <pre><code>class RatedAttribute(models.Model):
      TYPE = (
          ('attribute1', 'attribute1'),
          ('attribute2', 'attribute2'),
  
      )
  
      id      = models.IntegerField(primary_key= True, auto_created=True)
      type    = models.CharField(max_length=120, choices=TYPE, default=None)
      value   = models.IntegerField()
  
  
  class Rating(models.Model):
      user_id           = models.ManyToManyField(User, through='RatingUser')
      rated_object_id   = models.ManyToManyField(RatedObject, through='Rating_Object')
  
      attribute1  = models.ForeignKey(RatedAttribute, on_delete=models.CASCADE)
      attribute2  = models.ForeignKey(RatedAttribute, on_delete=models.CASCADE)
  
      class Meta:
          constraints = [
              CheckConstraint(
                  *NO IDEA HOW TO FILL THIS CONSTRAINT(s)*, name='something')
          ]
  
      def __str__(self):
          return self.place
  </code></pre>
  <p>code is with proper connecting <em>Rating_Object</em></p>