Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts

    is it possible to create db tables using fso or something?

    Hi,

    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...
    Last edited by jeskel; 10-14-2003 at 09:45 AM.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #6
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: is it possible to create db tables using fso or something?

    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.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #7
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanx a lot to you two.. Everytime you post something I feel like I'm learning so much... Top ASP buddies!

  • #8
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Last edited by jeskel; 10-14-2003 at 06:57 PM.

  • #9
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #10
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #11
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    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.
    OracleGuy

  • #12
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanx to you all....


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •