0

I have 3 types of news items, articles, videos, pictures. These are stored in 3 tables.

Around 30% of the time, users will view data from a single table, ie. /videos.

The other 70% of the time, users will view a feed of all 3 together.

What would be best practice here in terms of table design?

  1. Keep tables separate and join for the feed.
  2. Denormalize the data and put everything in a posts table, with null fields for inapplicable data.
  3. Denormalize the data and put data that does not exist for all 3 tables into some sort of json meta column.

Or something else?

I have concerns that with option 1, 70% of queries will require a join.

Shadow
  • 33,525
  • 10
  • 51
  • 64
panthro
  • 22,779
  • 66
  • 183
  • 324
  • 4
    Joins are no problem. With proper indexing this is super fast even for many records in your tables. Denormlizing is normally a bad idea. Do it only if you have to and there is no other option increasing speed. – juergen d Nov 05 '20 at 11:26
  • Joins are at least 70% of what database does :) – Caius Jard Nov 05 '20 at 12:50
  • This is the classic single table inheritance vs multi table (or class) inheritance. Both are valid database design models, both have advantages and disadvantages. Choose the one that suits you the best. – Shadow Nov 05 '20 at 12:51

0 Answers0