IndexedDB - Correct way to store big amount of data

Question

I have to realize a web application that use LocalDB to manage offline order creation.

In this moment I'm implementing the LocalDB using Dexie for Angular2/Typescript.

What I have to do is realize a database in order to be able to manage:

• Customers ( +1000 records )
• Products ( +2000 records )
• Products
• Price defined per Customers + ( 100.000 records )
• Orders ( few data )

etc...

It has to manage lof of data, because Products, Customers, and Prices are a lot or records...

My question is:

If I have to realized this db using a common relational database like SQL SERVER or MySql, when I compose the temporary order I store the idMaterial and the quantities...

But what I have to do in IndexedDB ?

Its better to store temporary order like for mySql/SqlServer using idMaterial/Qty and retrive the information of the product ( when needed ) by doing INNER JOIN or its better to store in a local table all the information of Products/Prices in order to avoid INNER JOIN queries ?

Can I run INNER JOIN between tables that joins over 2000+ Products and 100.000 Products Price defined per customer ?

Thanks to support!

Answer 1

IndexedDB is architectured a lot like an SQL database, in that it has tables, rows and transactions, even though they are named differently (Table = ObjectStore, row = Object).

Using Dexie, it is quite easy to do those kind of typical joins with foreign keys. IndexedDB doesn't check the constraints of the foreign keys, but you can do queries that are similar to SQL joins.

There is an addon to dexie, dexie-relationships, that can assist in doing the join queries.

import Dexie from 'dexie'
import relationships from 'dexie-relationships'

class OrdersDB extends Dexie {
  customers: Dexie.Table<Customer, string>;
  products: Dexie.Table<Producs, string>;
  pricesPerCustomer: Dexie.Table<PricePerCustomer, string>;
  orders: Dexie.Table<Order, string>;

  constructor() {
    super ("OrdersDB", {addons: [relationships]});
    this.version(1).stores({
      customers: 'id, name',
      products: 'id, name',
      pricesPerCustomer: `
        id,
        customerId -> customers.id,
        productId -> products.id,
        [customerId+productId]`, // Optimizes compound query (see below)
      orders: `
        id,
        customerId -> customers.id,
        productId -> products.id`
    });
  }
}

interface Customer {
  id: string;
  name: string;
  orders?: Order[]; // db.customers.with({orders: 'orders'})
  prices?: PricesPerCustomer[]; // with({prices: 'pricesPerCustomer'})
}

interface Product {
  id: string;
  name: string;
  prices?: PricesPerCustomer[]; // with({prices: 'pricesPerCustomer'})
}

interface PricePerCustomer {
  id: string;
  price: number;
  currency: string;
  customerId: string;
  customer?: Customer; // with({customer: 'customerId'})
  productId: string;
  product?: Product; // with({product: 'productId'})
}  

interface Order {
  id: string;
  customerId: string;
  customer?: Customer; // with({customer: 'customerId'})
  productId: string;
  product?: Product; // with({product: 'productId'})
  quantity: number;
  price?: number; // When returned from getOrders() below.
  currency?: string; // --"--
}


const db = new OrdersDB();

/* Returns array of Customer with the "orders" and "prices" arrays attached.
*/
async function getCustomersBeginningWithA() {
  return await db.customers.where('name').startsWithIgnoreCase('a')
    .with({orders: 'orders', prices: 'pricesPerCustomer'});
}

/* Returns the price for a certain customer and product using
   a compound query (Must use Dexie 2.0 for this). The query is
   optimized if having a compound index ['customerId+productId']
   declared in the database schema (as done above).
*/
async function getPrice (customerId: string, productId: string) {
  return await db.pricesPerCustomer.get({
    customerId: customerId,
    productId: productId
  });
}

async function getOrders (customerId: string) {
  // Load orders for given customer with product property set.
  const orders = await db.orders.where({customerId: customerId})
    .with({product: 'productId'});

  // Load prices for this each customer/product
  const prices = await Promise.all(orders.map(order =>
    getPrice(customerId, order.id)));

  // Return orders with price and currency properties set:
  return orders.map((order, idx) => {
    const pricePerCustomer = prices[idx];
    return {
      ...order,
      price: pricePerCustomer.price,
      currency: pricePerCustomer.currency
    };
  });
}

Note that I have declared each primary key as a string, so you will have to invent each key manually. Could have been using auto-generated numbers as well (using "++id, ..." instead of "id, ...") in the schema declaration. If so, declare the tables as Dexie.Table<Customer, number> instead of Dexie.Table<Customer, string>.