Currently we are storing the products in 3 different table namely courses, certification and live_events. Pulling information from these tables to the catalogue page is difficult and it involves scanning of three separate tables. More over, implementing pagination would be difficult. To overcome this problem, we can add a new table named products which would store all the common fields of course, certification and live_events (the fields which are generic to identify the item as a product)
This is how the DB design and the corresponding model design should be:
No major code change. The CRUD operations of the Course, Certification and LiveEvents will happen as it is now. When ever a Course, Certification or a LiveEvent gets updated, the common attributes are updated in the product table so that the solr index gets updated.
The catalog search display will be based on the data stored in the product table.
As mentioned earlier, the main purpose of introducing the product table was to improve the performance of the search functionality. When a product gets created/udpated the solr index for the same record is updated. The index update happens in an Asynchronous manner using the Workling-Starling setup. Since it is Asynchronous, the immediate data updation might not get reflected in the search results. The results would only get updated after a period of time (once the index gets updated)