0

I've created a feed but I'm not sure whether it's okay or not, hopefully will get some answers to correct it if there's anything wrong.

I'm using MySQL.

When a user does something, his activity is saved in activities table as you can see, but this user isn't alone. He has friends. I use feeds table to create a feed for a friend of this user. I'm using fan-out model (I hope it's clear. If there are 10 friends of a user, 10 new records are added to the feeds table when that user does something)

The question is, sometimes users upload photos. When a user uploads a photo, activity_type_id becomes 5 (photo) and I hold images in activity_image to know which post has which image(s). So, if a user is uploaded a photo, I deal with 4 tables to serve these images when a friend of his view his feed : feeds > activites > activity_image > images

activity_image

id
activity_id
image_id

activities

id
user_id
activity (text)
activity_type_id (text, photo or something else) (foreign key from activity_type table)
created_at
(I removed other columns since they are pretty irrelevant)

feeds

id
subscriber_id (foreign key from users table)
activity_id (foreign key from activities table)
created_at

I was wondering, would it be a good idea to get rid of activity_image table altogether and keep image_id's (even image names, but then I would have violated normalization) in the activities table's image column in an array?

That way I will deal with three tables (feeds > activities > images) tops rather than 4, (2 if I store image paths in the activities table) so that way performance will be better.

What's your opinion on this? What should I do?

1

I would prefer to maintain the activity_image table to support more than a single image per activity. Assume, you are storing the actual images in a separate file server and not storing as BLOB in the table itself.

I don't see what performance issues you are facing? Maybe, a bit of elaboration would be better for the sake of clarity.

I would prefer to store the image path along with other image related details in activity_image table itself, mostly the image path value would be constant unless you decide to move away the images to other folder location.

How huge the tables are? How the queries are written? Do you have proper indexing strategy? Is the query columns are hitting the index precisely? What datatypes are used for indexed columns? Is paging concept implemented on your application? At one search how large the resultset is? Are you restricting user's search and load more when the user scroll (assume web/mobile app)?

  • I store only image paths. The thing is, while creating the feed for a specific user, I want it to be as efficient as possible. If there are one or more images in a post, I query lots of tables : feeds > activities > activity_image > image seems too much to me, but I'm no SQL expert. It's normalized, so I deal with more tables. I wonder whether it is possible to get more performance if I remove the activity_image table and store images in activity table's image column as JSON object so I'll deal with 3 tables, top. – salep Oct 15 '15 at 15:49
  • There's no pagination, I use load more when a user hit a specific number of posts. Right now there's no performance issue but I don't want to over-engineer my database. My database is extremely small atm, but it'll get big soon since I use fan-out model for feeds. – salep Oct 15 '15 at 15:50
  • Querying multiple tables are fine that's what the whole concept is really about, but ensure they are tightly linked with FK and make sure your queries are hitting indexes precisely! In the above mentioned scenario, I don't see the issue at all. – Coder Absolute Oct 16 '15 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.