One-many equivalent in Kassandra and data model optimization

Question

I model my database in Kassandra based on RDBMS. I want to know how I can create a one-to-many relationship that is embedded in the same column name and simulate my table to fit the following queries.

For instance:

Boxes:{ 23442:{ belongs_to_user: user1, box_title: 'the box title', items:{ 1: { name: 'itemname1', size: 44 }, 2: { name: 'itemname2', size: 24 } } }, { ... } } 

I read that it is preferable to use compound columns instead of supercommands, so I need an example of the best way to implement this. My queries are similar to:

• Get items for a window by ID
• get the top 20 boxes with their elements (to display a range of cells with their elements on the page)
• update element size by element identifier (size increment by number)
• get all fields by user ID (all fields belonging to a specific user)

I expect a lot of entries to resize each item in the field. I want to know how best to implement it without the need for supercommands. In addition, I do not mind the solution, which takes into account the new features of Cassandra 1.2, because I will use it in production.

thanks

Answer 1

This particular model is somewhat complex for a number of reasons.

For example, with a box identifier as a string, a query for a range of cells will require a range query in Cassandra (as opposed to slice a column), which means using an ordered delimiter. An ordered delimiter is almost always a bad idea.

Another problem is the need to increase the size of the element, since this requires the use of a counter column family. Bar columns only store counter values.

Having noted the need for several box identifiers for a moment, you can simulate this using several tables in CQL3 as follows:

 CREATE TABLE boxes ( id int PRIMARY KEY, belongs_to_user text, box_title text, ); CREATE INDEX useridx on boxes (belongs_to_user); CREATE TABLE box_items ( id int, item int, size counter, PRIMARY KEY(id, item) ); CREATE TABLE box_item_names ( id int PRIMARY KEY, item int, name text ); BEGIN BATCH INSERT INTO boxes (id, belongs_to_user, box_title) VALUES (23442, 'user1', 'the box title'); INSERT INTO box_items (id, item, name) VALUES (23442, 1, 'itemname1'); INSERT INTO box_items (id, item, name) VALUES (23442, 1, 'itemname2'); UPDATE box_items SET size = size + 44 WHERE id = 23442 AND item = 1; UPDATE box_items SET size = size + 24 WHERE id = 23442 AND item = 2; APPLY BATCH -- Get items for box by ID SELECT size FROM box_items WHERE id = 23442 AND item = 1; -- Boxes by user ID SELECT * FROM boxes WHERE belongs_to_user = 'user1'; 

It is important to note that the BATCH mutation above is both atomic and isolated.

From a technical point of view, you can also denormalize it all into one table. For instance:

 CREATE TABLE boxes ( id int, belongs_to_user text, box_title text, item int, name text, size counter, PRIMARY KEY(id, item, belongs_to_user, box_title, name) ); UPDATE boxes set size = item_size + 44 WHERE id = 23442 AND belongs_to_user = 'user1' AND box_title = 'the box title' AND name = 'itemname1' AND item = 1; SELECT item, name, size FROM boxes WHERE id = 23442; 

However, this does not give any guarantees of correctness. For example, this model allows items in the same box to have different users or names. And since the boxes family does this with the column counter family, it limits how you can change the layout in the future.

Answer 2

I think first in PlayOrm objects, then show the column model below ....

 Box { @NoSqlId String id; @NoSqlEmbedded List<Item> items; } User { @NoSqlId TimeUUID uuid; @OneToMany List<Box> boxes; } 

The user is then a line like so

 rowkey = uuid=<someuuid> boxes.fkToBox35 = null, boxes.fktoBox37=null, boxes.fkToBox38=null 

Note that the form above has a columname = value, where some of the column names are compound and some are not.

The box is more interesting and says that Item has field names and idnumber, then the line string will be

 rowkey = id=myid, items.item23.name=playdo, items.item23.idnumber=5634, itesm.item56.name=pencil, items.item56.idnumber=7894 

I'm not sure what you meant if you got the top 20 boxes? What does the number of elements in them mean?

Dean

Answer 3

You can use a query-oriented methodology to model data. You have three broad access paths:
1) section for request
2) section + for the request (one or more sections)
3) table or table + for query

The most effective option is a " query section ". This article can help you in this case step by step. this example is a one-to-many expression.

And according to this , you will have several tables with some similar columns. You can manage this using Materialized Browsing or a batch journal (as an alternative approach).