I'm currently writing an e-learing web-application. I'm sorry if this is information overkill but I think it's better to describe it in detail so you get the idea. I have made thought on every point and would just like to know if my approach is ok or totally off the mark for this is the first time I handle that much data "on the fly".
The e-learning courses contain files of different types, e.g. Word files, PDFs, Flash (yes, sadly no way around it), videos etc. and have 1 or more sets of tests, each containing questions where images can be attached (one each question) and answers of course (single- or multiple-choice), which also can hold images (one each) in addition or instead of pure text. Upon completion a PDF (certificate) is created and stored on the server for the user to download at any time.
- Course creation is handled similar to a checkout in online shops, meaning you click on "New course" and a window pops up and at the top you see a flowchart showing where in the creation process you are right now. The whole process should be dynamic so information is only stored in the final "checkout". Each part of the process is on a separate "page" but you can go back and edit things. It'll be visualized like this (I will add text descriptions to the points of course):
- First page contains only basic information, e.g. Name, version, short description and a list of topics being taught in this course.
- 2nd page is where the fun starts: Upload multiple files with description. These file uploads should not disable the use of the form. Videos shall be uploaded and encoded in background.
- 3rd page: Test-sets are created, this means categories (Test-sets) with subcategories (questions) and sub-subcategories (answers) also with multiple file uploads (images).
- 4th page: final overview and confirm.
How I want to handle things:
I would like to use temporary tables for this case to store the file paths and basic data. As the creation process can be cancelled at any time all files belonging to entries for this current creation process shall be deleted.
This means creation starts --> Process is created in temporary table
creation-processes. After the first page is filled the data is stored in a temporary table with fk to process id, same goes for any file uploaded.
As soon as a video file is uploaded I will run a background php-script which sets a database-entry in the temporary table for the video file and starts encoding via ffmpeg (3 files for each video file: mp4, ogg, webm). Uploaded files can have 4 states: "Uploading", "Processing", "Finished" and "Error".
Upon completion the Data from the temporary tables is transferred into permanent tables and then deleted. Files still being processes (as video encoding can take some time) will also have their state transferred in the permanent table. The background-encoding-script checks for the filename (which is unique: timestamp+md5-hash of filename) in the temporary table first and then in the permanent table. If no entry exists in both it deletes the files it created.
In addition to just using temporary tables I will have a permanent table "tmp_files" containing path information of ALL files being uploaded. The entries and the files listed in this table are deleted upon cancellation of the process, also after completion the entries are deleted (but not the files) for they are now stored in the regular table (course-files). So for things going wrong I can run a cron job every hour that compares the entries of "tmp_files" with the files in the regular table and the temporary table and if it holds an entry which is not present in one of the other tables this means there is a zombie file which must be deleted (together with the entry of course).
Well that's it so far. I don't know if questions regarding planning of software are welcome here but I really would like to know if this is a good way to approach this process or not and if I'm missing something essential.