Changes between Version 20 and Version 21 of MultipleColumnPrimaryKeys

Timestamp:

Mar 11, 2013, 12:37:20 PM (12 years ago)

Author:

Chris Wilson

Comment:

comments on other peoples' proposed "simple" solutions

MultipleColumnPrimaryKeys

@@ -28 +28 @@
 bignose: In Python, the obvious choice is to make the primary key a ''set'' (a set of columns, in the model definition; a set of values, for a given instance). A compound key does not have an implied order, and a Python set is the obvious type to represent this.
 
+gcc: @bignose: there is often only one correct order. RITA uses (site_id,local_id) as a composite PK, you cannot reverse them! The values might sometimes accidentally be the same too, e.g. (1801,1801) is a valid RITA ID that might happen one day. A tuple sounds more appropriate to me.
+
 mjm: In my work using sequences I chose - not without some pain - to make all keys be sequences (and all keys' names were sequences, too).  This is at least consistent, and the changes were straightforward.  The fact that strings are themselves sequences actually made it more difficult, since overlooked cases would (often) misbehave rather than tossing an exception immediately - a point in favor of using mappings, maybe.
 
@@ -49 +51 @@
  * djansoft: It can be done using unique-together.
  * Tobu: You can't use just one key. A use case for illustration: a NamespacedTag model with a CK made of a namespace and a name. The primary key can't be just the namespace or just the name since that would be ambiguous. The only solution (and I dislike it since it is bad modelling and forces database accesses to be serialized on a counter) is to use an AutoField.
+ * gcc: @notnotpeter, @djansoft: in RITA, there would be thousands of records with the same site_id or the same local_id in some tables. Neither column could sensibly be made the unique key. similarly, my current project has a lesson translation table where the key is (lesson_id, language).
  * toszter: Call me nutty, but why not declare any number of columns as primary key in your model, which django then uses to create a "hidden" pk_composite column that's just a hash of the three pk values in the row?  When you do a lookup on a pk, you assume the ENTIRE combination of values is the primary key, nothing more or less. So when you look up according to the values needed to create the full pk_composite, the hash always computes and if the hash algorithm is public, can be used off the URL, the querystring in db lookups, and just about anywhere.  Seems pretty simple in my mind but then again I am not going into great detail here.
+ * gcc: @toszter: the reverse lookup (hash -> matching records) is extremely expensive when the table is large. e.g. in RITA, you might have to compute the hash of a million records every time you want to retrieve one, i.e. every time you access http://rita.example.com/admin/transaction/<hash>/
 
 = Links / Notes =