I couldn't decide on my data model for the performance and easiness of use perspectives or if I need to consider any other thing. The application will be Spring service and it will be a relational DB.
SELECT * from MainTable mt;
left join TableA ta
left join TableB tb
left join TableC tc
where (mt.startDate <= givenStartDate and mt.endDate => givenEndDate)
AND (ta.aId IN givenAIds or tb.bId IN givenBIds OR tc.cId IN givenCIds)
VS
SELECT * from MainTable mt;
left join TableCommon tableCommon;
where (mt.startDate <= givenStartDate and mt.endDate => givenEndDate)
AND tableCommon.fkId = mt.id
AND ( (tableCommon.tableType = "A" and tableCommon.tableId IN givenAIds)
OR (tableCommon.tableType = "B" and tableCommon.tableId IN givenBIds)
OR (tableCommon.tableType = "C" and tableCommon.tableId IN givenCIds)
)
One or more filters might be added from the main table.
We will use bIds to fetch details of B from another service after we fetch the result of the query.
I have different filter scenarios for fetching data between specific time intervals.
