Database design for products composition

here is a little challenge for you fellow overflower.

I am working with a database of products that we sell into our Web based quotation application. Each product has the following attributes:

• Unique ID
• SKU
• Description
• Price
• Parts

To give you a general idea, we manufacture sunrooms and we have roughly 800 generic models. Obviously, depending on the customer and his house, the generic model may not fit and needs to be modified using "options" such as adding a door on a side, changing the color and more.

As of right now in our quotation system, we do not worry about what option (new color, extra door, etc) the customer chose for his sunroom, we simply worry about the generic model name (ex: SUN-0810 for an 8 by 10 sunroom) and then set a price for it.

My end goal is to be able to generate the BOM (Bill of Material or part list) for a quotation depending on what model and what options the customer picked. We already have all the parts stored in the database and they need to be linked to the models.

Now here is my problem: We have ~800 different models with a wide variety of options for each. For instance, a standard sunroom is made of a left wall, a center and a right wall. A wall can be in 3 distinct colors, can have either no door or 1 door (possibly more depending on the size of the sunroom). A center can also be in 3 distinct colors and in 11 distinct lengths.

Here is an example of one combination for a generic model:

SUN-0810 (8 feet by 10 feet sunroom)

• Left wall: 8 feet long charcoal with a door
• Right wall: 8 feet long charcoal with no door
• Center: 10 feet long charcoal

Hence the new model name (product SKU) for this specific combination would be something like:

SUN-0810CH-L1-R0.

In the end, just for the 8 by 10 sunroom (SUN-0810), I would end up with this:

• SUN-0810CH-L0-R0 (8 by 10, color = charchoal, left wall = no door, right w开发者_StackOverflow中文版all = no door)
• SUN-0810CH-L1-R0 (8 by 10, color = charchoal, left wall = with door, right wall = no door)
• SUN-0810CH-L0-R1 (8 by 10, color = charchoal, left wall = no door, right wall = with door)
• SUN-0810CH-L1-R1 (8 by 10, color = charchoal, left wall = with door, right wall = with door)
• SUN-0810SM-L0-R0 (8 by 10, color = smoke, left wall = no door, right wall = no door)
• SUN-0810SM-L1-R0 (8 by 10, color = smoke, left wall = with door, right wall = no door)
• SUN-0810SM-L0-R1 (8 by 10, color = smoke, left wall = no door, right wall = with door)
• SUN-0810SM-L1-R1 (8 by 10, color = smoke, left wall = with door, right wall = with door)

To save you the details, we have around 800 generic models like the SUN-0810. I did some maths and realized that if I were to store every possible combination of product-option, it would result in over 50 000 distinct products.

Let's say all the 50 000 distinct products are stored in the database, I need to define their parts for every single one of them. As I said above, the parts are already stored in the database. Here are a few examples:

Part SKU | Description

ENT28 28 inches Cross stud

SCREW6-ZW 6 mm zinc white screw

ATT90ALU 90 degree aluminium tie

The end result would be this:

SUN-0810CH-L1-R0 contains:

• ENT28 x4
• SCREW6-ZW x19
• ....
• ....

I realized that the main issue is that most models contain over 50+ parts each and it would take an eternity to define the composition of all 50 000 distinct models.

Thereby, I'm looking for some help or some ideas on how to solve this problem or whether my approach is feasable or not.

Thank you for your time.

It seems to me that your challenge is not to define every possible permutation of parts. What you want to define is two lists. One list is a list of which kind of part a particular part is. For example you have one "product type" record called "6mm screw" and then a number of child records that break out the colour choices for that part.

The other thing you need is a more or less standard Bill of Materials schema that breaks down your salable products into the product types rather than the individual products.

In this way, you will have a fair bit of work to do still, but it won't be an unmanageable amount of work to do. When you pull a customized product together, this schema will allow you to guide the user through selections for each of the required product types that make up the customized product.

UPDATE: Example

Here is an example of how to abstract out the bill of materials to working with types instead of parts. Let's use OP's 8 x 10 sunroom as an example.

I'll assume for the sake of illustration that you have an ASSEMBLY table, which represents types of parts either alone or in some combination. I'll also assume a COMPOSITION table, which represents which assemblies are part of which other assemblies, and also how many of each are required. Lastly, I'll assume a PARTS table which contains the individual components in detail.

In this example, there are no PARTS for sale. Note that in a real world example, depending on your business, you might actually sell your raw parts. If that were true your ASSEMBLY table would probably have some trivial entries that correspond with individual records in PARTS. I'm going to leave that wrinkle out for the sake of this illustration.

So our scenario is an 8 x 10 sunroom, which needs a left wall, a centre wall and a right wall. The sides may or may not have doors and there are various colour combinations possible.

Your ASSEMBLY table has records like this: (Attributes: key, description)

• 1000, 8 x 10 SUN ROOM
• 2001, 8' LEFT SIDE WALL
• 2002, 8' CENTER WALL
• 2003, 8' RIGHT SIDE WALL

Your COMPONENT table has records like this: (Attributes: parent assembly key, quantity, child assembly key) - This is your Bill of Materials table...

• 1000, 1, 2001
• 1000, 1, 2002
• 1000, 1, 2003

Your PARTS table has records like this: (Attributes: key, assembly key, part description)

• 9000, 2001, 8' LEFT SIDE WALL CHARCOAL NO DOOR
• 9001, 2001, 8' LEFT SIDE WALL SMOKE NO DOOR
• 9002, 2001, 8' LEFT SIDE WALL CHARCOAL W/ DOOR
• 9003, 2001, 8' LEFT SIDE WALL SMOKE W/ DOOR
• 9004, 2002, 10' CENTER WALL CHARCOAL NO DOOR
• 9005, 2002, 10' CENTER WALL SMOKE NO DOOR
• 9006, 2002, 10' CENTER WALL CHARCOAL W/ DOOR
• 9007, 2002, 10' CENTER WALL SMOKE W/ DOOR
• 9008, 2003, 8' RIGHT SIDE WALL CHARCOAL NO DOOR
• 9009, 2003, 8' RIGHT SIDE WALL SMOKE NO DOOR
• 9010, 2003, 8' RIGHT SIDE WALL CHARCOAL W/ DOOR
• 9011, 2003, 8' RIGHT SIDE WALL SMOKE W/ DOOR

So with this there is a record for every component part that might be used, there is a record for every type of assembly containing multiple parts, and there is only one record for each type of component required for any given assembly.

This will turn out to be much less data to manage than trying to manually maintain every permutation of these parts going together. (3 records instead of 4^3 records)