View Single Post
 
Old March 27th, 2007, 04:34 AM
elysian elysian is offline
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default database design issue - generic vs performance

Hi

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
changes.
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.

EXAMPLE:

Following are the Object tables :-
1) OPT_TBL
Opportunity Id Description
201 Test opty

2) ACT_TBL
Account Id Description
301 Test acc

3) SR_TBL
SR Id Description
401 Test SR

MODEL 1:
1) we have task table (TASK_TBL)
Task Id Description
100 Abc

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

MODEL2:

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