database design issue - generic vs performance
We have an entitiy say Task that is related to multiple other entities like opportunity, order, service request, contact, order etc (about 15 such entities). We have thought of two ways that we can define relationship between task and these entities:-
1. Table for Task - TASK
Intersection table to maintain relation b/w task and other
entities called - TASK_OBJECTS.
TASK_OBJECTS table will store the OBJECT ID and OBJECT CODE and
the corresponding Task id that is associated with this object.
Con - when we need to query up details for an opportunity
associated to a task id. we have to do a join on TASK_OBJECTS and
OPPORTUNITY table. This leads to performance issues when we would
be having millions of records in the TASK_OBJECTS table.
Pros - This makes the database design generic. If another
entitity gets added to the business that can create Tasks then we
simply add an Object code in the master table and start adding
records in the TASK_OBJECTS table.
2. The other way of doing the same thing is to have separate columns
in TASK table assigned for these entities. These column would
store teh respective entity id associated with the task. For eg.
we can have OPTY_ID column in TASK table which stores the id of
the opportunity for which this task is created. So while querying
the opportunity details there is no need for a join and its
faster. Only in cases where we have a many to many relation that
we can have intersection tables for those specific entities.
Cons - This is not a generic data model. We freeze right
at the begining the number of entities for which we would be
creating a task as we define 1 col each for an entitiy. In case
we need to add another entity we would have to make schema
Pros - This reduces the number of joins and hence querying
the database would be much faster esp. when the TASK table is
going to have millions n millions of records.
Please let me know which of these models is the best one to use or if you have any other suggestions on how to structure the tables for this scenario.
Following are the Object tables :-
Opportunity Id Description
201 Test opty
Account Id Description
301 Test acc
SR Id Description
401 Test SR
1) we have task table (TASK_TBL)
Task Id Description
2) We have the task and object mapping table (OBJECT_TBL)
Task Id Source Object Code Source Object Id
100 OPT 201
100 ACC 301
100 SR 401
Here to get the opportunity details for task Id 100, we wld have to perform a join as follows :-
select * from OBJECT_TBL ob, OPT_TBL op
where ob.task_id = 100
and ob.source_object_id = opt_tbl.opportunity_id
1) Here there will only be task table (TASK_TBL)
TaskId Descrip OpportunityId AccountId SRId
100 Abc 201 301 401
Here to get opportunity details for task Id 100 the query is straightforward:-
select * from OPT_TBL
where OPT_TBL.opportunity_id = 201