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:-
POSTS
id
text
itemType
item1Id
item2Id
item3Id
etc
Or alternatively I could have a "posts" table, an "items" table and a "posts_items" table like this:-
POSTS
id
text
ITEMS
id
type
POSTS_ITEMS
postId
itemId
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:-
POSTS
id
text
itemData
In the last example the itemData field could contain something like this for each record:-
I'd use the 3 table approach if there may be a many-to-many relationship between attachments and posts. If, however, attachments can only "belong" to one post (a many-to-one relationship), then you would not need the posts_items table, instead including a foreign key in the items table pointing to the relevant primary key in the posts table.
In either case, retrieval is no big deal (once you are comfortable using joins ).
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
thx for the answer look like this is an answer for my question in different post Now, I need to find out how to do join and reversion on join method :P
jimmyoneshot, why would you upload your item to database and what type of database you going to use? I am not saying that you cannot do this.
Keep in mind that if you take this approach you will have very slow working database and on top of that your item will not be searchable. You will need additional data fields to accommodate search functionality. Not accounting for the speed your datafiles will grow.
Why don't you just generate index for each uploaded item and insert indexes and associated name of the item, who item belongs to into database, location of course for faster open on the UI. And you will need single table for uploaded item, second for users (which you need anyway).
That is it.
Structure roughly would look like this:
POSTED_ITEMS
itemid
userid
itemname
location
USERS
userid
sysfolder
All items stored for example in sysfolder/location/itemname
That will give you ability to control and correct on the fly any errors which will be provided by your users.
And by the way this approach will allow your users upload simultaneously any authorized type of files without messing your database...
Few years ago I was asked to build something similar, only limitation for users was no more than 10 files at same time from the listed types of the files (images, videos, MS Office docs).
File types verification allows me to restrict what is been uploaded to web server.
Reply With Quote
).
look like this is an answer for my question in different post 
Now, I need to find out how to do join and reversion on join method :P
Bookmarks