www.webdeveloper.com
Results 1 to 6 of 6

Thread: Multiple Attachments on Posts

  1. #1
    Join Date
    Dec 2010
    Posts
    232

    Multiple Attachments on Posts

    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:-

    {"itemType":"photo","item1Id":"7","item1Id":"8","item1Id":"9"}

    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?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    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

    eBookworm.us

  3. #3
    Join Date
    May 2012
    Location
    Yangon, Myanmar
    Posts
    5
    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

  4. #4
    Join Date
    Dec 2010
    Posts
    232
    Thanks NogDog. I'll give it a try.

  5. #5
    Join Date
    Mar 2006
    Location
    Discworld
    Posts
    75
    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.
    Last edited by alexsts; 06-04-2012 at 04:00 PM.

  6. #6
    Join Date
    Mar 2006
    Location
    Discworld
    Posts
    75
    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.
    Last edited by alexsts; 06-05-2012 at 03:12 PM.

    Please consider the
    environment before printing

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles