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

Keys relation

*id	owner	name	domain	required	default
1	Group::Registration	wishes		f
2	Group::Registration	room	1	t	0
3	User::SnoreFiend	snores	2	t	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:

Domains relation

*domain_id	*choice_id	choice
1	0	Unassigned
1	1	Room 1
1	2	Room 2
1	3	Room 3
2	0	Unknown
2	1	No
2	2	Yes
2	3	YES

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

KV-Pairs relation

*id	ref	ref_id	key	value
1	user	53	1	Would like to be with Jane
2	user	55	1	Would like to be with Joe
3	user	57	1	(none, this record needs not actually exist…)
4	user	57	3	3 # YES, snores
5	user	53	2	2 # Room 2
6	user	55	2	2 # Room 2
7	user	57	2	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:

Keys relation

*id	owner	name	required	default
1	Group::Registration	wishes	f
2	Group::Registration	room	t	Unassigned
3	User::SnoreFiend	snores	t	Unknown

KV-Pairs relation

*id	ref	ref_id	key	value
1	user	53	1	Would like to be with Jane
2	user	55	1	Would like to be with Joe
3	user	57	1	(none, this record needs not actually exist…)
4	user	57	3	Oh my goodness yes, like a sawmill
5	user	53	2	Room 2
6	user	55	2	Room 2
7	user	57	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.

Keys relation

*id	owner	rel	name
1	Group::Registration	User	wishes
2	Group::Registration	User	room
3	User::SnoreFiend	User	snores

KV-Pairs relation

*id	ref_id	key	value
1	53	1	Would like to be with Jane
2	55	1	Would like to be with Joe
3	57	1	(none, this record needs not actually exist…)
4	57	3	Oh my goodness yes, like a sawmill
5	53	2	Room 2
6	55	2	Room 2
7	57	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.