Overview of Data Structure

Internally Recon.work stores user data in a Document Database format. Document databases are known for their flexibility which is partially due to the missing schema and the capability of accommodating a wide variety of user data regardless of the source database engine or the current data structure being used. The following guide presents a case of translating a simple relational database to the corresponding document database structure.

Starting with Relational Databases

Relational databases are fragmented by design and require you to segment "related" units of data into separate tables, loosely tied together with primary keys. Displayed below are partial views of the three tables that represent a relational database that we would like to convert to a document database format.

Products Table:
1YetiSB5.5 Carbon XTR
2BMCTrailfox 02

Price Table:
Prod IDSale PriceMSRP

Inventory Table:
Prod IDIn StockQuantitySKUFeatures
1true3YTI005Crear suspension, mountain bike
2true110059000carbon fiber, mountain bike

Document Database Basics

Every company/organization that is hosted on Recon.work is assigned its own individual database, with each database owning its own set of document collections. A "table" in a relational database would be equivalent to a "document collection" in a document database, yet a single document collection can accommodate data that was previously stored in multiple tables. This is due to the flexible structure of the documents that can be placed into data collections.

Document DB Flat File Representation

Continuing with our example, lets reorganize the data so that each document contains all the relevant detail for a specific inventory item. Product manufacturer and product name fields can be grouped together under a "Prod" object. We will represent this relationship between an object (Prod) and its properties (mfg and name) by combining the two field names into a single string separated by a period. For example "Prod.name" will now contain all the values that were previously stored in the "Name" field of the "Products" table.

Another formatting convention is needed to represent array elements. In our original table bike features were listed as text keys separated with a comma. In our document database we will instead create an array "Tag", and store these features as individual elements of that array. Each array element gets a numeric index, so that "Tag.0" is the first element of the array. After performing all of these data transformations we end up with the following flat file that is suitable to be imported into the system:

Prod.mfgProd.namePrice.SalePrice.MSRPIn stockQuantitySKUTag.0Tag.1
YetiSB5.5 Carbon XTR8399.2010499.00true3YTI005Crear suspensionmountain bike
BMCTrailfox 025999.005999.00true110059000carbon fibermountain bike

The table above can be saved to the CSV (comma separated values) file or TSV (tab separated values) file and imported to any document collection. Please note that any commas in your column names and field values should be encoded as "%2C" when using CSV format. TSV files do not have this requirement. Have a look at the following sample documents for your reference: CSV file, TSV file. The relevant process for executing data import task is located under System Functions -> Data Administration -> Import Data".

Importing JSON Files

Javascript Object Notation format is a great alternative to the CSV/TSV flat files because it can easily capture the full hierarchy tree that is typically seen in document databases. When preparing your data for import, please remove any underscore (_) characters from your key names. Also ensure that every row in you JSON file can be parsed into a valid object by Javascript "JSON.parse()" function. A sample JSON file that is properly formatted for import is available here. JSON file import can be easily accomplished using the same "Import Data" process available from the user menu.

Back to Main