I'm trying to create an availability table for existing products. I'm fetching the suitable schema to design in order to get good performance when I fetch products in a table contains millions of rows.
Given an example 'product-1' which has a product_id = 1 and available from 27/03/2018 to 31/03/2018:
product_id product_name start_date end_date
1 product-1 27-03-2018 31-03-2018
However if the product is not available in 29/03/2018 I have to divide the period to 2 intervals:
product_id product_name start_date end_date
1 product-1 27-03-2018 28-03-2018
1 product-1 30-03-2018 31-03-2018
With a table contains millions of products and if I divide the availability by intervals I will got a huge number of rows and fetching data will degrade the performance. Is there any solution to use such as range types or anything which mentions the unavailable dates.
Based on the example mentioned above, can I mention a data type like range type that take the start_date and end_date and exclude the unavailable dates?