CODEWITHSUNDEEP
djangodjango-models
Justin Andrew
Justin Andrew

Reputation: 242

Dictionary In Django Model

I'm struggling with how to structure my models to allow me to capture some raw information that, once the instance is saved, I will no longer care about programmatically but will still want to save for posterity.

Specifically, I need to capture a variety of data-points tied to specific days of the week, but really those are only useful in calculating the information I do care about (such as total hours, overtime, etc). While I could do this all in the Form/View I do want to save the raw data as I'll want to display it later (but wont need it for calculating anything).

I could create a Day model and use a many-to-one relationship, but I'd like to not have to query the db 7+ times every time I display or process a timesheet.

Any thoughts would be greatly appreciated.

class Timesheet(models.Model):
     #Begin Stuff I Care About
     employee = models.CharField()
     company = models.CharField()
     week_ending = models.DateField()

     total_hours = models.DecimalField()
     regular_hours = models.DecimalField()
     overtime_hours = models.DecimalField()
     doubletime_hours = models.DecimalField()
     #End stuff I care about

     #For Each Day of Week - Stuff I Will Not Care About
     time_in_monday = models.TimeField()
     lunch_out_monday = models.TimeField()
     lunch_in_monday = models.TimeField()
     time_out_monday = models.TimeField()
     total_monday = models.DecimalField(decimal_places=2)

     time_in_tuesday = models.TimeField()
     lunch_out_tuesday = models.TimeField()
     lunch_in_tuesday = models.TimeField()
     time_out_tuesday = models.TimeField()
     total_tuesday = models.DecimalField(decimal_places=2)

     ... #You get the idea

Upvotes: 0

Views: 96

Answers (1)

David542
David542

Reputation: 110113

I would suggest structuring your database in a more normalized fashion so that you can more easily query it and the table won't be so unwieldy. An example might be:

class Timesheet(models.Model):
    employee = models.CharField()
    company = models.CharField()
    time_in = models.DateTimeField()
    time_out = models.DateTimeField()

From this, you can save a point of data every time an employee checks in or checks out. You can also calculate their hours, etc. I suppose you may also want to save additional fields to suit your purposes, but the above should give you a good starting point.

If you wanted to get a query for John on Monday, you would use the WEEKDAY() function, for example:

SELECT time_in, time_out FROM Timesheet WHERE employee = "John" AND WEEKDAY(time_in) = 0

If your employees are from all over the world, you might want to store a tz_offset column in the database and keep all dates in UTC. If all the employees are working in the same timezone, then it's simpler and you can just store the time in the local timezone.

Upvotes: 1

Related Questions