CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
rmagnum2002
rmagnum2002

Reputation: 11421

Multiple database records in a table vs database queries? What is the best for performance?

App running on: ruby 2.0, rails 4 and postgresql

1.The multiple tables story - How it works now:

A project has many users, as members.

Also project has many posts, when a post is created a notification is created for each project user.

Let's say if Project A has 100 users, we'll have 100 notifications in database, this will load the database with a lot of duplicates.

But a user can delete its own notification, can view it, we can update his notification with user specific data. And we'll use a rake task to remove the notifications that are older then a specific time interval.

2. The multiple db queries - What we want to do:

There is an idea of creating only one notification for an activity and use many to many relation with a table called notifications_users where we'll keep information about a notification if it was read by a current user, if this user removed this notification from his notifications tab, etc..

The downside of this I think it will be multiple db queries as when we'll need to find something about a notification and user we'll have to look up for the notification_users table to get the information needed.

Also, by building the relation this way it will be harder to clean up the database from old notifications as we will not know if this notification was read or not by some user.

Thank you.

Upvotes: 2

Views: 194

Answers (2)

Vlad GURDIGA
Vlad GURDIGA

Reputation: 1334

The option (1.) seems pretty reasonable and if you can keep the notification model thin — like user_id, activity_id, timestamps, and maybe a couple more flags, then wouldn’t expect unreasonable performance penalties, since it looks like a pretty usual relational model and I’d expect the database to easily handle it.

If you keep you notification expiration tight, this means that the notification should not grow, or if it does, for example when user just abandoned the account, I would look for specific solutions for the issues, as the appear.

At Assembla.com we do notification with email and this is working pretty well for us, I’d say. So, maybe at some point, you may want to consider this option too.

It seems to me that the (2.) just doesn’t fulfil the business needs, and if you’re not considering them than it’s probably not worth considering this option either.

Upvotes: 1

Ashish_Destiny_lies_in_coding
Ashish_Destiny_lies_in_coding

Reputation: 1

Before analyzing scenarios given in your question I would like to request you to clear one point which is a bit confusing in your question. The statements given below are confusing.

1) Also project has many posts, when a post is created a notification is created for each project user.

2) Let's say if Project A has 100 users, we'll have 100 notifications in database, this will load the database with a lot of duplicates.

Statement no. 1 describes that when a post is created; a notification is sent for each user. So suppose there are 100 users and 100 posts then the table which has notifications data will have 10000 rows (100*100).

Statement no. 2 describes that if there are 100 users then there would be 100 notifications in the database that means the notification table will have 100 rows.

Of the points given above which one should I consider.

Upvotes: 0

Related Questions