Re: create table in a procedure

/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 Received on Sat Apr 10 2004 - 00:16:38 CEST