Fair warning... dynamically creating a whole bunch of tables in a database in order to support a whole bunch of users is probably not the best way to handle such a requirement. Generally and broadly speaking, "lots and lotsa tables" is usually not a Desirable Thing.
"Using dynamically created tables is the best way to manage the system I am using."
Ashleigh, no offense, but never in over ten years of database design have I seen an instance where dynamically creating tables is the best way to manage a system. On the flip-side, I've had to fix or write complex code-arounds for many databases that were implemented this way. The reason you were having difficulty finding out how to do what you plan to do is, basically, because SQL Server and relational databases in general are not designed to do things the way you are planning to do them.
See if you can't add the scalability you need through one additional table or even one additional field in an existing table. It could save you hundreds of lines of code and many hours spent in performance tuning.
You have probably missed something in your data model if you need to build a database this way.
The only time I could ever see you doing something like this is if your boss (who really shouldn't be involved in the design process) tells you that this is the way he wants it built.
I assume this post links to your other post regarding the use of stored procedures and the data you have talked about in that post.... if so you should be able to do everything you have talked about in the other post without having to create these tables.
I'm creating a site which creates 'pools'. These pools are created by a 'manager' using a 'managementKey'.
When the manager registers with his key, I register his information including the name of his 'pool'.
A table is then made using the 'pool' name. This table holds all the user information. MemberID, Password, and their details.
The reason we elected to do it this way was, there will be lots of instances where we will be sorting through the table, displaying results etc. and we are looking at a possible 500K-1 million users. That's a lot of data in one table.
To log in, users enter the 'pool' name, 'memberID' and 'password'. It will then look for the memberID and password in the selected field.
Another table is also created for each 'pool' named
'pool'Results. This holds information about each members settings, results, etc. This table will hold around 5 entries a week from each member of that 'pool'.
On paper, this method is much neater and easier to manage than 4-5 massivetables.
If you can think of a better method for this, I'm all ears. But from what I can see (and I'm happy to admit I'm a novice sql man) this is the best way.
well I'll discuss it with the people upstairs, lol.
I've had a chat with the man (lol).
Basically, what we concluded was. Queries won't be searching through 1 million rows. If I use 4-5 tables only, finding results (linking the tables up) will be querying through around
1million x 20 x 8, so um, 160 million rows. That's for each user, probably twice a week. That's a lot of row searching. So by dividing it all up, we thought it would make it run a lot smoother.
In terms of db updating. The db's will all be based on 3 templates. So can you use a script (in ms sql) to update them? reguardless, I don't see any reason why we'd update. Of course, those are famous last words, LOL...
Its still better to go with 4-5 tables. You'll add one additional field to the tables to indicate the manager "pool", and then index this ID. With an index, the optimizer will be able to quickly locate the entries for a particular manager and then search through just those entries, much as if they were in a separate table.
On the plus side, you won't have to write all your code as dynamic. Dynamic SQL is difficult to debug and less efficient than direct SQL.
If this is important to you and your manager, get a database consultant to review your design. Then, if something doesn't work you have someone else to blame. This is what consultants are for.