CODEWITHSUNDEEP
djangodjango-modelsforeign-keys
rayjay
rayjay

Reputation: 369

Do Django models really need a single unique key field

Some of my models are only unique in a combination of keys. I don't want to use an auto-numbering id as the identifier as subsets of the data will be exported to other systems (such as spreadsheets), modified and then used to update the master database.

Here's an example:

class Statement(models.Model):
    supplier = models.ForeignKey(Supplier)  
    total = models.DecimalField("statement total", max_digits=10, decimal_places=2)
    statement_date = models.DateField("statement date")
    ....


class Invoice(models.Model):
    supplier = models.ForeignKey(Supplier)
    amount = models.DecimalField("invoice total", max_digits=10, decimal_places=2)
    invoice_date = models.DateField("date of invoice")
    statement = models.ForeignKey(Statement, blank=True, null=True)
    ....

Invoice records are only unique for a combination of supplier, amount and invoice_date

I'm wondering if I should create a slug for Invoice based on supplier, amount and invoice_date so that it is easy to identify the correct record.

An example of the problem of having multiple related fields to identify the right record is django-csvimport which assumes there is only one related field and will not discriminate on two when building the foreign key links.

Yet the slug seems a clumsy option and needs some kind of management to rebuild the slugs after adding records in bulk.

I'm thinking this must be a common problem and maybe there's a best practice design pattern out there somewhere.

I am using PostgreSQL in case anyone has a database solution. Although I'd prefer to avoid that if possible, I can see that it might be the way to build my slug if that's the way to go, perhaps with trigger functions. That just feels a bit like hidden functionality though, and may cause a headache for setting up on a different server.

UPDATE - after reading initial replies

My application requires that data may be exported, modified remotely, and merged back into the master database after review and approval. Hidden autonumber keys don't easily survive that consistently. The relation invoices[2417] is part of statements[265] is not persistent if the statement table was emptied and reloaded from a CSV.

If I use the numeric autonumber pk then any process that is updating the database would need to refresh the related key numbers or by using the multiple WITH clause.

If I create a slug that is based on my 3 keys but easy to reproduce then I can use it as the key - albeit clumsily. I'm thinking of a slug along the lines:

u'%s %s %s' % (self.supplier, 
    self.statement_date.strftime("%Y-%m-%d"), 
    self.total)

This seems quite clumsy and not very DRY as I expect I may have to recreate the slug elsewhere duplicating the algorithm (maybe in an Excel formula, or an Access query)

I thought there must be a better way I'm missing but it looks like yuvi's reply means there should be, and there will be, but not yet :-(

Upvotes: 1

Views: 341

Answers (2)

yuvi
yuvi

Reputation: 18447

What you're talking about it a multi-column primary key, otherwise known as "composite" or "compound" keys. Support in django for composite keys today is still in the works, you can read about it here:

Currently Django models only support a single column in this set, denying many designs where the natural primary key of a table is multiple columns [...] Current state is that the issue is accepted/assigned and being worked on [...]

The link also mentions a partial implementation which is django-compositekeys. It's only partial and will cause you trouble with navigating between relationships:

support for composite keys is missing in ForeignKey and RelatedManager. As a consequence, it isn't possible to navigate relationships from models that have a composite primary key.

So currently it isn't entirely supported, but will be in the future. Regarding your own project, you can make of that what you will, though my own suggestion is to stick with the fully supported default of a hidden auto-incremented field that you don't even need to think about (and use unique_together to enforce the uniqness of the described fields instead of making them your primary keys).

I hope this helps!

Upvotes: 3

Odif Yltsaeb
Odif Yltsaeb

Reputation: 5666

No.

Model needs to have one field that is primary_key = True. By default this is the (hidden) autofield which stores object Id. But you can set primary_key to True at any other field. I've done this in cases, Where i'm creating django project upon tables which were previously created manually or through some other frameworks/systems.

In reality - you can use whatever means you can think of, for joining objects together in queries. As long as query returns bunch of data that can be associated with models you have - it does not really matter which field you are using for joins. Just keep in mind, that the solution you use should be as effective as possible.

Alan

Upvotes: 1

Related Questions