Two things I have discovered about Django schema design

January 29, 2011

I've now gotten far enough into my application to have stubbed my toes on a few beginner's mistakes in Django and ORM schema design. Fortunately I've done this during development instead of production, so it's merely a valuable learning experience.

First, I should never make schema fields into explicit primary keys, no matter how attractive it seems and how SQL-proper it is. Especially I should not do this when the field is shown to or used by humans. Otherwise, sooner or later I will discover that either there's a typo in that field in some record or that there's some unanticipated need to change it after all. And then of course foreign keys make everyone else get heartburn, at least if you're working through Django's admin interface.

Letting Django have its invisible 'id' primary key field is much, much simpler and far more future proof, no matter how much it offends the SQL-aware portion of my brain.

(A related tragic mistake is directly linking audit records to the object that they're reporting on as a foreign key relationship, thereby insuring that either you can never delete the master object or that when you delete the master object you automatically lose all audit records associated with it.)

The second is that you cannot have an optional character or text field that is also unique (for instance, an optional login). Even if your database allows this in general (not all SQL databases allow UNIQUE fields to have several rows be NULL in the field), Django's admin interface will turn an unset field into an empty string, not a NULL, which really does have to be unique in the field.

Django's documentation covers that you really don't want to try to have fields that can be both blank and null (and what happens with character fields), but the documentation doesn't quite spell out the full consequences. Learn from my example; you really don't want to do that, even if it looks like you're getting away with it.

(If you are a particularly optimistic person, you can view this as Django saving you from making unportable assumptions about database behavior.)


Comments on this page:

From 24.216.252.176 at 2011-01-31 08:45:09:

First, I should never make schema fields into explicit primary keys, no matter how attractive it seems and how SQL-proper it is.

I'm glad you wrote on this topic: I'm going back and forth about continuing to use Django because idiomatic use conflicts with what I've been reading about proper schema design.

I'm not sure your data will be best served by giving up the database schema you want in deference to the conventions and limitations of the (Django ORM) framework. In "Wrecking Your Database", there are warnings about auto-id PKs.

http://it.toolbox.com/blogs/database-soup/wrecking-your-database-33298

I'd suggest choosing a full-featured database (e.g. PostgreSQL), and finding out (e.g. on #postgresql) if various DDL features can mitigate the need to change your schema to fit Django. If there are extra checks and constraints at the database DDL level, perhaps Django will cooperate, even if unaware.

I'd be very interested in reading a follow-up post.

From 157.166.167.129 at 2011-01-31 09:51:34:

Re: "A related tragic mistake is directly linking audit records to the object that they're reporting on as a foreign key relationship, thereby insuring that either you can never delete the master object or that when you delete the master object you automatically lose all audit records associated with it."

New in Django Development version: ForeignKey "on delete" property (http://docs.djangoproject.com/en/1.2/ref/models/fields/#django.db.models.ForeignKey.on_delete).

You can choose whether or not the the related objects are deleted.

-Hunter Ford

By cks at 2011-02-02 13:08:57:

I don't think that Django is the source of my issues here. I wrote about this in longer length in PrimaryVsSurrogateKeys.

Written on 29 January 2011.
« The amusement of minimalist spam
An obvious realization about SQL foreign keys »

Page tools: View Source, View Normal, Add Comment.
Search:
Login: Password:
Atom Syndication: Recent Comments.

Last modified: Sat Jan 29 02:26:34 2011
This dinky wiki is brought to you by the Insane Hackers Guild, Python sub-branch.