Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> A complex relationship

A complex relationship

From: Calvin Tai <calvin_tai2000_at_yahoo.com.hk>
Date: 15 May 2002 07:46:28 -0700
Message-ID: <e6780596.0205150646.4386c6df@posting.google.com>


Hi all,

    I got three entities named 'Profile', 'ProfileGroup' and
'AdCampaignRun' (an advertising campaign).

    There is a many-to-many relationship between 'Profile' >-<
'ProfileGroup'. That is, a 'Profile' can belongs to many
'ProfileGroup' and a 'ProfileGroup' can have many different 'Profile'.
So i added another relation as 'MapProfileGroup'. So there tables & fields are:
Profile



pid, name

ProfileGroup



gid, name

MapProfileGroup



pid,gid

    But i stuck in a problem when designing the entity
'AdCampaignRun'. The advertiser can put the advertisement in a single
profile only. So that it appears in that profile. But he/she can also put the advertisement in a group of profile (ProfileGroup). So that the advertisement will be appears on that group of profiles.

    Therefore AdCampaignRun may have a relation directly with a
'Profile' OR it may have a relation with a 'ProfileGroup' (so as it
have a transitive relation with a group of 'Profile').

    I initially added two table as 'RunGroup' and 'RunProfile', they are as following:
AdCampaignRun



rid,name

RunGroup



rid,gid

RunProfile



rid,pid

    But after i use a SQl statement to join all of them together, it gives me many unwanted rows. I don't know if my design on the tables have problems or i don't know how to write a proper SQL statement to join them all together.

    I would be grateful if anyone could help me as i stuck in this problem for two days.....

Best regards,
Calvin Received on Wed May 15 2002 - 09:46:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US