[HarbingTarbl]

Hello, recently a few friends and I have decided that we should try to further our knowledge of web development and databases. How you ask?

Why by creating our own web based DBMS of course .

We all have some experience in either database design and management, web programming with PHP/Javascript, web design (the artsy stuffs) and programming client applications with Java.

For the past week or so however, we have been arguing back and forth about what would be the best way to implement Client/DB communication and I was hoping you guys could lend us a hand.

First off, we can't seem to agree with what language we should even use for the server side. I'm thinking that we might want to use PHP as its one of the languages we all have experience with. However, some of the guys who know Java have pointed out that Java is not only faster (which is something that is required for a DBMS) but can also be used on the client side as well to create a web application.

The benefits of using Java in this case are fairly obvious, we won't have to deal with creating things in HTML/CSS as the Java applet can contain the GUI and we can store data on the users machine which will reduce server load.

The problem with using Java however is that I'm not sure if we can accomplish it, only one guy (out of five) knows Java and he doesn't have any experience with much beyond some simple desktop applications.

If we decide to use PHP however, we will need to use Javascript or a client based Java applet to communicate with and retrieve data from the server. The problem with this however, is that we are worried about how interactive the application can be if it is limited by HTML/Javascript.

We plan on marketing this application/system/thingy at some point, so we want to be able to make sure it's release is as polished as possible. We don't have any illusions that this will be a simple project however, or even something we can finish in a year or so.

... As you can tell this project has not really gone anywhere so far (but we have learned quite a lot so I guess that's something)

I'm hoping that someone has had some experience with creating web based DBMS's and could perhaps give some advice on how to tackle some of the issues with a creating a web interface.

Edit: About is hard to spell...

This post has been edited by HarbingTarbl: 10 February 2011 - 10:36 AM

[Dormilich]

are you really going to create a DBMS like PostgreSQL or DB2 (cf. DBMS)? then the obvious choice would be C/C++. PHP/Java are suited to communicate with a DBMS …

PS. IMO, Java ain’t that much faster than PHP, both are interpreted languages (PHP compliler vs. JRE).

This post has been edited by Dormilich: 10 February 2011 - 10:47 AM

[HarbingTarbl]

Perhaps I was using the wrong terminology, by web based DMBS I meant a web front end for an existing databse (such as MySQL) that could be used to manage a remote database from a web browser.

Our long term goals had been to market this to local small businesses as an easy to use database system which could be specialized to their needs and accessed through a web browser with minimal training.

This post has been edited by HarbingTarbl: 10 February 2011 - 10:56 AM

[Dormilich]

something along the lines of phpMyAdmin (in terms of functionality)?

if you go for PHP, IMHO your only possibility is to use PDO for communication with the database (PDO is the only system that is capable of comunicating with different databases). should you want to have a complete Database Abstraction Layer, you would need an existing ORM library/application like Doctrine.

[HarbingTarbl]

Are there any pitfalls we should be aware of when doing something like this in PHP? For example, we have been toying with the idea of storing recent table/row operations in the

$_SESSION

variable to reduce the amount of times the database needs to be queried.

Also, would it be best to serve the data to the pages when the page loads using PHP (Simple to code, but page updates would require a refresh)? Or to use Ajax to request the data from the server and then apply it to the web page using a template system (this option seems a bit more responsive and extensible, however it is very Javascript heavy and more complicated)?

What I'm looking for isn't really an explanation of how to accomplish our goals, but rather which goals to accomplish... if that makes sense.

[Dormilich]

unless the DB server resides on a different host, you gain nothing from saving database data in a session*. however you can optimize the database itself (using views, temporary tables and DB sessions).

whether to use AJAX or a page reload depends on what you need to display. the more data change (per request) the more equal both methods get. a page reload would have the advantage that you can use a templating system and your page works even if Javascript is disabled. on the other hand side, you have to transfer a complete HTML document each time.




* - you can set the session handler to store its data in a database …

[e_i_pi]

HarbingTarbl, on 10 February 2011 - 01:37 PM, said:

Also, would it be best to serve the data to the pages when the page loads using PHP (Simple to code, but page updates would require a refresh)? Or to use Ajax to request the data from the server and then apply it to the web page using a template system (this option seems a bit more responsive and extensible, however it is very Javascript heavy and more complicated)?

What I'm looking for isn't really an explanation of how to accomplish our goals, but rather which goals to accomplish... if that makes sense.

I'm doing something similar to what you're talking about. I run my own online browser-based game, and whenever a new map or new combat system is created, I have to go into phpMYadmin and do it all by hand, or dump it to XML, which has problems of it's own. I've decided to rewrite the entire site, work the DB to 4NF, and create a front-end that allows users (not just myself) to go into the DB and create their own maps, combat systems, etc.

Some of the key challenges I've come up against are:

• Low bandwidth-cost screen updating after a DB update (accomplished via AJAX and the MySQL LIMIT clause)
  
• Neat display of the contents of a single row in an HTML environment, in a way that logically abides by the particular columns variable types
  
• Creating a point-and-click interface for editing, deleting, and adding rows
  
• Developing an understanding of the information_schema DB tables that store intrinsic information, in particular the tables named COLUMNS, TABLES, and TABLE_CONSTRAINTS
  
• Figuring out a neat generic function to resolve constrained row data (IDs) into readable data for drop-down lists in the AJAX forms
  
• Creating generic code that displays a form whose contents are conditional upon the design of the particular table
  
• Heavy security around the add/edit/delete AJAX calls to ensure no MySQL injection or other attacks can occur

Hope this helps