Key value pair database in .Net

Question

I have a database design problem , where the database object's properties are not fixed. And the application user need to have option to add propery to the object at any time.

Since this can't be implemented in traditional database design with fixed number of columns, i am thinking about using key value pair design pattern And i am looking for ways to implement it in c#(.Net).

I prefer to use Mysql as backend database.

Q1. What is the best way to serialize object and store in mysql database ? ( XML or JSON or Binary ? )

Q2. Is there any key value pair database engine, with robust .Net Bindings ?

Answer 1

As far as I know, MySQL has support for XML fields. I'm not sure if it can index them though.

Just serialize your object to xml with XmlSerializer and then have a look at the MySQL xml functions.

Answer 2

If you dont need to query/report on these properties, I would suggest saving as XML, serialising your objects with some kind of loose coupling that wont error if properties are added or removed. JSON can be handled by your application when serving out the content.

If you do require querying/reporting with other database fields, I would suggest coming up with some kind of database schema that can store the data.

Answer 3

Serialization will add a performance overhead to your application, but if you go for that then I'd use binary serialization as it's the quickest. Take a look at protobuf-net for serialization.

Answer 4

In answer to Q2:

Apache CouchDB stores JSON.

Here is a getting started guide for C#: http://wiki.apache.org/couchdb/Getting%5Fstarted%5Fwith%5FC%23

The database has a RESTful interface, which makes it easy to use from any language.

Answer 5

Are you sure you need relational database? For my needs , the db4objects is enough