View source for Wafer/KeyValueStore

= KeyValue Store =

A problem we've had in the past was that specific conferences had specific needs, and we also grew more hungry in terms of the data we wanted to collect. While things like t-shirt sizes and dietary preferences are mostly standard these days, e.g. participation in the assassins game is not. Previously, we'd modify the main database to cater for these needs, but the idea has come up to handle this differently, namely using a key-value store that can be associated with objects (attendees, talks, sponsors, etc.). Those familiar with the Debian bug tracking system are refered to the concept of "user tags" for the same sort of idea.

Here are some usage examples:

* The organisers of the Assassins game can associate is-participating and whatever else may be needed with attendees;
* Day trip organisers can keep track of choices and paid status;
* Room allocation folks can keep track of wishes and assign rooms right there;
* Sponsor status, logos, URLs, if that were desired.

If this concept is properly implemented, it could also be used for the core elements (t-shirt size, emergency contact, dietary preferences, …), allowing us to keep the core database clean and aligned with upstream.

It goes without saying that this is a feature destined for upstream. Issue and discussion are here: https://github.com/CTPUG/wafer/issues/147

== Database design considerations ==

'''Update: [https://docs.djangoproject.com/en/1.9/ref/contrib/contenttypes/#django.contrib.contenttypes.fields.GenericForeignKey generic foreign keys] should provide the main functionality. Thanks, to the IRC channel #django.'''

We'd need the following relations:

;Keys
:Presumably, properties of a key include who owns it (could relate to Django authentication), what it can be attached to (e.g. Users, or Talks), whether it's a mandatory field (& default value), and what values it takes.
:The question of what data type to store is a lot harder, and we probably can't use referential integrity here, i.e. just store text and rely on the application logic to ensure that e.g. the values are limited to valid choices.
;Domains
:Without referential integrity, we probably want to pre-define all the possible choices for a key value, so this would be a 1:n relation between a key and the possible choices. It does add quite the complexity (see below), and maybe the simpler approach that follows the complex example would be just fine too.
;Key-Value pairs
:This relation essentially links a key to a the combination of an ID and a value. The ID references the table to which the key applies, e.g. if the key is applicable to Talks, then id=34 refers to the talk with id=34. This is soft, but leveraging referential integrity would mean creating individual key-value relations for all types with which kv-pairs can be associated, and might just blow up in terms of complexity.

Let's consider the example of room allocation:

=== Example with domains (complex) ===

{| class="wikitable"
 |+ Keys relation
 ! scope="col" | *id
 ! scope="col" | owner
 ! scope="col" | name
 ! scope="col" | domain
 ! scope="col" | required
 ! scope="col" | default
 |-
 | style="background-color:#00ff00;"|1 || Group::Registration || wishes ||   || f ||
 |-
 | style="background-color:#aa33ff;"|2 || Group::Registration || room   || style="background-color:#ffff00;"|1 || t || style="background-color:#99ccff;"|0
 |-
 | style="background-color:#ff0033;"|3 || User::SnoreFiend || snores || style="background-color:#ff99cc;"|2 || t || style="background-color:#ccffcc;"|0
 |}

The NULL domain for record id=1 could translate to a free-form text domain. All other values in that field would translate to choices defined in the following relation:

{| class="wikitable"
 |+ Domains relation
 ! scope="col" | *domain_id
 ! scope="col" | *choice_id
 ! scope="col" | choice
 |-
 | style="background-color:#ffff00;"|1 || style="background-color:#99ccff;"|0 || style="background-color:#99ccff;"|Unassigned
 |-
 | style="background-color:#ffff00;"|1 || 1 || Room 1
 |-
 | style="background-color:#ffff00;"|1 || style="background-color:#ffcc99;"|2 || style="background-color:#ffcc99;"|Room 2
 |-
 | style="background-color:#ffff00;"|1 || style="background-color:#ffaa00;"|3 || style="background-color:#ffaa00;"|Room 3
 |-
 | style="background-color:#ff99cc;"|2 || style="background-color:#ccffcc;"|0 || style="background-color:#ccffcc;"|Unknown
 |-
 | style="background-color:#ff99cc;"|2 || 1 || No
 |-
 | style="background-color:#ff99cc;"|2 || 2 || Yes
 |-
 | style="background-color:#ff99cc;"|2 || 3 || YES
 |}

And then finally, the kv-pairs relation ties it together:

{| class="wikitable"
 |+ KV-Pairs relation
 ! scope="col" | *id
 ! scope="col" | ref
 ! scope="col" | ref_id
 ! scope="col" | key
 ! scope="col" | value
 |-
 | 1 || user || 53 || style="background-color:#00ff00;"|1 || Would like to be with Jane
 |-
 | 2 || user || 55 || style="background-color:#00ff00;"|1 || Would like to be with Joe
 |-
 | 3 || user || 57 || style="background-color:#00ff00;"|1 || (none, this record needs not actually exist…)
 |-
 | 4 || user || 57 || style="background-color:#ff0033;"|3 || 3     # YES, snores
 |-
 | 5 || user || 53 || style="background-color:#aa33ff;"|2 || style="background-color:#ffcc99;"|2     # Room 2
 |-
 | 6 || user || 55 || style="background-color:#aa33ff;"|2 || style="background-color:#ffcc99;"|2     # Room 2
 |-
 | 7 || user || 57 || style="background-color:#aa33ff;"|2 || style="background-color:#ffaa00;"|4     # Room 3
 |}

=== Example without domains (simpler) ===

Now, this is all mighty complicated and possibly over-engineered. An obvious simplification is to remove the concept of "domains" entirely and just provide free-text values, expecting the UI or the scripts that the room allocation people will write/use to enforce integrity:

{| class="wikitable"
 |+ Keys relation
 ! scope="col" | *id
 ! scope="col" | owner
 ! scope="col" | name
 ! scope="col" | required
 ! scope="col" | default
 |-
 | style="background-color:#00ff99;"|1 || Group::Registration || wishes || f ||
 |-
 | style="background-color:#aa33ff;"|2 || Group::Registration || room   || t || Unassigned
 |-
 | style="background-color:#ff0033;"|3 || User::SnoreFiend || snores || t || Unknown
 |}

{| class="wikitable"
 |+ KV-Pairs relation
 ! scope="col" | *id
 ! scope="col" | ref
 ! scope="col" | ref_id
 ! scope="col" | key
 ! scope="col" | value
 |-
 | 1 || user || 53 || style="background-color:#00ff99;"|1 || Would like to be with Jane
 |-
 | 2 || user || 55 || style="background-color:#00ff99;"|1 || Would like to be with Joe
 |-
 | 3 || user || 57 || style="background-color:#00ff99;"|1 || (none, this record needs not actually exist…)
 |-
 | 4 || user || 57 || style="background-color:#ff0033;"|3 || Oh my goodness yes, like a sawmill
 |-
 | 5 || user || 53 || style="background-color:#aa33ff;"|2 || Room 2
 |-
 | 6 || user || 55 || style="background-color:#aa33ff;"|2 || Room 2
 |-
 | 7 || user || 57 || style="background-color:#aa33ff;"|2 || Room 3
 |}

=== Example without domains or validation, but object-specific keys ===

The following pulls the relation into the key definition (it's not likely that
a key will be re-used e.g. for Users and Talks), and also gets rid of all
validation (domain, required/default). Values are always text strings and it's
up to users (going via REST) to convert/validate their content.

{| class="wikitable"
 |+ Keys relation
 ! scope="col" | *id
 ! scope="col" | owner
 ! scope="col" | rel
 ! scope="col" | name
 |-
 | style="background-color:#00ff99;"|1 || Group::Registration || User || wishes
 |-
 | style="background-color:#aa33ff;"|2 || Group::Registration || User || room
 |-
 | style="background-color:#ff0033;"|3 || User::SnoreFiend || User || snores
 |}

{| class="wikitable"
 |+ KV-Pairs relation
 ! scope="col" | *id
 ! scope="col" | ref_id
 ! scope="col" | key
 ! scope="col" | value
 |-
 | 1 || 53 || style="background-color:#00ff99;"|1 || Would like to be with Jane
 |-
 | 2 || 55 || style="background-color:#00ff99;"|1 || Would like to be with Joe
 |-
 | 3 || 57 || style="background-color:#00ff99;"|1 || (none, this record needs not actually exist…)
 |-
 | 4 || 57 || style="background-color:#ff0033;"|3 || Oh my goodness yes, like a sawmill
 |-
 | 5 || 53 || style="background-color:#aa33ff;"|2 || Room 2
 |-
 | 6 || 55 || style="background-color:#aa33ff;"|2 || Room 2
 |-
 | 7 || 57 || style="background-color:#aa33ff;"|2 || Room 3
 |}

== UI design considerations ==

Using this from scripts via the Django API should be pretty straight forward.

Doing so via the web interface (not the admin interface) probably requires quite a lot of good thinking, but can also be done.

<!-- vim:ft=mediawiki -->

[[Category:General]]