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_at_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 - 16:46:28 CEST