PostgreSQL has a module named hstore that implement the use of key/value pairs (it's something like the OpenStreetMap model, but in a single table).
What are the Pros and Cons of hstore regarding GIS compatibilities, data adding and retrieving?
We actually demonstrate this in our book (which supposedly will be going to print in the next week or so). In chapter 3. You can download the code from here: http://www.postgis.us/chapter_03
(and of course if you buy our book you get our thorough explanations of the pros and cons)
It uses the osm2pgsql hstore switch to import osm tags as hstore column.
What we do to compensate for the fact that Quantum and other things don't support hstore directly is to create a view around the key tags we need. For applications where you have complete control using a query that aliases the key->value as a column works fine too even if you database driver doesn't understand hstore type.
I have no experience with it myself, but if you are interested in storing spatial data in such a database, you might want to search for spatial nosql. On post I found was eg: http://stackoverflow.com/questions/2041622/nosql-and-spatial-data