[jens]

Inspired by another post here I have the following questions:

What would you suggest as a solution, or how would you reason, under the following circumstanses:
- There is a need to present a DB (data base) on the web (build a front end for the DB on the web).
- The DB is quite complex (30+ tables with several many-to-many relations)
- The DB will be quite large (100.000+ (maybe 1M) records per table)
- There will (hopefully) be a lot of concurrent users (perhaps 1000+)
- There will be far more searches then writes to the DB (maybe 50 times more)
- Speed and ease of use for the end user is essential. (Priority one)

Questions:
What language would you recommend (is interpreted a drawback)?
Any DB that you would NOT recommend? (Yeah, I do relize that Access is not a choise)

This is a very open question, mostly in the spirit of trying to avoid any severe pitfalls.

Regards
/Jens

[Martyr2]

Well if you haven't actually done the database yet I would recommend MySQL and then use PHP for the coding. There are several reasons for this solutions...

1) MySQL is a great web database and works great with a back end server-side language like PHP.
2) MySQL is an index database which means that it organizes its data for optimal speed using indexing ability. It can make writes a little slow but it is perfect for reading speed... which is what web sites do, read a lot.
3) PHP is a very flexible, very advanced, and an easily extended language that works great with MySQL, has tons of documentation, is free, and can be extended using various plugins and addons (many which are already included with PHP like libraries for drawing ... aka gd... and others to interact with various other databases like oracle, sql server etc)
4) PHP is constantly updated and fixed by thousands of developers as well as has great resources as www.php.net.

The combo running on either a windows, in particular a Linux, machine is a great choice. If you can I would run it on a Linux machine and it will be able to EASILY handle over a 1000 concurrent users. It is the choice of many big commercial websites.

The second combo you can go with is the Microsoft solution where you have a database like SQL Server and then interact with it through PHP or more specifically, ASP.NET. ASP.Net is a good choice if you already know about a lot of Microsoft development products like VB.NET or C# and you can create projects for the web through Visual Studio. Personally I am not a huge fan of ASP.NET but it too has been successfully implemented on several high end commercial sites which handle way more than 1000 concurrent users.

Databases I don't really recommend for the web is Cache 4 (or any btrieve database), Oracle or like you said MS Access. I say Oracle because that database is pretty bulky and while it is a powerhouse database, it is a bit too much for a typical website I find. That is just personal opinion. As for the language, PHP, ColdFusion, ASP, JSP, Python and Perl are pretty much your choices. I wouldn't go with anything else there.

So the choice is up to you in which you feel more comfortable running.

This post has been edited by Martyr2: 01 December 2008 - 11:12 AM

[skyhawk133]

I'll second Martyr2's recommendation of using MySQL for the database. Dream.In.Code is run on PHP and MySQL and handles around 350 queries/second and 30 HTTP requests/second. It does this on 1 server. MySQL has been extremely scalable for us. We have several tables with well over 1 million records, and over 8,000,000 records in the database as a whole.

[jens]

Thank you for your input!

@Martyr2
I do not understand the following:

Quote

The second combo you can go with is the Microsoft solution where you have a database like SQL Server and then interact with it through PHP or more specifically, ASP.NET.

Does this mean that PHP is some kind of ASP?

Ok, I guessed I'd get the answers Linux + PHP + MySQL...

Thing is I have a problem with interpreted languages. Because of this I decided to go for a compiled language. While I as a principle embrace the Linux and open source world I've always had a really hard time with Linux.

This is a little side track...
Stuff missing, I download it, it won't install, some owner rights not rigth, when changed it still won't run, some obscure attribute wasn't set right, now all is well and I'm back to step one - stuff missing and into a seemingly endless loop of dependencies and strange creatures and programs named XFRIDGRYPH and stuff like that. When - at last - I got my system up and running I start searching for documentation... *MAN* there are many problems.

On the other hand I downloaded VB Express edition, double clicked and started programming. Documentation - F1 - a whole flood of info (although hard to wade through at times).

Where does this leave me?
I've been looking at C# and VB.NET and since VB.NET will come in handy at work I went for it. To my dispair and/or sorrow I lately relized that Visual Web Developer doesn't use compiled VB (as I understand it) but VB files that are interpreted.

In short:
I know nothing about PHP
I have some knowledge of VB.NET
My realtionship with Linux is somewhat strained.
I've been working a little with SQL server.

Would you recommend me to start learning PHP and MySQL or should I keep going at the MS-track? Why or why not?

Regards
/Jens

[PsychoCoder]

As a Microsoft Developer I would have to go with MSSQL and ASP.NET (utilizing C#). At my last employer we had a database (extremely large) with more than 10,000 tables and far more than 150,000,000 records (granted some of those were stored in off-site .bak files)) and between the in house and web users we were handing well over 125,000 concurrent connections and had zero or negligible affect on the DB servers.

I'm not sure what the deal is people have with ASP.NET, with the languages I can program in ASP.NET w/C# is always my first choice. I can show many sites (with 1,000,000+ registered users) built solely on the .Net Framework

[Martyr2]

jens, on 2 Dec, 2008 - 07:49 AM, said:

Thank you for your input!

@Martyr2
I do not understand the following:

Quote

The second combo you can go with is the Microsoft solution where you have a database like SQL Server and then interact with it through PHP or more specifically, ASP.NET.

Does this mean that PHP is some kind of ASP?

PHP, like ASP, is a server-side language. That is, it is run on the server and when a user sends the server a request for a page, the web server software finds the page, passes it to an interpreter (PHP) where PHP executes the code in that page and outputs a response in the form of HTML, Javascript etc. That output is then sent back to the user in the form of the retrieved page.

This means that PHP can interact with several types of database. I run 180+ sites using ASP and SQL Server, using the same SQL Server I run databases that interact with PHP pages. That means I have web pages that could be ASP or PHP from the same machine.

If you don't know much about PHP and are more comfortable with VB.NET you will want to go with the all Microsoft solution then where your database would be SQL Server and you are doing ASP.NET with VB.NET as your code of choice.

In the end go with what you know unless you are really up to learning something completely new and don't have to "hit the ground running" so to speak with a company project. Like Psycho has pointed out, there are tons of sites that use this combination and have used it with extremely successful results.

[Craig328]

Well, I'm going to mention a bias here but, there isn't an easier language to learn that ColdFusion. If you can code HTML, with not a whole lot of effort, you can learn ColdFusion. I've been a CF dev for over 10 years and I haven't encountered anything yet that I couldn't do with it.

It's a tag based language (although anything you do with a tag you can do with a built in scripting language it supports) and it very neatly encapsulates lots of complex functionality for you. The biggest downside to it before this year was that it cost money. However, NewAtlanta made their J2EE BlueDragon platform open source this summer and it's now known as openBD. BlueDragon is their CFML product and I actually prefer it over Adobe's. I understand there's an effort in the works to open source Railo (another CFML engine) soon as well.

In any event, one of the nice aspects of coding in CFML is the rapid development time. I work for a company that employs devs for PHP, ASP, ASP.NET and CF. We in the CF part of the family habitually churn out product 3-4 times as fast as everyone else. It's just easy to write and allows you to concentrate on routines and methods and business logic as opposed to "how do I open a connection to the database?".

BTW, opening a connection to the database in ColdFusion is as easy as:

<cfquery name="recordVLView" datasource="MyDatabase">
	 INSERT INTO vLViews (vLId, userId, viewdate, sourceid, tableid, itemid)
	 VALUES (#variables.locIdList#, #session.userId#, getDate(), 3, #client.distList#, 0, 0);
</cfquery>

That CFQUERY tag opens the connection to the database, passes in the proper user access info, passes the query in, retrieves the query product (if any) and closes the connection.

Anyway, enough evangelising. Google any of it and you'll get all the info you might be interested in.