What's the best way to store custom objects in relational database?

Question

I have my objects with their properties. Objects could change their structure: properties may be added/removed/changed. Objects could be absolutely dropped. So object's metadata (description, classes, call them like you want :) )could be changed.

The database should store objects schemas and instances of these objects.

What's the best way to organise a relational database structure to store data mentioned above?

Currently I see only two ways:

1. Store objects schemas in a few tables: schema general data,schema properties, possible properties types. Store instances in their tables: instance general data, a few tables - per each type from possible properties types table to store instance properties data. And so on.
2. store objects schemas like in p1 but store instances like XML files in one table: one table for general instance info and one table with instance XML.

please, don't ask why/for what I need this. Just need to store custom objects and DB should work fast :)

Answer 1

Serialize. In Java, literally, Serialize. In Python, pickle. In other languages, use whatever they got. Store the results in a blobby column. Go out for a beer.

Answer 2

I'd store the schema as a blob of XML and the data as another blob of XML. You can easily serialize/unserialize this and there are standards for XML schemas already.

If you need to index some part of the data for fast retrieval, then I'd pull that information out into additional columns on which you can index. But certainly keep the XML blobs.

Answer 3

I will use .NET (C# particularly). And I must use relational database for a project.

If I have millions of instances how this will slow down the database if I choose to use BLOBs (p2 from my list above) ? Will storing BLOBs be better from the performance point of view?

Answer 4

With relational DB there are several options to go.

1. Serialization into BLOBs - not good, as you can't search the objects.
2. Have a table for metadata and table with columns like Property1 .. PropertyN - quite fast but not quite flexible.
3. If you don't have to change schema often you can go with real tables for objects (e.g. really doing CREATE TABLE, ALTER TABLE etc.).

Apart from relational DBs you can try NoSQL db, e.g. mongodb. I've just ran some tests and a collection with 5M objects works reasonably fast. Obviousely NoSQL solution dosen't fit into your relational requirement, but if you have some time just give it a try.