Planning: DataBase

A critical part of this software will be an interface with it's master storage mechanism. We will be storing potentially thousands of messages within potentially hundreds of forums. It isn't improbable that one installation will actually drive the forums across tens, possibly hundreds of similar or distict websites on the same host (think: template websites or hundreds of commercial/free websites including phpMyForum as part of the 'deal').

Requirements

Database Interface

Our potential audience will be any website that is capable of hosting PHP scripts. 99% (A guess, ok?) of these hosts will also provide access to a data of some kind. In most cases we suspect this is MySQL, but could be Oracle, MSSQL, JODBC (Java ODBC interface), Windows ODBC (MS Access, etc) or otherwise. Like most standards that emerged whilst applications were being built, SQL is the common language used to access databases, yet it is different across databases, in different ways.

Therefore we must have a system of loading the right SQL query for the right database. For this occur phpMyForum must either know which database is being used (pitfalls, see below) and thus load the right calls, or be told to load a particular file where it will find all the database actions with a common API to them.

The two approaches I can immediately think of for this problem as follows:

1. Tell the software by way of API call, or by configuration file, which database it is to get it's data from. The software will then source particular SQL calls based on the code within individual functions, perhaps using a system whereby ANSI standard SQL calls are used by default, with database-specific versions provided to override the calls where necessary.
2. Load one or more files that provide the functionality of all database accesses on a per-database basis. This differs from the above method because the each database call will be held inside a 'driver' file(s) and encapsulated within an object whos methods will provide a db-neutal API for the main forum software.

The first approach may be quicker in the short term as will can write into the code MySQL calls immediately, and add checks and additional calls for other databases. However, once we have three ore more database supported code managebility may prove difficult, particularly if underlying logic such as PHP loops or different SQL logic such as sub-selects are used in some cases.

The second approach will mean constructing an API of database calls that will provide efficient access to the data needed in all possible cases. Behind this API can be private methods particular to the database for which it is written, allowing perhaps entirely different PHP and SQL logic to obtain the data in the most efficient way possible using the featureset of that database.

I'm opting for the second of the choices personally (jmkg). We should be able to provide API methods for obtaining each individual datum such as 'subject' or 'date' of whatever we have loaded such that full customisation can be acheived in the viewpacks where individual datum may be required to be displayed. We should also be able to provide a small set of load/read methods for common operations such as 'seek a message' (load details into db driver) and 'return a message' (get details from db driver into main forum object). This seems to be a very sensible and scalable approach.