Re: Data base tables design questions for: user saved forms, user parameters

Bruno Lavoie wrote:

> We have a relatively huge number of search forms, each one with specific
> fields. So I don't want, if possible, to use a distinct table for each
> form.

This is one of the few areas where storing XML in a relational database
seems to make sense to me. Forms are well suited to description and
validation by XML documents; in fact, with standards like XFORMS there's
even a degree of agreement on ways to model and present them.

There isn't any particularly strong reason to store the data in a
relational DB if you go for XML storage - but if your app already uses
PostgreSQL, which it presumably does, then it's much more convenient to
be able to use the same access, authentication and storage methods for
your form data as everything else.

> The hesitation here is : how to store the fields & values pairs, in
> FIELDS_VALUES?

> (1) XML field?

I'd certainly be tempted. I'd want to make sure I had good DTDs for my
forms, though, and had some sort of form versioning in place to handle
detection and conversion or invalidation of old saved form data.

Pg's XML support is still pretty rudimentary, but it's handy enough that
you can do useful queries on your stored XML data.

I'd be highly likely to implement this using (3) if I was tackling the
problem.

> (2) our custom text structure and formating representing something key =>
> value

This gets ugly because of the need to store a variety of data types for
values. One workaround is to store the values as their textual
representations rather than as the "real" type. That makes any sort of
checking and validation even harder than it already is, though, and may
also slow things down.

> (3) our custom serialized Java object into a field

I certainly wouldn't want to use any sort of binary object
serialization; I'd say that's a recipe for pain and eventual disaster.

Serialization to/from XML isn't too bad an idea, though, as Java
provides mechanisms for object versioning, etc, and in general makes
object<->XML (de)serialization surprisingly reasonable. I'd still want a
DTD or some other XML schema definition to permit checking of the data
in-situ.

Personally I think that if you want to store data for may different
types of form in one table, and want even rudimentary checking and
validation, then XML serialization of Java objects is probably the way
to go.

> * what happens if the form evolve over time? the final solution must not
> crash or cause some kind of inconsistencies.

Proper and careful use of Java's XML serialization should take care of
that. It does take extra effort, planning, and thought, but you're
signing up for that one way or another by tackling this problem at all.

> What is the perfect solution?

Much like storing generic "objects" in an RDBMS, the problem isn't a
very clean match to the relational model. As such, I doubt there is a
perfect solution.

I suspect that the most strictly clean approach would be to properly
model each form as a table or (more likely) set of related tables. It
doesn't sound like that's practical for your particular app, though.

XML serialized objects might be the most reasonable compromise. I'll be
very interested in the other responses to this, though, and in the
resources/articles people reference.

> like many modellers, it's easy to fall into the generic models easy to
> maintain but harder to optimize and to ensure consistency?

There's a real risk of falling for "Enterprise-ey" design with
system-builder-builders and such. This site:

http://thedailywtf.com/

is full of examples of such, including some pretty horrifying
database-related ones. For example:

http://thedailywtf.com/Articles/The_Enterprise_Rules_Engine.aspx

http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx

--
Craig Ringer