Database Design

The Oscailt database layout is relatively simple and straight forward with only a handful of tables. This allows for great flexibility and ease of maintenance. Future enhancements are likely to result in additions to the tables.

In this section only the main parts of the database are described. For the full details on each table (e.g.field names and types), one should consult the code (see script install_database.php). The list of database tables is as follow with the more important ones being highlighted.

Database Tables

• STORIES -contains story contents and all other associated data.
• COMMENTS -contains all comments for all stories and all other associated data.
• ATTACHMENTS -contains URLs to all image and audio files. Indexed by tables STORIES and COMMENTS.
• TYPES -contains the list of allowable story types and their assigned ids.
• REGIONS -same as for TYPES except regions.
• TOPICS -same as for TYPES except topics.
• LANGUAGES -same as for TYPES except languages, also stores language codes.
• EDITORS -contains the list of editor/administrator user accounts.
• EDITOR_PERMISSIONS -pretty self evident.
• CONFIGURATION -controls number of allowable images, audio files per story, per comment, site name, newsire query length etc

Query Caching

A database cache has also been implemented and this greatly reduces the number of database transactions which happens to be one of the most important criteria for Oscailt, and useful when hosting with a provider that places limits on your database access.

It is implemented so that it caches the results of each unique SQL statement as a file in the querycache directory. The cache is cleared everytime an update statement is executed.

Data Access Objects

Within the Oscailt codebase there are three main objects which represent data access functionality:

• Story
• Comment
• Attachment

These objects have their own persistence and retrieval methods, and can also be created using the Story Query object which searches the database.

-- VinnieC - 08 Aug 2005