Example 1: Designing a Schemaless Table

You have multiple options to store information about browsing patterns in your table. One option is to define a table that uses a cookie ID as a key and keeps audience segmentation data as a single JSON field.

// schema less, data is stored in a JSON field
CREATE TABLE audience_info (
       cookie_id LONG,
       audience_data JSON,
       PRIMARY KEY(cookie_id))

In this case, the audience_info table can hold a JSON object such as:

{
  "cookie_id": "",
  "audience_data": {
    "ipaddr" : "10.0.00.xxx",
    "audience_segment: {
       "sports_lover" : "2018-11-30",
       "book_reader" :  "2018-12-01"
    }
  }
}

Your application will have a key field and a data field for this table. You have flexibility in what you chose to store as information in your audience_data field. Therefore, you can easily change the types of information available.

Example 2: Designing a Fixed Schema Table

You can store information about browsing patterns by creating your table with more explicitly declared fields:

// fixed schema, data is stored in typed fields.
CREATE TABLE audience_info(
       cookie_id LONG,
       ipaddr STRING,
       audience_segment RECORD(sports_lover TIMESTAMP(9),
                               book_reader TIMESTAMP(9)),
       PRIMARY KEY(cookie_id))

In this example, your table has a key field and two data fields. Your data is more compact, and you are able to ensure that all data fields are accurate.

Example 3: Designing a Hybrid Table

You can store information about browsing patterns by using both typed and JSON data fields in your table.

// mixed, data is stored in both typed and JSON fields.
CREATE TABLE audience_info (
       cookie_id LONG,
       ipaddr STRING,
       audience_segment JSON,
       PRIMARY KEY(cookie_id))

Primary Keys

You must designate one or more primary key columns when you create your table. A primary key uniquely identifies every row in the table. For simple CRUD operations, Oracle NoSQL Database Cloud Service uses the primary key to retrieve a specific row to read or modify. For example, consider a table has the following fields:

• productName

• productType

• productLine

From experience, you know that the product name is important as well as unique to each row, so you set the productName as the primary key. Then, you retrieve rows of interest based on the productName. In such a case, use a statement like this, to define the table.

/* Create a new table called users. */
CREATE TABLE if not exists myProducts 
(
  productName STRING,
  productType STRING,
  productLine INTEGER,
  PRIMARY KEY (productName)
)"

Shard Keys

The main purpose of shard keys is to distribute data across the Oracle NoSQL Database Cloud Service cluster for increased efficiency, and to position records that share the shard key locally for easy reference and access. Records that share the shard key are stored in the same physical location and can be accessed atomically and efficiently.

Your Primary and shard key design has implications on scaling and achieving provisioned throughput. For example, when records share shard keys, you can delete multiple table rows in an atomic operation, or retrieve a subset of rows in your table in a single atomic operation. In addition to enabling scalability, well-designed shard keys can improve performance by requiring fewer cycles to put data to, or get data from, a single shard.

For example, suppose that you designate three primary key fields:

PRIMARY KEY (productName, productType, productLine)

Because you know that your application frequently makes queries using the productName and productType columns, specifying those fields as shard keys is appropriate. The shard key designation guarantees that all rows for these two columns are stored on the same shard. If these two fields are not shard keys, the most frequently queried columns could be stored on any shard. Then, locating all rows for both fields requires scanning all data storage, rather than one shard.

Important factors to consider when choosing a shard key

• Cardinality: Low cardinality fields, such as a user home country, group records together on a few shards. In turn, those shards require frequent data rebalancing, increasing the likelihood of hot shard issues. Instead, each shard key should have high cardinality, where the shard key can express an even slice of records in the data set. For example, identity numbers such as customerID, userID, or productID are good candidates for a shard key.

• Atomicity: Only objects that share the shard key can participate in a transaction. If you require ACID transactions that span multiple records, choose a shard key that lets you meet that requirement.

What are the best practices to follow?

• Uniform distribution of shard keys: When shard keys are uniformly distributed, no single shard limits the capacity of the system.

• Query Isolation: Queries should be targeted to a specific shard to maximize efficiency and performance. If queries are not isolated to a single shard, the query is applied to all shards which is less efficient and increases query latency.

See Creating Tables to learn how to assign primary and shard keys using the TableRequest object.