Re: A complex relationship

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 16 May 2002 15:03:57 -0700
Message-ID: <c0d87ec0.0205161403.3c1d9dc6_at_posting.google.com>

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

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

CREATE TABLE Profiles
(profile_nbr INTEGER NOT NULL PRIMARY KEY,  profile_name VARCHAR (25) NOT NULL);

CREATE TABLE ProfileGroups
(profilegrp_nbr INTEGER NOT NULL PRIMARY KEY,  profilegrp_name VARCHAR (25) NOT NULL);

CREATE TABLE ProfileGroups
(profilegrp_nbr INTEGER NOT NULL

             REFERENCES ProfileGroups(profilegrp_nbr)
             ON UPDATE CASCADE
             ON DELETE CASCADE,
 profile_nbr INTEGER NOT NULL
             REFERENCES Profiles(profile_nbr)
             ON UPDATE CASCADE
             ON DELETE CASCADE,

 PRIMARY KEY (profilegrp_nbr, profile_nbr));

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

Suggestion: keep the ad campaign information at the lowest level of detail -- profiles rather than profile groups.

CREATE TABLE AdCampaigns
(advertiser VARCHAR(25) NOT NULL, -- use a code in the real database  campaign_name VARCHAR(25) NOT NULL, -- use a code in the real database
 profile_nbr INTEGER NOT NULL

             REFERENCES Profiles(profilegrp_nbr),  PRIMARY KEY (advertiser, campaign_name));

This would be handled by expanding a profile group into a list, something like this:

 INSERT INTO AdCampaigns
 SELECT :my_advertiser, :my_campaign_name, G1.profile_nbr    FROM ProfileGroups AS G1
  WHERE G1.profilegrp_nbr = :my_profile_or_grp_nbr  UNION ALL
 SELECT :my_advertiser, :my_campaign_name, :my_profile_or_grp_nbr    FROM Profiles AS P1
  WHERE P1.profile_nbr = :my_profile_or_grp_nbr;

Then you could use a relational division to get the profile groups out of the Ad campaigns. Put it in a VIEW and you are ready to go; the VIEW will adjust itself as the profile groups are changed. Received on Fri May 17 2002 - 00:03:57 CEST