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
id activity_id image_id
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)
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?