Design: storing (external?) query criteria in table to be summarized by main query?? (1 Viewer)

[Abiart]

Hi Everyone,

I need an intelligent way to store complex location criteria for e-marketing campaigns, which will allow consistent and useful reporting of which messages were sent where.

I’m designing a DB to record and report statistics for e-marketing campaigns. It does not contain mailing list or message data (e.g. lists of names, message content etc), but rather data about the mailing: format (e.g. newsletter, announcement, product feature etc); category (product area); geographical mailing list criteria; when it was sent; how many messages sent; how many opened etc. One of the main tables is as follows:

tblListRentals
(PK)ListRentalID
CategoryID
MailingPublicationID
MailingRegionID
MessageTypeID
Date
NoEmailsSent
OpenRate
SingleClickthroughRate
TotalClickthroughRate

The part I’m struggling to understand is how to store the geographical criteria. The table tblMailingRegion currently consists of just MailingRegionID and MailingRegionDescription, but due to the nature of our mailings, a different set of criteria may potentially be required per mailing, which will need normalising to result in useful reporting functionality.

To this end, I have created these 3 tables, as yet unrelated to other tables:

tblCountries
CountryID
ContinentCode
CountryCode
CountryName
RegionID

tblContinents
ContinentCode
ContinentName

tblRegions
RegionID
RegionName

The reason for this structure is that we divide the globe into 3 regions, loosely based on time zones; thus a single continent can be in more than one Region, and each Region can also contain multiple continents.

I need some way of specifying that (for example) ListRentalID W was sent to (RegionID X excluding ContinentCode Y), AND CountryID Z, or any combination of these (with different combinations of bracketing), in a fashion where it is intelligible that the same countries can be included in multiple MailingRegion criteria.

The only way I can think to do this is to have a one-to-many relationship between tblListRentals and tblMailingRegions (where one ListRentalID relates to multiple MailingRegionIDs), and add the fields:

IncludeCountry;
ExcludeCountry;
IncludeContinent;
ExcludeContinent;
IncludeRegion;
ExcludeRegion.

I just doubt this is the best solution, as it seem rather untidy, and I will have to write complex code for a query to understand the difference between fieldnames “IncludeX” and “ExcludeX”. Any comments or suggestions would be gratefully received!

Many thanks for your time.

Abby

 

[RichMorrison]

Abby,

Quick response with no deep thought.

1) When designing complex criteria I think you should give up the exculde/include idea. Just stick to include. Relational databases work on the principle of positive joins and don't really recognize a 'not in' relationship.

2) Make a permanent structure table that contains Region, Continent, Country. Fill it in with all possible combinations. Update as needed.

3) To let the users make requests, design cascading list or combo boxes and let the users drill down to the level they need. Do this with the structure table in step 2). For your example
<<
ListRentalID W was sent to (RegionID X excluding ContinentCode Y), AND CountryID Z, or any combination of these (with different combinations of bracketing), in a fashion where it is intelligible that the same countries can be included in multiple MailingRegion criteria.
>>
let them pick Region X, show a list of all the Continents in Region X, let them pick all except Y. Then let them pick Country Z. Based on what they pick, join to the structure table and select all Continents or Countries or whatever.

4) If you want to get fancy, store what they pick in a criteria table. Let them save their selections with a descriptive tag and let them recall their saved selections.

This is how I would approach this project.

Good Luck,
RichM

 

[gemma-the-husky]

at the point when the queries are run to genearate the mailing list there must be a sql string generated

why not save this entire string (or at least the where clause) within your criteria table, with who did it, which terminal etc.

 

[Abiart]

Thanks for your suggestions. I'm going to have a go at the structure table method. Unfortunately I can't simplify it to the point of saving SQL statements, as the mailing database that holds the list of email addresses etc is held externally, and we can only access it via a web client .