handling images, videos, docs for custom cms

Question

I am in the process of building a CMS using php, however I am struggling on how to handle the various different assets that a dynamic page could serve, typically an average page will contain pictures of various sizes(thumb, body, main, etc), inline images and various different assets(pdf, flv, mp4, doc, etc).

At the moment a user can upload assets and create a folder on the server, I just wanted some techniques and concepts on how to manage this in terms of deleting, editing and linking to my dynamic pages within the cms? I already have a content table which contains all the content(meta_stuff, title, friendly_url, content_text, etc).

On upload should a reference be stored somewhere in say a asset table? Should all paths to assets(images, docs, videos,etc) be stored in one table? or separate for each asset? Should multiple image sizes(small, medium, large, etc) be stored in different fields e.g(assetid, smallpath, mediumpath, largepath? What technique to use to link assets to the dynamic page? should this be a joined table or single? how do I go about retrieving the different assets for a page several pdfs and several images? How to handle deleting of assets as this could be referenced to another dynamic page? and anything else you think would be beneficial?

Thanks for all your help

Answer 1

Here are some basic things to keep in mind when dealing with resources (videos, images, documents..) in a scenario similar to what you're describing.

You'd want is to know what's being uploaded, what kind of stuff are being uploaded and who's uploading. To do that, it's good to have the following tables:

Edit: Sorry about the mistake, asset_types.asset_type_id should be linked to assets.asset_type_id and NOT assesst.asset_id

(Note: This is just an outline, of course you'll have more fields)
This model makes it easy to do the following tasks

• Upload: When something is uploaded, you store it somewhere (with the correct .htaccess configurations) then store that path in the table.
• Delete: When the user is trying to delete something, you check if he's the owner.
• When you want to add a new type, you don't need to create a new table.

Hopefully this will put you in the right direction.

Update: Answering your comment, when a user wants to add an image to an article for example, you have two options here:

1. The user is presented with a button/select menu/whatever to choose the desired image, once clicked it will add <img src=LINK_TO_RESOURCE" /> to the body of the content (Make sure you protect yourself from XSS).
   
   OR
   

2. You use your own simple markup, when the user clicks on an image this [[PREFIX_IMG:IMG_ID]] will be added to the body of the content, when the content is viewed you'll replace that "tag" with <img src="LINK_TO_RESOURCE" /> which you've acquired using IMG_ID.

2nd Update: Well you have a couple of options to handle resources that aren't directly embedded in the "articles" themselves.

1. You can have a field in the "articles" table you can call summary for example, and one more column to the assets table which you can call asset_sub_type and have different types like summary. Then when you want to view the summary you fetch the summary text/title from database and you add the resource to it.

2. (This is the technique I use) When fetching the summary from the database I see which image is the database that is related to that article and the resize it on the fly with PHP and append it to the summary.