Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 27th, 2007, 04:34 AM
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




Similar Threads
Thread Thread Starter Forum Replies Last Post
performance issue keyvanjan Classic ASP Basics 0 May 23rd, 2006 10:57 AM
Generic Image structure working design-time, how? jacob C# 3 March 6th, 2006 05:28 PM
issue of performance alyeng2000 SQL Server 2000 6 August 20th, 2004 01:17 PM
Performance issue deyakhatib SQL Server 2000 2 June 21st, 2004 10:47 PM
Java Design issue with UML and Design Patterns the_logical_way Apache Tomcat 0 May 31st, 2004 04:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.