Product Catalog Schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Product Catalog Schema

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-03-21 : 16:31:29

I have a fairly complex product catalog to implement. I have 2 solutions, each with their drawbacks. I'm curious if anyone has a better solution.

Basically, i need to keep a list of products per vendor, per language/culture region, and per some custom data segment (let's ignore this last one for now as it makes the head hurt). So each vendor can have a product list for each region that the website is localized to.

So our schema might look like the following:

Solution #1 (Tables)
Product(ProductId, Name, etc)
Vendor(VendorId, Name, etc)
Region(RegionId, LanguageCultureCode, etc)
ProductVendor(ProductId, VendorId)
ProductRegion(ProductId, RegionId)

This solution will give me the ability to list products per region and per vendor. But this solution is incomplete. I need to have each vendor able to define products for each region.

Therefore Solution #2 is complete:
Product(ProductId, Name, etc)
Vendor(VendorId, Name, etc)
Region(RegionId, LanguageCultureCode, etc)
Catalog(ProductId, VendorId, RegionId)

However, this list will get exponentially huge. As I hinted at earlier, I am forced to complicate the issue further by adding the ability for a custom segment. The actual structure will look something like this:

Product(ProductId, Name, etc)
Vendor(VendorId, Name, etc)
Region(RegionId, LanguageCultureCode, etc)
Segment(SegmentId, Name, etc)
Catalog(ProductId, VendorId, RegionId, SegmentId)

Basically think of a segment just as another way to divvy up the catalog. It's a classification system of the products. like Product Line A, B, C etc... So, this list can get pretty big. Say we have the following:

1000 Products x 10 Vendors x 50 Regions x 5 Segments = 2,500,000 rows

So I have 2 questions.
1. With proper web caching, will querying 2 million+ records be feasible? What if we have 5000 products? this gets huge.
2. is there a better way of organizing this schema?

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-03-22 : 12:31:07

I think I may have found a solution to this problem. The most common scenario is that everyone will have the same product offering. So I've implemented somthing that will work basically like dumbed down Outlook rules, as they're simply include and exclude rules. This solution enables me to store minimal data. The challenging portion is writing the administration piece and correctly matching up rules.

The schema looks like this:
Product (ProductId, Name, etc) - int, varchar, etc
ProductRule (ProductRuleId, ProductId) - int, int, bit
Rule (Include, DiscriminatorId, ProductRuleId) - bit, int, int
Discriminator (DiscriminatorId, DiscriminatorTypeId, DiscriminatorValue) - int, int, varchar
DiscriminatorType (DiscriminatorTypeId, DiscriminatorTypeValue) - int, varchar

so this lets me create rules and group multiple instances of them for a given product. here's some sample data:

Product
1 (ID), 'Donut', etc
2, 'Coffee', etc

DiscriminatorType
1 (ID), 'Vendor'
2, 'Region'

Discriminator
1 (ID), 2, 'North America'
2, 1, 'Dunkin Donuts'
3, 1, 'Krispy Kreme'
4, 1, 'Al's Donut Hole'

ProductRule
1 (ID), 1
2, 1
3, 2

Rule
true, 1, 1 (this evaluates to "include North America for Donuts, exclude all other regions")
false, 3, 1 (this evaluates to "exclude Krispy Kreme for Donuts, include all other vendors")
true, 4, 2 (this evaluates to "include Al's Donut Hole for Donuts, exclude all other vendors")
false, 2, 3 (this evaluates to "exclude Dunkin Donuts for Coffee, include all other vendors")

The first 2 rules are grouped since they reference the same ProductRuleId. Together, they evaluate to: "Donuts will be sold only in North America for all vendors except Krispy Kreme." The 3rd rule evaluates to "Al's Donut Hole will sell donuts everywhere." Therefore the first 3 rules together indicate that "Donuts will be sold in North America for all vendors except Krispy Kreme and Al's Donut Hole will sell Donuts everywhere."

Finally, if I want to just add a product to everyone, it's added to the Product table. No rules are needed. So that's my highly complex product catalog. Done.

Kristen
Test

22859 Posts

Posted - 2007-03-22 : 13:06:20

We have a Product table, that includes prices and stuff.

Then there is a Price Table. 99% of folk get the core price. So IF we can find a price in the Price Table that matches the Customer Price Key we show that, otherwise we just take the price out of the Product Table. (I'm over simplifying a bit, but I expect you get the idea)

An alternative would be to have a Price List called "MASTER", and then any other exception prices. The Customer has a Price Key column, which joins to the Price table, and that will be set to "MASTER" unless someone has provided an override Price Key.

In practice where a customer has an Override Price Key, then that Price List usually only holds prices where they are different to the MASTER list - so we revert back to the MASTER list if we can't find a specific Product in the Price Table (using the Customer's Price Key).

This prevents having "exponentially huge" numbers of records, but it also fits well with how our clients work - they negotiate a few products with a customer, and just put those in the price list (or they set up a price list of products commonly bought by a particular type of Trade Customer; or they set up a SALE price list that only has a few products in it - buying anything else is "not in the sale")

Just thinking out loud!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources