I'm trying to put together a simple system for a user to generate a list of users to whom surveys will be sent. The list generation may depend on various constraints. For example, "we only want people from the U.S. and Canada" or "we only want people who have a level 2 or level 3 membership."
This part is pretty easy and I've set up the tables to capture the selection criteria. One additional criteria though, is that they may want to get a certain percentage of each item. For example, "give me 70% U.S. users and 30% Canada users." Again, I think that I can do this without too much trouble. They will give the number of users that they want, so I can just multiple by the percentages then make sure that the numbers still add up after rounding and I'm good to go.
Thinking to the future though, what if they wanted certain percentage breakdowns by two sets of criteria. For example, "Give me 70% U.S., 30% Canada and at the same time, 50% level 2 users and 50% level 3 users." Since it's not a current requirement I'm not planning to give myself a headache over it, but if anyone has a reasonably simple algorithm (or SQL code) for accomplishing something like this then I'd be happy to see it.
Although I would prefer a DB-agnostic solution, I'm on MS SQL 2005, so solutions specific to that RDBMS are fine too.
The table structure which I'm currently using is similar to this:
CREATE TABLE Selection_Templates
(
template_code VARCHAR(20) NOT NULL,
template_name VARCHAR(100) NOT NULL,
CONSTRAINT PK_Selection_Templates PRIMARY KEY CLUSTERED (template_code),
CONSTRAINT UI_Selection_Templates UNIQUE (template_name)
)
GO
CREATE TABLE Selection_Template_Countries
(
template_code VARCHAR(20) NOT NULL,
country_code CHAR(3) NOT NULL,
selection_percentage DECIMAL(2, 2) NULL,
CONSTRAINT PK_Selection_Template_Countries PRIMARY KEY CLUSTERED (template_code, country_code),
CONSTRAINT CK_Selection_Template_Countries_selection_percentage CHECK (selection_percentage > 0),
CONSTRAINT FK_Selection_Template_Countries_Selection_Template FOREIGN KEY (template_code) REFERENCES Selection_Templates (template_code)
)
GO
CREATE TABLE Selection_Template_User_Levels
(
template_code VARCHAR(20) NOT NULL,
user_level SMALLINT NOT NULL,
selection_percentage DECIMAL(2, 2) NULL,
CONSTRAINT PK_Selection_Template_User_Levels PRIMARY KEY CLUSTERED (template_code, user_level),
CONSTRAINT CK_Selection_Template_User_Levels_selection_percentage CHECK (selection_percentage > 0),
CONSTRAINT FK_Selection_Template_User_Levels_Selection_Template FOREIGN KEY (template_code) REFERENCES Selection_Templates (template_code)
)