Arpan Arpan - 4 months ago 5
SQL Question

SQL Table structure for storing daily activities of users

I'm using Django to build a poem reading app and would like to store the daily activities of users. Specifically I want to store which user read which poem and on what date. Also the number of poems is quite large (in thousands) and will probably keep increasing.

I am finding it difficult to think of a table structure to store this data that would not require manual updates. Having the days as rows and a list of User vs Poem read as the value against each day was my first thought, but the list would be very long for a substantial number of users.

Is there any standard structure to deal with cases like this? What can be done here?


assumed that you use Django's built-in User Model, I would keep it as simple as possible:

class Poem(models.Model):
    title = models.CharField(max_length=100)
    #.. other fields

class ReadingHistory(models.Model):
    user = models.ForeignKey(User, related_name="user_reading_history")
    poem = models.ForeignKey(Poem, related_name="user_read_poems")
    date = models.DateTimeField(auto_add=True)

def poem_reading_page(request, poem_id):
    if request.user.is_authenticated():
        poem = Poem.objects.get(pk=poem_id)

        #create reading history with this poem

    # etc etc

didnot test it though