Skip to content

Searching a key-value schema

July 27, 2011

Sometimes when designing a database schema the designer cannot determin the exact content that will be stored in the databse in advanced. The common solution for that is designing a Key-Value pairs schema (Sometimes refered as Attribute-Value schema).

There are a few flavors to the key-value schema I this post we will look at a simple one with one table that repeats the key name for every occurence of the key, and a more complicated one that uses a seperate key table.

Simple schema:

USER_ATTRIBUTES
USER_ID KEY_NAME VALUE
1 IS_GOLD_MEMBER 1
1 START_DATE 2010
2 IS_GOLD_MEMBER 0
2 START_DATE 2010
3 IS_GOLD_MEMBER 1
3 START_DATE 2008

Advanced schema

USER_ATTRIBUTES
USER_ID KEY_ID VALUE
1 1 1
1 2 2010
2 1 0
2 2 2010
3 1 1
3 2 2008
ATTRIBUTES_NAME
KEY_ID KEY_NAME
1 IS_GOLD_MEMBER
2 START_DATE

In scoped search there is a special declaration for a key-value db schema. The auto-completer complete both keys and values.

The scoped search declaration for the simple schema should look as follows:

scoped_search :in => :user_attributes, :on => :value,  :on_key=> :key_name, rename => :attr

For the second schema the search should be defined as follows:

scoped_search :in => :user_attributes, :on => :value, :in_key=> :attributes_name, :on_key=> :name

The auto completer will suggest the same syntax for both schema.

Advertisements

From → Design, Developers

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: