Application Design Document DB

Question

I must develop an application that uses a un DB, and that is structured in this mode:

1. there are metadata tables(Document Type, Document Attributes...)
2. starting from Metadata Tables are created/modified (also during the normal application- lifecycle at runtime) metadata tables.

IE: If I create a new Document Type, inserting a new record in the METADATA table named "Document Type" (Contract, Invoice, Note..), the new relative DATA table is created into the DB. This table has as columns the attributes that I have definited in the other METADATA table (Document Attributes).

I'd like to use an ORM in order to map the METADATA Tables because these not change the structure at runtime. Is it possible to map also the DATA Tables? I'd like to work with POJO also for the Data tables.

I think that is not possible to create classes at runtime (with reflection is possible, but is the best choiche?) and modyfy theyr structure.

This is probably the logic/problem used from CMS and CRM.

Have you some suggests Have you any suggestions on how to structure my application, especially for the ORM / DB?

Thank you

Answer 1

JPA and eclipseLink would be my choice maybe.

You are a bit in the following position: a web application developed with the business logic online editable by the organisation (CMS), say with a rules engine. Very generic, sounds nice, reduces development costs. But: changes can now not be tested, cannot be put in version control, are immediately live. No debugging, no compiler intelligence and error messages, no cleanup (like code cleanup). In fact such a system is quite a quality risk and a drain on maintenance.

In your case, you need very strong tools: version control, separate staging server, controllable logic, reporting (automatic documentation of the model and interactions).

JPA can in principle create the database tables itself from the entities (or the other way round). So that is one layer. It maintains a meta model.

Originating from your meta model you indeed would need to do something clever.

Easiest, most achievable, would be to stay just with your meta model and define versioning etcetera. Manual code would the use generic data like Map's. You could store the code in the database too, versioning it, and use the Java Scripting API. But you would need to inform the extend to the management, and be convinced of the feasibility - create a prototype.

I definitely hope someone knows a better solution.

P.S.

Maybe a NoSQL database would be something, it looks a bit like a document DB.

Answer 2

It's difficult to offer good advice with such little information. However, you included the "JCR" tag and it sounds like your use case is similar to a content or document management system, so you should definitely consider JCR. See "When to use JCR over other options?" for a more general response, but I'll try to describe its benefits for your particular use case. (Always choose the best tool for the job, though.)

Perhaps the best feature of JCR for you is the ability to have a flexible schema that allows you to add (or change) the metadata for different documents and over time. You can either restrict the properties to ensure your apps only add accepted information, or you can choose to be a little looser and keep your options open to add additional metadata as needed. You can even break your metadata into "characteristics" that can be added to individual nodes via mixins. In short, JCR gives you a ton of flexibility in designing your data structure, while giving you several knobs to control how much enforcement/flexibility you use.

Using a JCR repository will also let you keep that metadata with the document (if you wanted; you can always separate them). And, this may give your applications great performance if they use access patterns that read a document and then get the metadata. Navigation is often much faster than querying.

Thirdly, many JCR repositories even support querying repository content (by mapping node types into relational table-like structures). JCR 2.0 has several languages, including XPath and a SQL-like language called "JCR-SQL2".

There are some object-mapping libraries for JCR, but they'll very likely hinder really making use of JCR's data structure flexibility. JCR itself is a Java API, and it has built-in support for events, security, queries, locking, versioning, etc.

If you do look at JCR, be sure to check out the different implementations, including Jackrabbit and ModeShape. They each offer something different (e.g., Jackrabbit is the reference implementation, while ModeShape offers some extensions and additional features like expanded query languages and sequencing; see this related question).