Several years ago, back when I was a budding junior web developer back at MindComet--better known as the evil empire--I was part of a fantastic (if under-appreciated and under-paid) development team that included at the time folks like Jerry Jackson and Jim Defilipo, two guys I have emmense respect for and keep in contact to this day.

Jim and I were heading up the development initative for a new CRM system for one of our customers (which became the basis of the background in the area that lead me to co-found Evware, but I digress).  We sought out to create a simple, yet elegant database structure that was infinitely extensible, completely indexed, an elegant relationship system where anything could be related to anything, and would never require any additional tables or fields to be created. 

The database was simply designed to be able to describe anything.  We theorized how it could be used to describe any business structure, brain patterns, space, or the atomic structure of our desks.  It was created with just a few database tables, roughly as memory serves me:  items, item_types, item_values, and item_relationships.  The item_type table would describe the class of the item and its general behavior.  The items table would contain a reference to each item of all types with a column for a globally unique id (guid), its item type, and its name.  The item_values table would contain all of the properties of each item in the items table so would have a column referring to the guid of the item it described, its field name, and its value.  The item_relationships table would contain two columns with guids (the two items with a relationship) and possibly another column describing the relationship.  It was a really ambitious project that took way more time to develop than the company was willing to allow us to invest--not having the forsight to see future projects that could have all been based on this one system.  And unfortunately it was in the middle of this project that the evil emporer got rid of me... I believe if I had stuck around we would have been able to see it through as at least half of the driving force and knowledge of the complete vision and its presentation technologies (xpath, xsl, which no one else knew) was in my skillset.  But I degress again.

Anyway... it was an incredible project and one that I still believe in.

Amazon it seems, while not fully trying to do exactly this, has taken on sort of a subset of this kind of theory in their new SimpleDB product that I first tried out this week--and love.  Internally, it seems to contain two tables, the equivalent of our item and item_value tables.  The cool thing is that you can insert anything in it, assign its "row" (item) a unique name (guid).  Then you can add any number of properties to it.  It allows you to break out of the strict row and column throught-process because each row can theoretically have its own unique fields/columns.  Each and every value is indexed for fast queries.  And a single field/column can have multiple values.  So normally you'd have to create all kinds of join tables to do that kind of thing... here just insert multiple values into the one field.  Internally they are just all entries into the item_values table, so that's how they work their magic.

They also wrap it all up so you can query it with (basic) SQL-like queries.  Now internally they are translating these queries because they don't really have the normal table structure like you are querying.

The downside of the system is that it does not allow multiple "item types".  Each item type is what they call a "domain" and it basically exists in its own little world.  So this means no relationships and no cross-table joins or anything.  So it kind of limits what it can be used for--but I guess that's why it's called SimpleDB.  But for certain applications it is certainly just what the doctor ordered!