Re: Should an application ever be allowed to change a schema?

Karen Sundquist wrote:

> Imagine a database that is only accessed by a server application.

(Sorry for being slightly off-topic.
I am trying to initiate a c.d.theory glossary.)

This effectively reduces the meaning of the word 'database' to 'deluxe filesystem'. No problem, many use the word in that sense. The implication it is that part of the server application may be busy taking care of contraints which would have to be guarded by the dbms if there would be other applications. Typically, developers *will* implement some of the more complicated contraints in the server application. This severely limits the chance of the data ever being shared. 'Import' and 'export' facilities become crucial to the success of the application.

> This
> application provides the ability for admin users to create templates
> for forms. When a template is created, other users of the application
> can then submit instances of a form, which is then stored in the
> database.
>
> The form templates are used by the application to generate GUI front
> ends to enter data. The form data collected is transmitted to external
> systems for processing.
>
> This must be a common problem so how would any of you model this in a
> relational database?
>
> There are two approaches that come to my mind:
> Solution 1. Header table and Value Pairs

[snip casting solution]

To me it looks as if you are trying to implement a generic, table driven data-capture gui in front of another, more specific datacollection, so the casting doesn't really surprise me.

> Solution 2. Header table and Dynamic Table Creation

[snip dynamic schema solution]

> In this solution we could end up with many tables, yet we are using
> the storage facilities of the RDBMS better. Also we are allowing the
> schema to dynamically change.
>
> From a practical and theoretical point of view, which of these
> approaches is better?

Practical: I've seen both. The dynamic schema approach seems to fit big workflow applications. I did not see recent examples of the casting approach for big applications, so maybe it doesn't scale as well. The dynamic schema approach requires thorough knowledge of the specific dbms.

Theoretical: I prefer programming languages for programming instead of tables.

> ... Is there another way I haven't thought of?

I don't know.

Just my 2 Eurocents. Received on Sat Apr 17 2004 - 14:33:17 CEST