I am just getting started with Accumulo and NoSQL databases and I am looking for some discussion on table design. I get the key value structure that is seen in the manual. However, if I am trying to recreate a relational database, I am not sure how relationships work. Can someone explain to some degree how to setup and "Hello World" database (i.e., manager-employee database). I want to use key-value implementation.
The first thing you should realize is that Accumulo is not a relational database. If you want to make it work like one, you need to create that functionality on top.
One way to do this would be to create rows that look like rows in a relational table, complete with foreign keys that you link together in your application's (an Accumulo client) code. Your client code must enforce the cascading updates/deletes and the other maintenance aspects of relations you may have come to expect from a relational database. Accumulo just stores and sorts Key/Value pairs for you. You decide the semantics of these Key/Value pairs.
Note: For illustration purposes, Accumulo Key/Value pairs will be represented as:
RowID:ColumnFamily:ColumnQualifier -> Value
EmployeeID1:Data:Name -> Chuck EmployeeID1:Data:Position -> Owner EmployeeID2:Data:Name -> Barbara EmployeeID2:Data:Position -> StoreManager EmployeeID2:Data:Manager -> ManagerID1 EmployeeID3:Data:Name -> Tom EmployeeID3:Data:Position -> SalesManager EmployeeID3:Data:Manager -> ManagerID2 EmployeeID4:Data:Name -> Sally EmployeeID4:Data:Manager -> ManagerID3 EmployeeID5:Data:Name -> Rick EmployeeID5:Data:Manager -> ManagerID3
ManagerID1:Info:EmployeeID -> EmployeeID1 ManagerID1:Employees:1 -> EmployeeID2 ManagerID2:Info:EmployeeID -> EmployeeID2 ManagerID2:Employees:1 -> EmployeeID3 ManagerID3:Info:EmployeeID -> EmployeeID3 ManagerID3:Employees:1 -> EmployeeID4 ManagerID3:Employees:2 -> EmployeeID5
See how I've established foreign keys in both directions? Scanning the
Manager table, you can quickly see which employees are managed by whom, but you'll have to look up each
EmployeeID in the
Employee table to see their information. Scanning the
Employee table, you can quickly see information about a particular employee, including the ID of their manager, but you'll have to look up the manager's
EmployeeID in the
Manager table, and then look that up in the
Employee table if you want the name of their manager.
This could be drastically simplified by combining the two tables into a single table and avoiding a separate
ManagerID by using
EmployeeID for both purposes. Something like:
One Employee table:
... EmployeeID3:Data:Name -> Tom EmployeeID3:Data:Position -> SalesManager EmployeeID3:IsManagedBy:EmployeeID2 -> Since 2001 EmployeeID3:Manages:EmployeeID4 -> Since 2003 EmployeeID3:Manages:EmployeeID5 -> Since 2001 ...
However, depending on your application, you may find that in a NoSQL database, you are better off flattening and duplicating your data, rather than normalizing with relations.
Flattened Employee table:
... Tom:Data:Position -> SalesManager Tom:IsManagedBy:Barbara -> Since 2001 Tom:Manages:Rick -> Since 2003 Tom:Manages:Sally -> Since 2001 ...