Modelling and Design - what is the best table schema for my data

[dario-vega]

Schema flexibility in Oracle NoSQL database.

📲 Modern application developers have many choices when faced with deciding when and how to persist a piece of data. Unlike the traditional relational model with purely fixed schemas, NoSQL Database is essentially about schema flexibility – the ability to change how data is organized and stored easily. 👨‍💻 👩‍💻

what is the best table schema for my data?

-- Order JSON document
{
    "id":100,
    "orderedby":"X",
    "address" : {
        "addressee":"123 rue bellevue"
        , "city":"Paris"
        , "country":"France"
        , "badfield":"X"},
    "lineitems":[
        {"itemname":"Rissoto","itemprice":16.5, "quantity":10},
        {"itemname":"Water","itemprice":5.5,"quantity":10},
        {"itemname":"Tiramisu","itemprice":11.5, "quantity":10 }
    ],
    "comments":["Expected at 13h", "leave the meals at the reception desk"]
 }

A brief aside here - People generally think about all the features and data types they're already familiar with from their past experience using other products - so, What is a table schema or simply a table?
✔ In Oracle NoSQL Database, A table is an unordered collection of record items, all of which have the same record type. We call this record type the table schema.
✔ Oracle NoSQL database supports the following non-scalar data types: JSON, Records, Array, and Maps.
✔ Oracle NoSQL database support also Parent-Child tables.
✔ Seamless multi-model - the interoperability between schema-less and fixed schema data models provides even more flexibility. Use the same query/API whatever the schema is chosen.

More in https://docs.oracle.com/en/database/other-databases/nosql-database/23.1/sqlreferencefornosql/data-model.html

So let see what are the possibilities

1. Using Atomic Datatypes, identities and non-scalar data types

CREATE TABLE IF NOT EXISTS order(
    id LONG GENERATED ALWAYS AS IDENTITY, 
    orderedby STRING, 
    address RECORD(addressee STRING, city STRING, country STRING
        , state STRING, street STRING, zipcode STRING),
    lineitems ARRAY(RECORD(itemname STRING, itemprice DOUBLE, 
        linenumber INTEGER, quantity INTEGER)), 
    comments ARRAY(STRING), 
    PRIMARY KEY(id)
)

2. Using Atomic Datatypes, identities and JSON type instead of ARRAY, RECORDS

CREATE TABLE IF NOT EXISTS order (
    id LONG GENERATED BY DEFAULT AS IDENTITY , 
    orderedby STRING, 
    address JSON,
    lineitems JSON, 
    comments JSON, 
    PRIMARY KEY(id)
)

3. Using Only JSON type and a UUI as a key

CREATE TABLE IF NOT EXISTS order (
    id STRING  AS UUID GENERATED BY DEFAULT, 
    orderedby JSON, 
    address JSON,
    lineitems JSON, 
    comments JSON, 
    PRIMARY KEY(id)
)

Note: The JSON type represents all valid JSON values. Specifically, an instance of JSON can be

• an instance of ANYJSONATOMIC,
• or an array whose elements are all instances of JSON,
• or a map whose field values are all instances of JSON.
  Any instance of a numeric type, the string type, and the boolean type is an instance of the ANYJSONATOMIC type as well. The json null value is also an instance of ANYJSONATOMIC.

4. Using Only one big JSON field and a UUI as a key - JSON collection Style

CREATE TABLE IF NOT EXISTS order (
    id STRING  AS UUID GENERATED BY DEFAULT, 
    jsonDoc JSON
    PRIMARY KEY(id)
)

We can continue to explore also the usage of Child Tables and we have some other features to come.

Finally, to start this discussion, let me show the code to insert data into my table order

const result = await client.put("order", myJSONdoc, {exactMatch:false} );
res.json({ result: result});

I will provide more information in a next post in this discussion. Meanwhile, learn about Trade-offs while using Flexible Schema - Table Modelling and Design https://docs.oracle.com/en/database/other-databases/nosql-database/23.1/nsdev/table-modelling-and-design1.html

[dario-vega]

🆕 Oracle NoSQL Database 23.3 introduces a new type of table called JSON Collection tables. These are particularly useful for applications that store and retrieve their data purely as documents. JSON Collection tables simplify document management and manipulation by eliminating the need to declare fields during table creation.

You create a JSON collection table with one or more primary key fields. When you insert data into the table, a single document is created without explicitly declaring columns. The document can contain any number of JSON fields. The primary key fields can include IDENTITY columns and UUIDs.

CREATE TABLE IF NOT EXISTS order (
    id STRING  AS UUID GENERATED BY DEFAULT, 
    PRIMARY KEY(id)
)