Re: Generic Modeling

jraustin1_at_hotmail.com (James) wrote in message news:<a6e74506.0201041230.78f9402e_at_posting.google.com>...
> > Again, nobody here has shown definitively why generic modeling of data
> > in an RDB isn't good enough.
>
> I have used this technique on some projects and these were some of the
> affects:
> 1. Could only use the simpler SQL.
> 2. Data manipulation via VB + simple SQL + lots of
> iterations/recursions.

I agree, if your application is accessing the data directly then the generic modeling is more difficult if you don't provide some kind of abstraction layer above the data. But for querying I disagree that it requires a lot of iterations or recursions; it is just a matter of how complex of a SQL statement that you want to use. As for INSERT and UPDATE, I agree that you need some procedural code for most databases (when all attributes are in different rows) although I'm hoping to mitigate that with Oracle's MERGE and multitable INSERT statements if possible (sorry this is turning into an Oracle9i commercial but that is what I know).

> 3. Execution time (ie generate reports) was considerable and
> impractical in our case.

> Also I found the need to have a variant field which was implemented
> using text which further slowed things down. Have you seen the need
> for a variant field in your generic modelling?

Yes, this is my problem because there is no such thing as a polymorphic column in SQL. The simplest implementation is use a VARCHAR column to store every value, but that breaks down because VARCHAR columns are usually limited in size (Oracle's limit is 4000 characters) and they can't store binary information. I am considering using three mutually-exclusive columns of types {DECIMAL, VARCHAR, BLOB} or types {DECIMAL, CLOB, BLOB} but I'll have to do a lot of measurements. In PostgreSQL VARCHAR columns can be as big as 1GB and they have a bit(n) column type that might be helpful for this type of thing.

I had thought about creating a custom storage system but I didn't want to deal with things like concurrency, portability, and security.

• Brian