CODEWITHSUNDEEP
databasesqlitedatabase-designnosqlrelational-database
user40129
user40129

Reputation: 833

Which database can do this?

In the recent project, I need a database that does this.

each item is key value pair, each key is a multi-dimensional string, so for example

item 1: key :['teacher','professor'] value: 'david'

item 2: key :['staff', 'instructor', 'professor'] value: 'shawn'

so each key's length is not necessarily the same. I can do query like

anyone with both ['teacher','staff'] as keys.

Also I can add another item later easily, for example, a key-value pair like.

item 3: key :['female', 'instructor', 'professor','programmer'] value: 'annie'

so the idea is that I can tag any array of keys to a value, and I can search by a subset of keys.

Upvotes: 0

Views: 64

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Since (judging on your comments) you don't need to enforce uniqueness, these are not actually "keys", and can be more appropriately thought of as "tags" whose primary purpose is to be searched on (not unlike StackOverflow.com tags).

The typical way of implementing tags in a relational database looks something like this:

enter image description here

Note the order of fields in the junction table TAG_ITEM primary key: since our goal is to find items of given tag (not tags of given item), the leading edge of the index "underneath" PK is TAG_ID. This facilitates efficient index range scan on given TAG_ID.

Cluster TAG_ITEM if your DBMS supports it.

You can then search for items with any of the given tags like this:

SELECT [DISTINCT] ITEM_ID
FROM
    TAG
    JOIN TAG_ITEM ON TAG.TAG_ID = TAG_ITEM.TAG_ID
WHERE
    TAG_NAME = 'teacher'
    OR TAG_NAME = 'professor'

And if you need any other fields from ITEM, you can:

SELECT * FROM ITEM WHERE ITEM_ID IN (<query above>)

You can search for items with all of the given tags like this:

SELECT ITEM_ID
FROM
    TAG
    JOIN TAG_ITEM ON TAG.TAG_ID = TAG_ITEM.TAG_ID
WHERE
    TAG_NAME = 'teacher'
    OR TAG_NAME = 'professor'
GROUP BY
    ITEM_ID
HAVING
    COUNT(*) = 2

Upvotes: 1

Argeman
Argeman

Reputation: 1353

PostgreSQL can do something similar with it's hstore data-format: http://www.postgresql.org/docs/9.1/static/hstore.html

Or maybe you search for arrays?: http://postgresguide.com/sexy/arrays.html

Upvotes: 0

Related Questions