Structuring a database where products have multiple packaging types from multiple vendors

Question

Let's say I have a Products table, and there is a "default" unit the product is sold in (EA let's say, for individually). The product is also available from some vendors as BX (box) and CT( carton), each containing a certain amount of the "default" unit (in our case let's say a box is 6 and a carton is 12).

The product has a regular List Price associated with the "default" unit, and then another list price for each of its additional selling units. The cost of the product (i.e. what we as the seller pay) differs per product per vendor, and we intend to implement a way to view a list of vendors selling the product so we can select the best price (e.g. we might go with Vendor A for the individual product, but Vendor B for the carton because they sell it to us cheaper than Vendor A does).

Business rules demand that we maintain a separate SKU for products at each level of packaging, but the rest of the information (e.g. sales copy, manufacturer, description) is identical. So given a product with the SKU ACM1234 it could have "displayable" SKUs:

ACM1234 - Individual product (default) with List Price $5.00
ACM1234BX - Box packaging (6 individual) with List Price $28.00
ACM1234CT - Carton packaging (12 individual) with List Price $50.00

and this product might be supplied by 3 vendors at various levels - some of them might not supply the carton version, but supply the box and single, while others might have all three.

In addition, the product needs to be treated as a separate entity since our primary customer is the U.S. government and we have the concept of "contracted" and "non-contracted" items; for example we might only choose to sell the box and carton versions of the product to the government, but not the single. When we receive an order then, we'll need to look up an item by it's SKU (e.g. ACM1234BX) and get back it's information - for example what we're selling it for. This can run into issues if the product database has the "regular" SKU (the single item, for example) but a government customer has purchased the box version.

Normally I would have a "master" products table that has the SKU and other related information like the description. In a case like this, however, what would be the best way to create a schema to handle this? I could do a lookup table of some sort, and list "common" information in one table (sales copy, description, is it recycled, etc.) and link this to another table that lists out the products and its packaging level along with a "computed" column that has the displayed SKU. Maybe something like:

# Products table
id    description             recycled   sales copy
1     ACME(R) Widget Plus     1          The only Widget you will ever need...

# Packaging table
product_id        unit          selling_sku    list_price
1                 EA            ACM1234        5.00
1                 CT            ACM1234CT      50.00
1                 BX            ACM1234BX      28.00

But I am afraid this will become very complex and unwieldy when it comes to looking up or adding new products. Our vendors who we receive the product feeds from contain what amounts to one single table with all of the related information, and don't assume you are selling a product in multiple packaging types (just the main type). Also they provide their own artificial IDs that relate everything together. The other way I was thinking of doing this was having a row in the Products table correspond to the regular product entity (e.g. single) and then have the related table with other package levels for that product, and on the UI (which I haven't given any thought to yet as I'm trying to architect a proper data model) do an "auto suggest" type of deal where if you type ACM1234 it will display that along with the BX and CT variants, and you can choose the one you want and get the appropriate values populated by it.

I feel that I'm on the right track, but I still think I'm missing something and my head is starting to spin from attempting to run all of these potential scenarios mentally to come up with a scalable solution. It needs to be able to accommodate products that can be sold individually, in boxes, and in cartons from multiple vendors at different pricing levels.

Any suggestions or thoughts?

EDIT: I am using SQL Server 2005 Standard as the database.

EDIT (02/25/2009): So, taking the advice of awithrow and David Aldridge something along the lines of:

# Products
id          base_sku          ...
1           ACM1234

# Vendors
id          name
1           United Supply Co.
2           ACME Office Supply

# ProductsPackages
id          productID          unit          listPrice
1           1                  EA            5.00               
2           1                  BX            15.00
3           1                  CT            40.00

# VendorsProductsPackages
vendorID          packageID          cost          
1                 1                  3.45          
1                 2                  7.85
1                 3                  14.86
2                 2                  10.45

Possibly create a table that has a mapping of SKU suffixes for each type of unit in case we add more.

Answer 1

It sounds like you are on the right track but it looks like your tables need to be broken up a little bit more. Here is how I would do this:

A products table similar to what you have:

Products table

id    description             recycled  base_SKU     sales_copy
1     ACME(R) Widget Plus     1         ACM1243      The only Widget you will ever need...

A Packaging table

Packaging table:

id     unit     SKU_suffix
1      EA       ''
2      CT       'CT'
3      BX       'BX'

Finally a third table that relates the two:

Packaged Product Table

id     product_id     package_id     price
1      1              1              $5
2      1              2              $50
3      1              3              $28

Now you can select from the third table and use the base_SKU and SKU_suffix to create the 'final' SKU. This will vary depending on your what DB engine you're using. This ideal that it prevents you from copying the same information in to multiple tables.

You also mentioned presenting different packages to different customers i.e. not selling individual items to the government. You could create another table called 'catalogs' or something similar which would map Packaged Product ids to customer ids. Then you could send custom catalogs to customers. You could also just generate a catalog for the govt by doing something similar to:

SELECT <fields> from Packaged_Products WHERE package_id != 1

again, this will vary depending on your DB

hope this helps

Answer 2

It sounds to me lie you'll need the following tables:

• Product: the base product itself
• Product_Package: the packages it comes in
• Vendor: the complete list of vendors
• Product_Package_Vendor: the intersection that relates vendors to the product packages that they supply