Have Geek Brain

Object metadata storage architecture

We are building a document storage solution, and for each document, we need to store a lot of additional metadata with it to comply with local regulations, ranging from basic data such as title or description to dates of relevant events or placement and classification rules.

I have seen various types of solutions, but no one convinces me:

  • Tables that grow in columns when a new metadata slot is added (so they have as many columns as document-related metadata)
  • Tables with many spare common columns. Very similar to 1.but tables don't grow (less permissions)
  • A table of document IDs, metadata keys, and metadata values.
  • The table with metadata definitions and metadata keys in 3. is replaced with metadata identifiers. We have used this solution in the past. There are millions of rows at the end of the table.
  • A text field in a document table or related table that stores XML or other structured information with all metadata in key-value pairs.

I am tied to number 5 by providing a parallel full-text index (Lucene.Net? Other?) For searching the relevant metadata (not everything needs to be "searchable").

Any suggestion? A similar experience?

+1


a source to share


3 answers


Table 1: Document Information (PK is the Document ID)

Table 2: Metadata Definitions (PK - Metadata Definition Identifier)

Table 3: Document ID, Metadata Identification ID, Metadata Value

The biggest disadvantage of this is that you must have one type (presumably), or you must have n columns (where n is the number of data types you want to store) and use a column in the metadata definitions table to determine if which column in table 3 to output value from.



My opinion on the listed 5 solutions:

  • Growing tables are painful and can cause row problems (especially if you want / need a non-nullable metadata value).
  • I hate "spare common columns" with a passion (although they are popular).
  • Close, but this limits the flexibility of your metadata even more than my solution. If your metadata keys and values ​​are fairly simple, this might work.
  • I'm not really sure what you mean by this - is it the same as what I suggest, or something else?
  • I don't like storing structured XML in an RDBMS - you lose most of the RDBMS authority by doing this IMHO.

What my thoughts are - I have never designed such a system, but I have dealt with commercial systems that used several of these schemes.

+1


a source


Why not use CouchDB ? It is specially designed to meet this requirement.



If that's not an option, consider using Lua or JSon (for your option # 5) as the metadata descriptor.

+1


a source


Perhaps you can have a look at JCR (Java Content Repository). JCR is a standard for a content repository that addresses general content management requirements such as version control, full text search, and editing. Plus, it provides an abstraction layer in the content store, which means you can use one API to host content on any storage system like database, xml file, etc. You can of course add metadata to your document by adding some properties to the document node with the JCR API. You don't need to worry about how the document and metadata will be stored. JCR will take care of this. Jackrabbit is the JCR reference implementation. Try it.

+1


a source






More articles:


All Articles