CODEWITHSUNDEEP
database-design
Siraj Samsudeen
Siraj Samsudeen

Reputation: 1690

Which approach is better - comment attribute in each table vs one comment table

Currently, I have a bunch of tables in my system which need to store one comment per row (not by content type or anything else - just simple one comment per row and no history tracking). Is there any advantage to creating a comment table and having a FK to it from all other tables which need comments as opposed to just directly creating a comment column in each of those tables?

Is there any advantage on the physical DB side - in terms of database performance for these 2 approaches?

Upvotes: 1

Views: 296

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

It's really impossible to comment (cough) meaningfully on performance without testing. You're the only one in a reasonable position to do that.

At the logical level, a comment about the data in a row should be stored with the row.

Some dbms will move wide columns internally to a secondary storage area. This speeds up access to rows when you don't select the comment column (fewer physical reads), but it slows down access when you do select the comment column (reads from a different part of the internal data structures). You might not notice the difference unless you're selecting many thousands of rows out of millions of rows. (But, again, testing . . .)

Upvotes: 2

Jon Skarpeteig
Jon Skarpeteig

Reputation: 4128

Having a separate table, and rely on joins, can be a performance issue. Unless the same comment goes to multiple tables, there is no reason to have this separate.

Upvotes: 2

Related Questions