Is This Feasable?

I am looking to design a system that will allow users to define their own
"objects". One of the methods I considered was to allow them to generate
tables representing these objects, and allow them to add and remove columns
from these tables as necessary.
These tables could potentially range from 3-50 columns and 1-10,000,000 rows
and there could be tens of thousands of new tables generated by users.
Is this feasable? What sort of hardware would be required to cope with such
a system (assuming thousands of simultaneous users)?
Hi
Based on what you described, you are starting to talk enterprise class
hardware here.
8-16 way Intel Itanium2 Servers.
64GB+ RAM
Large SAN like EMC behind it.
And of course, clustered for high availability since it seems to be mission
critical.
Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
Short of knowing more about the application, it usage patters, transudations
per minute, not much more can be said.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mika" <mika@.hotmail.com> wrote in message
news:eOoKFHMoFHA.4012@.TK2MSFTNGP09.phx.gbl...
>I am looking to design a system that will allow users to define their own
>"objects". One of the methods I considered was to allow them to generate
>tables representing these objects, and allow them to add and remove columns
>from these tables as necessary.
> These tables could potentially range from 3-50 columns and 1-10,000,000
> rows and there could be tens of thousands of new tables generated by
> users.
> Is this feasable? What sort of hardware would be required to cope with
> such a system (assuming thousands of simultaneous users)?
>
|||Allowing thousands of users to generate their own tables doesn't sound like
a way to build a scalable database capable of handling tens of millions of
rows of data. Not unless all your users are experienced database designers.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:_M-dnZ2dnZ00zAH8nZ2dnUuoYt-dnZ2dRVn-z52dnZ0@.giganews.com...
> Allowing thousands of users to generate their own tables doesn't sound
> like a way to build a scalable database capable of handling tens of
> millions of rows of data. Not unless all your users are experienced
> database designers.
Obviously the creation of the tables and columns will be tightly controlled
by the application.
What alternative technique would you suggest which would allow them to
simulate the creation of user-defined tables/objects?
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uAVS7TMoFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi
> Based on what you described, you are starting to talk enterprise class
> hardware here.
> 8-16 way Intel Itanium2 Servers.
> 64GB+ RAM
> Large SAN like EMC behind it.
> And of course, clustered for high availability since it seems to be
> mission critical.
> Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
> Short of knowing more about the application, it usage patters,
> transudations per minute, not much more can be said.
Hmmm thanks. Gives me an idea of what may be involved.
|||Tables are not objects. The question you should be asking is how will you
represent RELATIONALLY the DATA that your users want to store. That has
nothing to do with the application creating its own tables for users. Since
you haven't told us anything about your data I can't answer your question.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sd6dnWPLCqA3pGLfRVn-qA@.giganews.com...
> Tables are not objects. The question you should be asking is how will you
> represent RELATIONALLY the DATA that your users want to store. That has
> nothing to do with the application creating its own tables for users.
> Since you haven't told us anything about your data I can't answer your
> question.
I realise tables aren't objects but I was considering using tables to
simulate objects.
|||>> Is this feasable? <<
Where did you get the idea that a random user can design a schema on
the fly? Are you such a bad programmer that this is true?
No. The name of this design flaw is EAV and there are los of postings
about why it is a stupid, dangerous idea. It comes up over and over
again with newbies -- like Martingales in gambling.
|||Conceptually a table might be regarded as representing a set of facts about
a particular class of objects but that analogy is a weak one because there
may not be a one-to-one correspondence between what users perceive as
"objects" and their efficient and logical representation as tables in an
RDBMS.
What I infer from your previous posts it that you don't know at design time
what data the users will wish to store. In most enterprise environments that
wouldn't generally be an acceptable starting point for a project. The first
step would be to research users' needs and identify a set of requirements
up-front. Without doing that it may be difficult to make a business case for
any kind of solution. Also, most enterprises entrust their data to database
professionals precisely because there is value in conforming information to
a standard data model. If users in an enterprise build their own models on
the fly then much of the data's value is lost.
Another possibility is that you are talking about providing some kind of
database hosting service to consumers, perhaps over the internet. In that
case I suggest that SQL Server may not even be the right tool for the job -
since it isn't obvious what kind of benefit you could expect to gain from
storing an unknown set of data relationally. Also, be aware that Microsoft
has a separate licensing model for Application Service Providers. I've no
idea if that will apply to you - I'm just guessing.
David Portas
SQL Server MVP
|||>> Obviously the creation of the tables and columns will be tightly controlled by the application. <<
LOL! Anyone who can get to a tool like QA can destroy everything.
Over time you will have a lot of applications, and changes to existing
applications. Since each module and each tool does not have to have
the same integirty rules, they will drift. Since each of the users
will have a different data model, how would you write integrity
constraints anyway? Johnny has a rule that (age > 18) and Sally has
the rule that (age >= 18) and they are in the same Monster database.