0

I am trying to create a blogging sites. My problem is how to handle the blog database system.

For example , i have a scenario like for each blog there may have more comments,likes,photos,videos for one blog. so i created separate table for comment,likes,photos,videos and insert each record with related blog id (ex : one blog can have more than one photos,comments ,videos...).

But the problem i am facing really when i am trying to create a summary of the blog by creating a view for blog with related tables. The blog view has duplicates when i joining blog table with comments,likes,videos tables using blog id.

Please help me on this ? can anyone suggest any idea for handling this problem or can anyone would know how fb or twitter handle this situation ?

please correct me if am wrong i ready to redesign the database if anyone has new idea ...

It would be great if you help me on this.I am struggling to find the way for this ...

Can anyone share me a example .

user3678467
  • 1
  • 4
  • This is a rather broad topic and hard to answer specifically.. Very much depends on what you have done, and you have shared no details (or very vague details). If you tell us what platform you are building this on there are many examples on how to build blogs out there. Also, just some general advice, start out small with a simple blog and get that to work well, then expand on it. – JensB May 28 '14 at 07:39

1 Answers1

0

What you need is to build aggregation mechanism. After you read from SQL view this mechanism should compose those multiple rows that were produced with LEFT JOINS into object that fits you needs. It most likely will have arrays for photos and other join-able data.

Here's an article that is showing how to create such an object.
Database results as objects or arrays?

Community
  • 1
  • 1
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • by using objects or arrays how can you know that the joins doesnot have duplicates means each blog object must have their own comment,likes,photos,videos objects internally. – user3678467 May 28 '14 at 11:32
  • @user3678467 you check for that in mechanism that constructs object and eliminate them. – Matas Vaitkevicius May 28 '14 at 13:08
  • what mechanism ... i am not pro at mysql ... i jus want to kno how can i return a single blog response with related information (photos ,comments). – user3678467 May 29 '14 at 11:10
  • How are you reading data from database? What is the mechanism that does the SELECT? – Matas Vaitkevicius May 29 '14 at 11:19
  • i am trying to create a view with all the related attributes for each blog for example the view must look like blogid -1,blog content,blog_photos(select photos from photo where blog_id =1 ) and similiarly comment,videos etc.The photo table will return more than one data so how can i handle i stuck on this ? if i have a seperate table also anyone way i need to combine all blog related response.please also see fb json structure similiar scenrio please find below comment i attached for reference i need response like this to process the response straightway instead of sending more select query. – user3678467 May 30 '14 at 06:08
  • "data": [ { "post_id": "", "updated_time": 1400667757, "created_time": 1400667757, "app_data": { "attachment_data": "[]", "images": "[48448623207,4844831625457]", "photo_ids": [ "4844831623207", "4844831625457" ] }, "comments": { "can_remove": true, "can_post": true, "count": 0, "comment_list": [ ] }, "likes": { "count": 1, "friends": [ ], "user_likes": true, "can_like": true }}] – user3678467 May 30 '14 at 06:09
  • Structure that you have posted is called JSON. It was produced not by database but by aggregation layer that is calling database. Structure goes like this: there's endpoint that you are calling to get JSON, then SQL SELECT is executed that reads from SQL database (Your table joins), then in same layer that is between endpoint you called and database sits mechanism that assembles rows into JSON that you have posted. – Matas Vaitkevicius May 30 '14 at 06:51
  • thanks i kno the json response i posted that because i want to express how my response will be.I requesting data from mysql as a single response that will give all the related data for that blog.. – user3678467 May 30 '14 at 10:03
  • i want response like this what i have to do ? http://stackoverflow.com/questions/23882844/mysql-insert-data-into-multiple-table the above link will show you how the blog table i need please refer to this... – user3678467 May 30 '14 at 10:04