1

What is the most optimal way to do this?

  • In TV Show page I need to get all progress from all episodes in that TV Show
  • In Homepage I need to get the continue_watching
  • In Episode I need to get progress of the episode.
  • Some TV Shows have more than 100 episodes

Ex:

USER_ID | SHOW_ID | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | 12           | ---

USER_ID | EPISODE_ID | PROGRESS
10      | 10         | 15
10      | 11         | 20
10      | 12         | 95

OR

USER_ID | SHOW_ID | PROGRESS                     | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | {"10":15, "11":20, "12": 95} | 12           | ---

In PosgreSQL I can get the individual progress with:

SELECT progress->'10' as progress...

I think that the best method is the first but it will create a lot of rows and this could make DB slower?

Improve this question
5
  • 1
    The rule of thumb is: start with a clean, normalized data model (your first solution). The second solution also has the significant disadvantage that you can't ensure that progress only stores existing episode IDs or that the value is actually a number e.g. you can't easily prevent: {"the new one": "half way through"}. Only when this proves to give you (performance) problems, try to come up with something better. The big question typically is: how do you want to access the data? e.g. do you need to aggregate (sum, average, ...) progress over different episodes and different users?
    – a_horse_with_no_name
    Apr 2, 2020 at 6:05
  • The problem is when i need to bring all episodes progress from a certain show. I need to bring it with a large SQL query: WHERE id IN (...) The other solution is to add SHOW_ID in Episode Progress table.
    – Angel Vega
    Apr 2, 2020 at 10:15
  • The latter, I would say. The combination of (show_id, episode_id) is most likely (or should be) a primary key in a table named episode, so your progress table should actually contain show_id, episode_id to correctly reference an episode.
    – a_horse_with_no_name
    Apr 2, 2020 at 10:21
  • Im not really good indexing, do you think that (user_id, show_id, episode_id) should be the primary key, or should (user_id, episode_id) be the primary and (user_id, show_id) an additional index?
    – Angel Vega
    Apr 2, 2020 at 10:29
  • The other option is to not add show_id and bring them directly with a left join.
    – Angel Vega
    Apr 2, 2020 at 11:11

1 Answer 1

Reset to default
1

I think that the best method is the first ...

Simply put, it is.

... but it will create a lot of rows ...

Yes, it will.
Don't worry.

... and this could make DB slower?

Relational databases are really, really good at running around large numbers of rows in many tables and putting things together. With proper indexing, this structure will handle large numbers of rows with no problem at all and is the most flexible, so will support many different types of query well.

Improve this answer
2
  • Should i index episode_id and user_id for foreign keys or with user_id, episode_id is enough?
    – Angel Vega
    Apr 2, 2020 at 23:45
  • Both! Your DBMS can choose to execute your SQL in any way it feels is most efficient, not necessarily in the order that you write it. So give it the option to join /to/ your foreign key fields from their primary equivalents (even if it feels that "wrong way round" to you and me) by indexing each foreign key field on its own. On your second table, I would suggest that (user_id, episode_id) is actually the [composite] Primary Key, so that definitely needs indexing.
    – Phill W.
    Apr 3, 2020 at 13:28

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.