1

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 :-(

rayjay
  • 359
  • 3
  • 16

2 Answers2

3

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!

yuvi
  • 18,155
  • 8
  • 56
  • 93
  • What i was trying to convey is, that django does not "validate" such relations - all this takes place in database and what you describe in models is only for getting models to "work" not for optimising real queries. How you describe the relations and how fast they work is still only based how you create the tables and how you optimise the indexes. Django layer only creates objects based on data read from database. – Odif Yltsaeb Dec 30 '13 at 11:28
  • Thanks. The conclusion is disappointing but at least I haven't missed something obvious. The linked article actually expresses the same dilemmas and posits the option of an artificial `hash` with the consequential drawbacks. – rayjay Dec 30 '13 at 13:05
  • Don't be disappointed, the point I'm trying to make is similar to @OdifYltsaeb - you should always [prefer surrogate (meaningless) keys over natural (meaningful) ones](http://stackoverflow.com/questions/590442/deciding-between-an-artificial-primary-key-and-a-natural-key-for-a-products-tabl) – yuvi Dec 30 '13 at 15:34
1

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

Odif Yltsaeb
  • 5,575
  • 12
  • 49
  • 80
  • But in this case I can't set a single primary key on `Statement` which means identifying the correct record involves a query with multiple conditions `WHERE supplier=test.supplier AND total=test.total AND statement_date = test.statement_date`. That's ok where I can put in the SQL, but many Django packages assume that the foreign key will point to an object with a usable primary key, so much of the automation available is lost. For example I'm using `django-restframework` and I don't see how I'd update an `Invoice` with a link to the correct `Statement` using behind-the scenes magic. – rayjay Dec 30 '13 at 12:29
  • I think I need a single unique key on `Statement` but it has to be one that is persistent and still works if a record should be replaced by a new copy which would have the same visible fields but a different value for an `autonumber` `id`. Hence feeling driven towards a `slug` that combines my three identifying field values. – rayjay Dec 30 '13 at 12:30
  • Well primary key pointing to some other object/table is easyest way to understand connections between objects/tables. I'ts very hard to create 3rd party projects which take in account all possible relations between models. I understand that the issue is with csvimport project, which makes it hard for you to use relations, which make most sense for you. In this case, ditch the project and use pythons own csv module. – Odif Yltsaeb Dec 30 '13 at 12:52
  • I'm also using `django-restframework`, and I think any REST interface will also need a single key to identify an item in the resource – rayjay Dec 30 '13 at 13:33