large database techniques

[ryen]

Hi again,
First off, I'm thinking of starting a site of some sort which may end up having hundreds of thousands of users to keep track of, and store information about. I'm thinking of learning SQL and using ASP/VBScript to do the databases, but I think that I could make my own database system from scratch using PERL if I just knew a few more things.. I've built plain old pipe-delimited databases with PERL before, but they were very small (400 users), and I didn't need to worry about using system resources, and disk space.. but if I want many users, and if I need to allow for rapid expansion (optimistic), I'll definitely have to worry about this. So here are some things that I'm concerned about, and any advice or links to good sites would be awesome:

1) Is there any disadvantage to giving each user their own text file with information in it, as opposed to one huge file containing many users' information, each user on one, delimited line? Then, when [user] wanted to look up info, the cgi program would simply open [user].txt (for example) instead of searching through the large file. Would this technique use up more disk space due to extra file info, such as the actual file names, and attributes? But wouldn't it be faster as well?

2) Does the flock function truely stop one user from writing to a file if another user is reading it? i've read varying descriptions of this, and this could quickly become a problem with many users.

3) It seems like there is little overhead with PERL, as opposed to using SQL, ASP, etc.. is that true?

Once again, ANY help would be greatly appreciated.

 

[Coderifous]

Use a relational database solution. The first thing you may want to do is separate SQL from a database (in your head that is). Using SQL (a standard language for interfacing with DB's) you can 'talk' with most any brand of database out there. They all have their own bells and whistles, but most of them support ANSI SQL statements. This allows you to update, delete, and add data in your Database tables for whatever database system you choose - without having to learn THAT databases 'communication system'.

So that being said - SQL itself doesn't have a speed - a database server can be fast and then it depends on which one you go with. MySQL is an excellent choice, it can handle very heavy load, and is very fast - also it's easy to develop programs interacting with it using Perl or PHP.

ASP is microsoft's answer to Perl and PHP. It's an OK language - but if you know Perl, you don't NEED ASP. However the more one knows the more valuable they are. I'm just saying that becuase I'm not sure why you were saying you would might do it in ASP, but you could do it from scratch using Perl. Either way you need a database. And Either way you're going to have to right programs. I guess it depends on which language you are more comfortable yet, what software you have on hand, and what database you go with. I don't know for sure, but I'll bet that ASP has special accomodations built in to Microsoft's "SQL Server".

Have fun however you do it.

--jim

 

[Leland123]

Hi Ryen,

For the volumn of users you anticipate you definitly need a database. Postgres SQL is open source and has many features. It is alot like MySQL. I use postgres for my web site as the back-end database and the perl DBI/HTML as the front-end and I have been very pleased with the results. I have also written a VFP 7 front-end client to a postgres database server using SQL pass-through and these two products work very well together. Using VFP7's strong string handling to pass-throught native SQL to a postgress database is about like using perl's strong string handling to do the same. Using either VFP7 or Perl to access a postgres back end is about the same. Both are very similar.

You can learn more about postgres by visiting the postgres forum here at Tek-Tips.
Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[ryen]

thanks for the help Jim and Leland. I see I have a lot to learn before I really get started. I had no idea that PERL could interface with SQL. And it helps me to think of SQL as something that simply communicates with databases. Also, I don't know much about SQL to begin with. I'm taking a Web Databases class in college because I thought it would go into detail about SQL and such, but its just a class on how to use Visual Interdev, which I'm not that fond of... I think I'll research using PERL with SQL.

Also, I've never heard of Postgres or VFP7, so I'll definitely look into those. Thanks a lot guys, much appreciated.

PS. Does anyone know if these, or similar programs are free to use? I heard that SQL was rather expensive???

 

[Krel]

There are a lot of databases that are free and are just as good as the expensive ones. Try postgres SQL or MySQL, both excellent choices.

 

[ryen]

Thanks for the info, will do

 

[goBoating]

Get a copy of "Programming the Perl DBI" by Alligator Descarte and Tim Bunce published by O'Reilly. It presents the basics of relational database design, the basics of SQL, and then good coverage on the Perl DBI/DBD approach. It has clear examples and good practical content.

DBI - database independent interface layer
DBD - database driver the fills the gap between the DBI layer and a specific database flavor (MySQL, PostGreSQL, Oracle, SQL Server, etc..)

Really, if you are just starting with this stuff, the book will be well worth the $35 US.

Good Luck 'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.