I’m somewhat new to databases, and completely new to ORMs. I’m trying to design an “insurance claims portal” server, and I’d like to make sure that I’m approaching this correctly.
What I’m worried about is the case where, say, user A has 100 total claims, and wants to load the first page of their claims (up to 25, chronologically newest, owned by that user). I want to make sure that this event, which should occur on every user’s landing page after login, doesn’t inadvertently read and inspect potentially ~25% of all claims in the database.
I’d also really like to avoid denormalizing the data by storing a “duplicate” .policyholder column on each Claim when that’s already something that’s accessible from the .contract.policyholder.
What kind of index do I need to create for this? Will the indexes Django gives me automatically be sufficient to make this work reasonably well, or is there some specific index I should consider adding?
class Claim(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
created_at = models.DateTimeField(auto_now_add=True, db_index=True)
class State(models.IntegerChoices):
AWAITING_INSURER = 1, _("Awaiting Insurer")
AWAITING_CLIENT = 2, _("Awaiting Client")
CLOSED = 0, _("Closed")
state = models.IntegerField(choices=State)
# TODO: verify whether we need to add an index
# to make Claim.objects.filter(contract__policyholder=user) efficient
#
# I suspect/hope it will be at least TOLERABLY efficient as-is:
#
# * Django will(??) automatically create an index
# to efficiently look up Claim by Contract
# because of the contract ForeignKey field
#
# * Looking up Contract by policyholder should also be efficient
# since that property has db_index=True, too?
type = models.ForeignKey("policies.Policy", models.RESTRICT)
contract = models.ForeignKey("policies.Contract", models.RESTRICT)
class Meta:
ordering = ["-created_at"]
class Contract(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
created_at = models.DateTimeField(auto_now_add=True, db_index=True)
# the customer that "owns" the policy
policyholder = models.ForeignKey(get_user_model(), db_index=True, on_delete=models.SET_NULL, null=True)
endorsements = models.ManyToManyField("Policy", related_name="used_by")
is_rider_on = models.ForeignKey("self", null=True, blank=True, related_name="rider_set", on_delete=models.CASCADE)
effective_from = models.DateTimeField()
effective_to = models.DateTimeField()
class Meta:
ordering = ["-created_at"]