Where Sometimes, we use Ruby on Rails to produce both after sales and frontend programs. Usually, these programs interact with similar MySQL database. It really works ideal for most our data, but we now have one situation which I must proceed to a NoSQL atmosphere.

We now have clients, and our clients have what we should call "inventories"--a number of of these. A listing might have many 1000's of products. This really is presently carried out by two relational database tables, inventories and inventory_items.

The issues start when two different inventories have different parameters:

# Inventory item from inventory 1, televisions 
{
  inventory_id: 1
  sku: 12345
  name: Samsung LCD 40 inches
  model: 582903-4
  brand: Samsung
  screen_size: 40
  type: LCD
  price: 999.95
}

# Inventory item from inventory 2, accomodation
{
  inventory_id: 2
  sku: 48cab23fa
  name: New York Hilton
  accomodation_type: hotel
  star_rating: 5
  price_per_night: 395
}

Since we clearly can't use brand or star_rating because the column title in inventory_items, our solution to date is to use generic column names for example text_a, text_b, float_a, int_a, etc, and introduce another table, inventory_schemas. The tables now seem like this:

# Inventory schema for inventory 1, televisions 
{
  inventory_id: 1
  int_a: sku
  text_a: name
  text_b: model
  text_c: brand
  int_b: screen_size
  text_d: type
  float_a: price
}

# Inventory item from inventory 1, televisions 
{
  inventory_id: 1
  int_a: 12345
  text_a: Samsung LCD 40 inches
  text_b: 582903-4
  text_c: Samsung
  int_a: 40
  text_d: LCD
  float_a: 999.95
}

It has labored well... up to and including point. It's cumbersome, it's unintuitive also it lacks scalability. We must devote assets to setup inventory schemas. Using separate tables isn't a choice.

Enter NoSQL. By using it, we're able to let every single item get their own parameters but still store them together. In the research I have done, it certainly appears just like a great alterative with this situation.

Particularly, I have checked out CouchDB and MongoDB. Both look wonderful. However, you will find a couple of other odds and ends we have to have the ability to use our inventory:

  • We have to have the ability to choose products from just one (or several) inventories.
  • We have to have the ability to filter products according to its parameters (eg. get all products from inventory 2 where type is 'hotel').
  • We have to have the ability to group products according to parameters (eg. obtain the cheapest cost from products in inventory 1 where brand is 'Samsung').
  • We have to (potentially) have the ability to retrieve 1000's of products at any given time.
  • We have to have the ability to access the information from multiple programs both after sales (to process data) and frontend (to show data).
  • Rapid bulk insertion is preferred, though not needed.

In line with the structure, and also the needs, are generally CouchDB or MongoDB appropriate for all of us? If that's the case, which would be the best fit?

Thank you for reading through, and thanks ahead of time for solutions.

EDIT: A primary reason I love CouchDB is it could be feasible for us within the frontend application to request data via JavaScript from the server after page load, and display the outcomes without needing to use any after sales code whatsoever. This could result in better page load and fewer server strain, because the fetching/processing from the data could be done client-side.