Re: variable column table

"Billy Verreynne" <vslabs_at_onwe.co.za> schrieb im Newsbeitrag news:bb6vgs$foj$1_at_ctb-nnrp2.saix.net...
> Paul Brewer wrote:
>
> > No magic really, I'm afraid. Sad to report that in my (admittedly
limited)
> > experience, one of three things always happens in the end:
> >
> > a) Queries end up with hard coded values - which defeats the object.
> > b) It's all run procedurally - and performance suffers accordingly.
> > c) We have technically correct SQL, but it never finishes.
>
> Thanks Paul. But I'm not that pessimistic. Yet. :-)
>
> Will likely start working on the logical and physical db designs soon and
> will see what we can do to lick this problem.
>
> The crux is - if the name-value pairs are hard coded as table columns,
this
> will have a big impact on the planning tools envisaged. It is much easier
> for a planning tool to do its thing on table containing
> (entity_id,name,value) than to go and select on user_tables to see what
> tables there are and then figure out how to deal with the columns that
> differ between tables entitity_1 and entity_2...
>

Hi Billy,

sorry if I may be overdoing this,
but please keep in mind, that
"easyness" of design as a l`art pour l`art leads to a lot of *hardnesses* in the app development basing on your data model !
Once I did such a thing two years ago, a fairly small project for an inhouse inventory to register anything one could think of, and the data model design looked "elegantly flexible", like a chemical formula (a benzol ring of 6 tables), and the app developement was *awfully* difficult. We have made it, but
we were not satisfied with the results,
and we were finally happy to be off of it in the end ...

To my experience, there is a kind of "local optimum" at the number of tables to design,
to make apps maintainable.
Too few (maybe only 1) table with columns like "field_name" and maybe "data_type"
are very hard to develope on,
and on the other extreme, some 1000 of tables with no FKs like in some very famous ERPs as just yet another nightmare ... far too much to be reasonable.
It is much easier to redesign Your data model that to recode the entire app.

Seeems to be some clashes of professions that tend to make things hard: design, development, marketing ... ;-)

please let us know about your success anyway, good luck !

Jan Received on Fri May 30 2003 - 08:25:58 CDT