I have a basic problem for which I'm sure there is an widely-used pattern. I'm converting a relational database to an Accumulo store, but I'm having trouble mapping associations. The tables look like this:
comments
--------------
comments_id
incident_id
comment_text
poc_id
poc
---------
poc_id
fullname
I started with an Accumulo schema that seem straightfoward enough:
rowid column_family column_qualifier value
----- ------------ ---------------- -----
incident_id comments comment_text this is a comment
incident_id poc fullname bob jones
The problem is there can be multiple comments per incident, each with it's own POC. How can I associate a comment to a poc?
There are many ways to store this.
Remember, the schema is not rigid. Different rows can follow different patterns. Suppose you have an incident with several comments. Entries might look like this:
rowID, cf:cq, v
===============
incident|<uuid1>, poc:fullname, bob jones
comment|<uuid2>, incident:key, incident|<uuid1>
comment|<uuid3>, incident:key, incident|<uuid1>
But that approach above would require you to index the comments separately so that you can quickly find all comments belonging to a particular incident. Another approach would be to add a column qualifier to the incident row for each comment.
rowID, cf:cq, v
===============
incident|<uuid1>, comment|<uuid2>:text, my comment
incident|<uuid1>, comment|<uuid3>:text, my second comment
incident|<uuid1>, poc|<uuid4>:fullname, bob jones
incident|<uuid1>, poc|<uuid5>:fullname, john smith