use static tables or create tables at runtime?

19 May 2006 6:14 PM

Mark Duncan

i'm developing an application that currently creates tables in the database
after a user has completed the design for a workflow. having never created
an application like this i keep having second thoughts about whether this is
a good idea or not. i keep debating with myself whether i should force all
data to be fit into static tables created at installation or if the more
flexible creation according to the users design is the best method. has
anyone been down this road before and if so what did you choose and why and
how did it work out?

19 May 2006 6:21 PM

Adam Machanic

I think it's fair to say that any time you can avoid creating tables
dynamically, you should. There are certainly a few edge cases -- just like
anything else -- where it makes sense, but in the vast, vast, vast majority
of cases I've seen, dynamically creating tables is a recipe for disaster.
It's a management and query nightmare, and it rarely brings any benefit
other than the feeling in someone's mind that they're doing something to
make the app more "flexible" or "agile", when in fact the app often ends up
becoming quite brittle due to the need for complex special case code to
support all of the different data formats.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

Show quote

"Mark Duncan" <MarkDun***@discussions.microsoft.com> wrote in message
news:5F58F22F-1D08-4AF8-BE85-1D3677C90BE7@microsoft.com...
> i'm developing an application that currently creates tables in the
> database
> after a user has completed the design for a workflow. having never
> created
> an application like this i keep having second thoughts about whether this
> is
> a good idea or not. i keep debating with myself whether i should force
> all
> data to be fit into static tables created at installation or if the more
> flexible creation according to the users design is the best method. has
> anyone been down this road before and if so what did you choose and why
> and
> how did it work out?

19 May 2006 6:36 PM

Omnibuzz

You can have fixed tables for your data storage. Maybe something similar to
few of the information_schema.views that you have in SQL Server.
Hope this helps.

19 May 2006 6:43 PM

Mark Duncan

i'm kind of looking for a best practices for this type of decision in hopes
of getting some info for pitfalls that might occur if i choose to create the
dynamic tables for each workflow.

19 May 2006 7:12 PM

Jim Underwood

Look into makign a rules engine, and see if it can help with what you need.

http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/SQLruleengine.asp

Also, explain in mroe detail what you are trying to do, and folks may be
able to offer more detailed guidance.

Show quote

"Mark Duncan" <MarkDun***@discussions.microsoft.com> wrote in message
news:5F58F22F-1D08-4AF8-BE85-1D3677C90BE7@microsoft.com...
> i'm developing an application that currently creates tables in the
database
> after a user has completed the design for a workflow. having never
created
> an application like this i keep having second thoughts about whether this
is
> a good idea or not. i keep debating with myself whether i should force
all
> data to be fit into static tables created at installation or if the more
> flexible creation according to the users design is the best method. has
> anyone been down this road before and if so what did you choose and why
and
> how did it work out?