2

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 :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LucID
  • 151
  • 1
  • 9
  • In what languages do you have these objects? – bmargulies May 16 '10 at 22:47
  • What database are you using ? Must you use that database ? I ask because you are really using the wrong tool for the job, you can get it to work but it would be suboptimal. This is where the NO-SQL solutions come in handy. – Romain Hippeau May 16 '10 at 23:15

3 Answers3

2

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.

bmargulies
  • 97,814
  • 39
  • 186
  • 310
  • This was an old question but that was my intuition and I'm glad you confirmed. Serialization seems the most logical. Stringify. – gcr Apr 09 '21 at 03:51
0

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.

thomasrutter
  • 114,488
  • 30
  • 148
  • 167
0

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.

zzandy
  • 2,263
  • 1
  • 23
  • 43