How to re-structure the activities table

database-designrelational-theory

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?

Best Answer

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)?

Related Solutions

How to link Similar-but-distinct Models of Medical Imaging Data

Since you said that there are always 2 types of data I would do it like this

This might use the wrong terms since I don't know what exactly is available, but I think you will get the idea.

image (the data that is common to both scanes/images)
---------------------------
    image_id
    date


coordinator (the one that is inserted by hand)
---------------------
    image_id (PrimaryKey and ForeignKey)
    comment
    description
    order
    [...]

metadata (that you get programmatically)
----------------------
   image_id (PrimaryKey and ForeignKey)
   {whatever data that is}

This way, you have both images connected but have the freedom to model the details differently.

Additionally, you could move the metadata into a seperate table if the data is similar. This might be useful for later comparisson/collection/...

metadata (that you get programmatically)
----------------------
    image_id (PrimaryKey and ForeignKey)
    metadata_id


metadata_description
-------------------------
    metadata_id
    description
    name
    {whatever the metadata is}

Designing an aggregation schema

Relational DBMS is not ideally suited to this type of application. RDBMS is great for tracking and enforcing referential integrity for relations between two entities, THING A and THING B. What you want to do is to track relationships between THING A and every other thing in your database (or lots of other things, at least).

Your options, each of which include some form of compromise, include:

Sparse Foreign Key Columns: Create an activity table that has mutually exclusive foreign key columns that point to the various kinds of activity that you want to track. This is a fully 3NF way of doing things, but it still requires new columns to be added for new kinds of activities and it results in sparse columns, which people try to avoid as a rule.
Forrest of Tables: Create individual activity tables each of which track a particular type of activity. You can then create a query or view that uses UNION ALL to bring the various kinds of activity together into one place for querying. This is also very by the book with respect to normal forms, but you end up with a database cluttered with activity tables and you have to create a new table and alter a view whenever you add a new type of activity.
Data Driven Joins: Use a single activity table that captures the type of the activity and the instance of the activity in one place. You would need to have more sophisticated logic built for retrieval of the activity details, since this approach foregoes formal foreign keys. There is no declarative referential integrity and the approach smells a lot like EAV, which makes many people's skin crawl. On the other hand, you wind up with a single table, no sparse columns, and the addition of a new activity type requires no schema changes, only a tweak to one piece of code - the one that reads type of activity and knows what to do to go and retrieve the details of that activity.

You need to look at where your sensitivities are going to lie. How many types of activity will you ultimately be tracking? How often are new types of activity going to be added to the mix? Are you prepared to endure the scorn of colleagues when they see you've implemented a solution with sparse columns (or worse) EAV? When you answer these questions you'll be able to pick the approach which makes the most palatable compromises for your application.

Best Answer

Related Solutions

How to link Similar-but-distinct Models of Medical Imaging Data

Designing an aggregation schema

Related Question