View Full Version : is it possible to create db tables using fso or something?
10-14-2003, 09:42 AM
I was wondering if it is possible to create a personnal table for each person subscribing to a site where everything related to that user would be found. For example, I want my users to be able to send each others messages. Right now, I have one "messages" table where I record all the messages exchanged for every member and I make a sql request where I get the messages for a user according to his ID (his ID being recorded in the "messages" for each message sent to him). It should be a very small site just for the people of my sport club (about 30 members). But I was thinking that if I was building a site with let's say 10'000 users sending everyday 10 messages each, that would end up taking a long time running the whole table to get the messages of one user according to his ID. Am I right about it or not? Would it take no time no matter how many messages there would be?
So, I was thinking that if I had to do such a huge site, that would be great for each member to have its own table. Is that possible? Is that a good idea or not? Right now I'm using an access db, but i'll move quite soon to an sql db (as soon as my site will work correctly). As far as I know, you can't do this with FSO.. But if it is a good idea to create one table for each user, how could that be done?
So give me your thoughts about it...:)
It's a very bad idea to create a new table for each client.
Databases have no problem filtering out the messages, if the ID-column is indexed. If Access can't cope with it (which i doubt cause it's more likely it will crash due to to many simultanious users, then because the table is to big), then you should upsize to MsServer or MySQL or PostGreSQL or Oracle or DB2.
Telecom operators proces millions of SMS's etc per day without any problem.
If you would ever have a site with 10k users that make 10 posts a day, then you'll probably have an Oracle or a DB2 database (probably multiple 'federated' databases) to proces the request + you'll have some automatical archiving system (--> the best way to only keep relevant information in your operational tables and to keep responsetimes low) + you wount be saving/selecting/showing all these posts. You'll probably have a messanger based system (java-servlets or so that act as listeners and proces a new message, by sending it to the recipients messagebox (if he is online) or to his machine or your database(if he is ofline) and maybe write a backup to a table, that is only used for writing and not for selecting) etc)
<edit> About the create table issue:
you can use the CREATE TABLE sql-command.
MsAccess helpfunction has a quite good subsectionsection on DDL (data defenition language) that covers this.
10-14-2003, 12:34 PM
well, if it's a bad idea, I won't do it that way... But i'll still check the CREATE TABLE sql-command to learn something new. MySQL is not free right? you have to buy it? You've mentionned MsServer or MySQL or PostGreSQL or Oracle or DB2 but I seem to remember that I read people using a SQL db... Is that right? does that exist? DataBases is definitly a subject that I haven't seen at all by now. But I'll start soon. When my site will work, I'd like to have another db than access...
Thanx for all these informations raf:thumbsup:
mySQL is free to download (www.mysql.com). But they do have a commercial licence.
PostGreSQL is also free. Completely no strings attached for any kind of use + it realy is a stable and fast, feature packed database. I think it's even a better choice for sites that need a lot of power then MySQL or MsServer.
The others aren't free (and can be quite pricy)
If you plan on sticking with ASP and have your sites hosted, then MsServer is probably the bigger db-server that you will be useing. Most hosts suppie IIS + ASP + MsServer or Unix + PHP + MySQL.
SQL db's are db's you can query using an SQL-language. All of the above mentioned db's are SQL databases, but they all have there own SQL-language (well the more core syntax and functions are the same, but they all have some specific functions and clauses. Once you know your basic SQL commands, you quickly pick up a new db-formats specific functions and syntax-rules). If you write an application, and you only use the 'universal' sql-commands and features, then your app can be powered by any SQL-database.
But if you start using functions like top(1) (JetSQL) or stored procedures or datashaping etc, then you need to stick with a Microsoft database (or you need to change your code).
So you can develop an app that can be combined with any SQL-db (which can be handy if you develop something you want to sell without a db) but if you know that you will stick with a specified db, then it's better to look at its extra SQL-goodies to make your coding easier and to get faster responses from your db.
10-14-2003, 03:53 PM
thanx a lot raf, I'll keep this thread in mind and re-post something if I have a problem the day I'll be moving from access to another db.
10-14-2003, 04:30 PM
Originally posted by bouchel
So, I was thinking that if I had to do such a huge site, that would be great for each member to have its own table. Is that possible? Is that a good idea or not? Right now I'm using an access db, but i'll move quite soon to an sql db (as soon as my site will work correctly). As far as I know, you can't do this with FSO.. But if it is a good idea to create one table for each user, how could that be done?I concur with raf's comments, so I won't repeat what he has said.
I think you might be slightly confused though...
FSO = FileSystemObject (which has nothing to do with databases, unless you're manipulating flat-file DBs, like CSVs)
ADO = Active Data Objects (which ARE used for querying DBs)
Furthermore, ADOX (installed alongside ADO) is used to manipulate DB structures, including tables, fields, etc.
Thus, you could create new tables (not that I'm recommending you *do*) by executing "CREATE TABLE" T-SQL statements via ADO, or by manipulating ADOX objects to do the same.
10-14-2003, 06:46 PM
thanx a lot to you two.. Everytime you post something I feel like I'm learning so much... Top ASP buddies! :thumbsup:
10-14-2003, 06:52 PM
Originally posted by raf
Databases have no problem filtering out the messages, if the ID-column is indexed.
just the shadow of a doubt: what would happen if the ID column wasn't indexed?
10-14-2003, 07:08 PM
Originally posted by bouchel
just the shadow of a doubt: what would happen if the ID column wasn't indexed? You'd have *SEVERE* performance problems! :D
Indeed. Indexes realy speed up searches conciderably (20 times faster or so i believe (depending on the content))
If at all possible, variables that are frequently used for selecting records, should be numerical and indexed.
10-14-2003, 10:32 PM
Although this isn't completely related, you can create temporary tables that can last as long as the connection or until the server is restarted with at least MsSQL, I dunno about the others.
I think it is if you put one underscore before the table name a table that lasts the duration of the connection and two underscores for until the server reboots.
There are times when that can come in handy. I use them sometimes when I'm writing scripts to convert and compact data from one db (and usually different server) to another.
10-15-2003, 09:29 AM
thanx to you all.... :thumbsup: