I have a web application running over a MySQL database (in development). I'm considering to migrate my application to Google App Engine, and would like to better understand how my simple relational database model can be transformed to the non-relational approach.
I'm a long time relational database person, and I have no experience with column based DBs such as BigTable. Just in case Google also supports small deployments of relational databases, I would like to state that my question is general and not specific to Google - I would like to understand how simple relational models can be represented in non-relational DBs.
My database (simplified) is as follows:
Items Table
------------
ItemID ItemName ItemPriority
1 "Car" 7
2 "Table" 2
3 "Desk" 7
ItemProperties Table
---------------------
ItemID Property Importance
1 "Blue" 1
1 "Four Wheels" 2
1 "Sedan" 0
2 "Rectangular" 1
2 "One Leg" 1
I have many items, each with a name and ID. Each item has multiple properties, each property has several parameters (I only stated the name and "importance" of each property, but there are more). I have tens of millions of items, each has hundreds of properties.
The usage scenario: I receive an ItemName as input, look up its ID in the items table, and fetch all the properties by that id. I then perform some analysis on the list of properties (in memory), and return a result.
90% of the work is lookup based on a parameter, which (if I understand correctly) is the pain-point of non-relational DBs.
What is the recommended approach?
From someone who has been working with Non-relational db's for a while your two tables should be really easy to translate to a non-relational db.
Take the two tables and turn them into a single object.
Item: - Id - Name - Properties - prop1 - prop2
Store the whole thing in your data-store columns(Big-Table),document(CouchDB),or whatever else it uses.
You can look up items by any of the ids, names, or properties. There are no joins which are one of the bigger pain points of non-relational dbs. Parameter lookups aren't really a pain point unless I'm not understanding what you mean by that. You may have to do multiple lookups but most times that is not an issue and it scales way better than an rdbms does.
In your example I actually consider the non-relational model to be simpler and easier to implement and understand.
Each non-relational data store has different conventions and constraints though so it's hard give guidance in the general sense. CouchDB can create an index on any part of the object with it's views for instance. With BigTable you may have to store multiple copies of the denormalized data to get fast indexed lookups. Others will have different things to consider when you decide how to store the data. There is quite a lot of differentiation out there once you leave the world of SQL.