I'm developing a system whereby users can upload files then make posts on a thread whilst attaching multiple items/files to each post i.e. one user may make a post with a single photo attached, another user may make a post with 3 or 4 photos attached and another user may make a post with 2 text document's attached or another type of file etc etc. I've limited it so that only 1 item type can be applied to a post i.e. a user couldn't attach 1 photo and 2 text documents but could upload 5 photos.
Previews of each of these attachments will then be shown within the post itself. Very similar to facebooks wall. This will also be used to show things like - "John just added 4 photos" with the thumbnails of each of the 4 photos he just uploaded being shown in the post that says this.
I'm wondering what is the best way to store this in a database? One way (which seems very inefficient if any items don't actually have attachments) would be to have a "posts" table like this:-
Or alternatively I could have a "posts" table, an "items" table and a "posts_items" table like this:-
The above would be hard to retrieve in mysql though I think based on the fact that the number of item attachments varies between posts
The final way I thought of was to include all the attachment data in an array inside the posts table in a basic table like this:-
In the last example the itemData field could contain something like this for each record:-
This I think would eliminate the need for a complicated sql query to retrieve all posts but would also be hard to query against at all
Can anyone suggest anything for this perhaps in the form of a similar example using sql and mysql and am I thinking along the right lines?