Arpan Arpan - 6 months ago 12
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?

Answer

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)

views.py:

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
        request.user.user_reading_history.create(poem=poem)

    # etc etc

didnot test it though

Comments