4

Let's say we've got 3 entities here, User, Tag, Track and a single associative entity called Tagged. ERD

I need to count a tag frequency of a track and the common way is to count the record of tagged, but what if I just store the tag's frequency as an integer attribute on Tag entity, and maintain the value every time there are changed on Tagged's record, will it be a problem ?

Edit:
The DBMS I'm using is MySQL 5.6.
About the dataset, there are 947 tracks, 7962 tags, 800~ users, and 366617 taggeds

Why I need to query it fast ? Because, I'm trying to calculate similarity value between each track and in that process I need to obtain the frequency of each tag of the tracks I'm calculating and I think the slow query is where it should obtain the frequency from counting the record on Tagged, that's why I think maybe I should just directly store the frequency instead of count through those records.

Improve this question
5
  • 6
    Its called de-normalizing, and its fine, as long as you know why you are doing it.
    – GrandmasterB
    Aug 11, 2018 at 4:33
  • 1
    @GrandmasterB This is not de-normalizing. The relational normal forms don't address redundancies based on aggregate functions.
    – reaanb
    Aug 11, 2018 at 5:46
  • Could you possibly explain why you think it is a good idea to store (and maintain) the tag count and what DB technology you use ? To further help us giving objective advise, could you also indicate: how many users run the report and how often, if there are other reasons than the report for getting the tag count, how many users that created tagged entries and how often, as well as volumes of your dataset ?
    – Christophe
    Aug 11, 2018 at 8:31
  • also, what is the slow query you are trying to avoid running?
    – Ewan
    Aug 11, 2018 at 9:20
  • I've added the explanation on the question
    – Edwin Harly
    Aug 12, 2018 at 3:29

2 Answers 2

Reset to default
3

You should only record derivable information when absolutely necessary, after it's been observed that a query or view on the Tagged table isn't practical.

However, once the decision has been made to store this info, recording derivable information is fine, provided you maintain consistency. Consider implementing this as a trigger on the Tagged table to ensure consistency at the database level, so that direct-to-database changes (e.g. by a DBA) can't break your data.

BTW, your associative entity should just be a ternary relationship. Associative entities in the ER model are only required when a relationship needs to be the subject of another relationship.

Improve this answer
9
  • While there are certainly some cases where such denormalizing could be justified, it is not the case here. First, if there are many inserts and updates in 'tagged', your triggers would create a concurrency issue (a bottleneck in principle). Second, your triggers create a vendor lock-in that will make change of db difficult. Third, the triggers would absorb a part of the application logic, potentially breaking a clean architecture. Fourth, what will you recommend if the next report wants also stats on tags per user ?
    – Christophe
    Aug 11, 2018 at 8:11
  • Finally, I'd add that "premature optimization is the root of all evil": we don't even know if there's a real performance issue: the RDBMS engine should do very well with a GROUP BY clause, provided there are the right indexes.
    – Christophe
    Aug 11, 2018 at 8:14
  • 1
    @Christophe I agree that recording derivable information should only be done when absolutely necessary, after it's been observed that a query or view on the Tagged table isn't practical. However, once the decision has been made to store this info, a trigger ensures data integrity at the DB level and does it in a single transaction, instead of multiple calls from the app. Data integrity isn't "application logic", it's part of the data model and what database management systems were built to manage.
    – reaanb
    Aug 11, 2018 at 8:31
  • @Christophe Finally, using only the least common denominator functionality so that you can switch DBMSs easily will always leave you with the need to switch since you won't ever exploit the value of any of them.
    – reaanb
    Aug 11, 2018 at 8:31
  • I thank you for having taken my main concern in consideration in your edit! I hence removed my downvote. On the trigger question, I understand and appreciate your arguments even if I do not necessarily share them. But given the little we know over Edwin's context it will be difficult to continue the discussion on an objective ground ;-)
    – Christophe
    Aug 11, 2018 at 8:53
4

I would not recommend doing this.

It complicates the database with what is essentially either a cached value or a report.

A cached value can be maintained better outside of the database

A report should be done on a reporting server.

I would especially not recommend using triggers to keep the value up to date. This could really slow your db down.

Your application could use any number of different aggregates and calculations, will you be adding each as an extra column on a table?

Run the aggregate query and cache the result in the application (invalidating when required), or run a report each day and use that.

On the whole adding the extra column is a very database-centric solution. If you have an application or API layer above the database you will have multiple options available to you to avoid this problem.

For example, if I always retrieve a Track and its Tags together to populate an aggregate object, then there is no need to calculate the number of tags on the database.

If I want to show the number of Tags per day, per week etc then a single col of on the track table no longer helps me. I can query Track + today's Tags from the transaction DB and the Tag frequency over time for that Track from the reporting Cube which will be optimised for that kind of query on historical data.

Even if I want an on demand up to the second report of the most frequently Tagged tracks over all time, for which a single, updated col would be useful. I would still not add the column. Instead I would have the application raise an event when a tag was added and deal with this in a completely separate solution, possibly statsd or a reporting cube that I would write directly to.

Improve this answer
13
  • 1
    While denormalizing can sometimes be justified, I fully agree with your analysis for this case: here it's a very bad idea ! Not only will there be concurrency issues with updates that change the relations, but furthermore, what will happen if the next reports want statistics on tags per user and so on ? For this kind of needs the rdbms engines's GROUP BY will do fine, provided there's the good indexes.
    – Christophe
    Aug 11, 2018 at 8:00
  • A whole reporting server is overkill for a few calculated values, there's a minimum critical mass required to make a separate server cost-effective. Below that threshold, it can be viable and valuable to manage some calculated data on the same server. As for "could really slow your db down", it could also really speed your db up, as I've seen with trigger-maintained closure tables for hierarchical data.
    – reaanb
    Aug 11, 2018 at 8:41
  • @Christophe The question doesn't involve denormalization. What normal forms are the tables in before and after the addition of the derived column?
    – reaanb
    Aug 11, 2018 at 8:43
  • @reaanb interesting debate :-) The definition of the normal form is based on relational algebra and functional dependencies (see Boyce-Codd normal form), not on tables and columns. Functional dependency means exactly what you call derivability. So introducing data that could be derived breaks the normal form, which is by definition denormalization.
    – Christophe
    Aug 11, 2018 at 9:14
  • 1
    @reaanb sure, but you specifically said changes by the DBA. Also, it seems to me that that trigger could get pretty complicated with the Track and User options+ transactions and locking to deal with concurrent changes. I'm sure it can be done, but you are loading up the DB with logic to perform
    – Ewan
    Aug 11, 2018 at 12:45

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.