Pattern: Master/Custom Objects

Lets say you have an e-commerce system which has certain master products available to a number of separate stores. Some of the master products MUST be carried by all stores, some CAN be carried by all stores, and stores also have the option to add their own custom products (the generalized solution also supports the special cases where all master products are required, no master products are required and where no custom products are allowed). The easiest solution is to have three tables: Product_Master, Product_Custom and ProjecttoProduct. Lets pretend products just have a title, price and ID, the tables would look like:

Product_Master
ProductID int
Title nvarchar(100)
Price float
Essential bit

Product_Custom
ProductID int
Title nvarchar(100)
Price float
ProjectID int

ProjecttoProduct
ProjectID int
ProductID int

You then create a fairly simple UNION view that takes a given project ID and unions all of the essential master products, all of the non-essential master products that have been joined to that project ID and all of the records in the Product_Custom table that are associated to that Project ID for a view of all of the products for that project.

The interesting part then is to consider how you might want to provide administrative capabilities for this MasterCustom object. You want to provide one straightforward admin for the master products so a superadmin can manage the master list. But you also need to provide an interface to allow for a project admin to see all of their products, to select which optional products to include and to manage their custom products specific to their project (list, add, edit and delete).

It seems to me that depending on the use case, you might need to provide a simple admin to list/add/edit/delete custom products along with a separate admin to associate optional products to a given project, but you might also want an integrated view that shows all of the essential products, allows you to associate or disassociate optional products AND allows you to add/edit/delete custom products from the same list screen which would list all master and project specific custom products.

So, we need to support the following core methods:

• getProjectProducts() - Gets all of the products for a given project - master essential, joined master optional and custom for that project.
• getProjectandMasterProducts() - Gets all of the master products (whether associated to the project or not) and all of the custom products for the project.
• getMasterProducts() - Gets all of the master products (essential and optional)
• getOptionalProducts() - Gets optional products for associating to a project
• getCustomProducts() - Gets custom products - e.g. for list/add/edit/delete admin for custom products
• associateProducts() - Associates custom products to a project
• addMasterProduct() - Add a master product
• editMasterProduct() - Edit a master product
• deleteMasterProduct() - Delete a master product
• addCustomProduct() - Add a custom product
• editCustomProduct() - Edit a custom product
• deleteCustomProduct() - Delete a custom product

Plus of course, you'll need all of the usual filtering and get by category and other such methods. So, what is the most elegant way to conceive of and access the functionality required?

One approach would be to have three objects - MasterProduct, CustomProduct and ProjectProduct. MasterProduct would allow for list, add, edit and delete by the super admin on the master product list and would also provide a list of essential, optional or all master products for use throughout the system. CustomProduct would provide list, add, edit and delete of project specific custom products. ProjectProduct would be a view only list that would allow for listing, searching and filtering the appropriate records for any given project. All three objects would extend a core Product object (which would actually be an abstract class) so the only metadata you'd have to customize between them would be the table name and the one extra property: essential for MasterProduct, ProjectID for CustomProduct and probably a "type" field - enum{Essential, Optional, Custom} that'd be generated for the ProjectProduct object. The only problem would be where to put the list of all master products plus the custom products for a given project, and I'd treat that as an optional method of ProjectProduct which would be capable of getting either all selected project products or all possible project products.

Any thoughts on other approaches to the problem?