Re: create table in a procedure

"Hans Forbrich" <forbrich_at_yahoo.net> wrote in message news:ahFdc.27891$J56.12879_at_edtnps89...
> /motten wrote:
>
>
> > display data for an arbitirary (spelling?) number of surverys each
>
> (in this group, anyone who watches for spelling is itching for a heart
> attack!)
>
> >
> > My idea is to do a "master" table containing the question "types"
 meaning
> > the
> > text / label for the question plus a unique identifier. Then construct
 the
> > actual survey
> > instances several rows per survery in another table containing
 references
> > to the "master type"
> > and the actual values...
> > My colleague on the other hand wants to contruct new tables for each and
> > every
> > survey which would then contain only the appropriate fields for that
> > survery...
>
> (Based on my experience) No, no, no, no, no! For a production environment
> you want things reasonably stable, repeatable and measurable. Your
> colleague is asking for instability through and through. The dynamic DDL
> will eventually make an unexpected decision, usually due to the untested
> patch at 3AM as a result of some crisis.
>
> Worst of all, it will become unmaintainable within a short time as the
> proliferation of tables will become overwhelming to any DBA activity.
>
> I've tried a dynamic tables project like this and soon regretted it.
>
> IMO, if you really, really need differentiation, map views dynamically on
> top of the consistent and manageable table. YMMV!
>
> >
> > He claims his approach makes things simpler while I'm in the opinion,
 that
> > the
> > dynamic sql required makes things harder...
>
> Some additional reasons to go with a single table:
>
> - global statistics (never say never ...)
> - ease of enhancement - add something to one, not 287,352 tables
> - consistent reports
>
> /Hans

English isn't my primary language so I need to make sure i understand what you're saying.
Are you saying my approach is reasonable or are you saying that both mine and my collgegaues
designs are defect ?

The design / model I'm proposing is extremely simplified in the above description - but it never
ever involves creating new tables nor includes the need for creating views for each survery...

Cheers

Morten Olsson Received on Sat Apr 10 2004 - 01:06:24 CEST