Interesting Design Challenge

Recently I have faced a interesting design challenge working over an application (lets call it MyApplication).

I like to discuss the challenges and few of the approaches I have thoughts of. I like to hear your ideas.

Functional Requirements

It is a module in a bigger application. This module allows categorization of customers on the basis of attributes attached to them. A customer has following attributes:

1. Role: R1, R2 & R3 (one or multiple)
2. Age Group: 0-25, 25-50, 50 on ward (One and only one)
3. Status: Active, In-Active, Deleted (one and only one)
4. Gender: Male, Female, Other (one and only one)
5. Address: Country, State, City, Pin code (one unique combination)

Now basis of these attributes customer will be categorized (category & Sub-category) and then population criteria will be developed to populate those sub-categories.

There is only one level of categorization is allowed. It means a category can have only one level of sub categorization. So Cat1 has sub-categories SubCat11, SubCat12, SubCat13. Under SubCat11, SubCat12, SubCat13 only customers reside as per the population criteria. The development of population criteria is user driven. To develop a population criteria following user interface should be used.




IMAGE-1

The interpretation of selected items is (as depicted in IMAGE-1):

(Role: R1 OR R2) AND (Age Group: 0-25 OR 50-onwards) AND (Status: Active OR Inactive) AND (Gender: Male) AND (Country: India OR USA) AND (State: Karnataka OR Bihar OR California OR Texas) AND (City: Banglore OR San Jose OR Austin) AND (PIN/ZIP: 560030 OR 560070 OR 73344)

Apart from Population Criterion Creation screen there will be screens which will do the following tasks:

1. Update a population criterion
2. Using population criterion, sub categorize customers.
3. Update sub categorized customers as per the updated population criterion.

Update a Population Criterion screen will be similar to Population Criterion Creation screen. While appearing on computer terminal, Population Criterion Creation screen will show what are the different items are pre selected which user can change as per the requirement.




IMAGE-2

Populate a sub category screen will depict a selected population criterion and category/sub category. This screen will allow user to populate customers in a sub category as per the selected population criterion.


IMAGE-3

Re-populate a sub category screen will depict the updated population criterion and category/sub category. This screen will allow user to update population of customer under selected population criterion and category/sub category.



IMAGE-4

To support above screens there will screen which will allow formation (as well as updation) of association of category/sub category and population criterion. Call this screen as Association: Population Criterion & Category/Subcategory



IMAGE-5

The existing RDBMS schema is:



IMAGE-6


We can add tables/views as desired but do not change any existing tables/views.

Non-Functional Requirements

1. The selection items (Current: Role, Age group, Status, Gender, Address; Expected: Marital status, Family size, etc.) count is expected to increase as more static attributes will be captured over time period.
2. To make population criterion using dynamic attributes (e.g. shopping behavior, browsing behavior, etc.) Business intelligence and/or data ware housing will be used. But even in these cases population and re-population should remain with in control of MyApplication.
3. The population criterion creation and updation should be fast enough to qualify for Gold (confirmation of success or failure should be displayed to used with in 800 ms) service level.
4. The population and repopulation of customers in a sub category should qualify for Bronze (confirmation of success or failure within 2000 ms or information of “working asynchronously” within 300 ms) service level.
5. Association: Population Criterion & Category/Subcategory screen should qualify for Platinum (confirmation of success or failure within 200 ms) service level.
6. SQL query should be SQL 92 compliant. Usage of Store Procedure, Trigger and Functions is prohibited.


So you got the problem, what are the possible solutions with their positives and negatives.