How to model Build-To-Order products?

Am working on an e-commerce site for my wife to sell silk-screened T-shirts.

Customers add a “Shirt Design” to their shopping cart and then select a “Shirt Size” and “Shirt Color”.

For now, we will probably just offer a few colors and sizes, and the shirts will be unisex.

What is the best way to set this up in the back-end database?

I am assuming we will want an “Item Number” for business purposes?!

If so, then would I want an “Item Number” based on just the “Shirt Design” OR should it be based on the combination of the components (i.e. Shirt Design + Shirt Color + Shirt Size)?

Also, if it is the latter, what happens if we adds lots of colors, sizes and other variables like gender-type, shirt-style, etc?

I want a database that is scalable, but also not so complicated that I can’t get it built and working with PHP.

Could use some advise on this…

TomTees

Are all shirts available in the same colors and sizes? Does the color and size impact the price?

Will configurable attributes be static or dynamic? You “say” all you need NOW is color and size, but will that ever change or change based on the selected design?

You were saying something about gender-type and shirt-style. How do those impact the price of the design? Do they even impact the price of the design or are they more or less classifications by which to group similar designs for display purposes?

Lots of good questions! (Hey, I’m just the IT guy!) :lol:

I think we are planning that. (There likely only be {white, black, forest, midnight blue, yellow} colors.)

Does the color and size impact the price?

I don’t think think so.

Will configurable attributes be static or dynamic? You “say” all you need NOW is color and size, but will that ever change or change based on the selected design?

It certainly could change.

This is a start-up business, so supply will meet demand.

You were saying something about gender-type and shirt-style. How do those impact the price of the design? Do they even impact the price of the design or are they more or less classifications by which to group similar designs for display purposes?

Well, I believe we will just start with plain old T-shirts. But if demand grows, we might offer different style shirts (e.g. long-sleeve, crew, sweatshirts, etc.)

The price would surely change between a T-shirt and a Sweatshirt.

Like many real-life situations, we just don’t know what will happen on so many levels, and as the IT-side of this partnership, I’m just trying to build a reasonably well-designed solution. (You don’t need a school bus if you can only get a few passengers.)

Here are my guesses on things…

Color probably won’t affect price unless it is some strange or obscure color.

Size probably has a minimal impact and we could likely just price an average to standardize the price across sizes.

Shirt style will definitely affect price IF we ever offer more than just T-shirts.

I don’t mind building tables that handle every combination of color, size, shirt type, etc HOWEVER, do I need to create a unique SKU for every possible combination?

Do I need…

tshirt_male_blue_xlarge
tshirt_male_red_xlarge
tshirt_female_blue_xlarge
tshirt_female_red_xlarge

I mean I guess concatenating component attributes to create a “master SKU” wouldn’t be that hard, but I just wasn’t sure if that’s the way you want to do things.

Realistically to increase our chances of business survival, I would think that we want to minimize the shirt size and color selection and focus on the silk-screen images.

(You likely spend $30 on a T-shirt because it says “Iowa Little-League State Champions 2010” and not because it is blue?!)

And, therefore, I was leaning towards just making the SKU based on the silk-screen.

Then again, the components of making a finished product could be important enough making a more complex SKU. (Good thing we aren’t building computers!)

Hope that helps answer your questions and to give you a better idea of what we might face.

TomTees

Considering your requirements I would propose something like:


products_config:

id      |       config_name
--------------------------------
 1      |         'color'
 2      |         'size'
 3      |          'style'
 
 
 
products_config_values:

id      |   config_id   |  config_value         |   price_modifier
----------------------------------------------------------------------
1       |       1       |     'white'           |       NULL
2       |       1       |     'black'           |       NULL
3       |       1       |     'forest'          |       NULL
4       |       1       |     'midnight blue'   |       NULL
5       |       1       |     'yellow'          |       NULL
6       |       2       |     'S'               |       NULL
7       |       2       |     'M'               |       NULL
8       |       2       |     'L'               |       NULL
9       |       2       |     'Xl'              |       2.00
10      |       2       |     'XXL'             |       5.00
11      |       3       |     'long-sleeve'     |       15.00
12      |       3       |     'crew'            |       3.00
13      |       3       |     'sweatshirt'      |       10.00
14      |       3       |     't-shirt'         |       10.00


designs:

id      |      design_name     |  base_price
-------------------------------------------------
1       |       'ABC'          |    5.00
2       |       '123'          |    20.00
3       |       '456'          |    15.00

So when someone choose design ABC the base price would be: 5.00

The color would not change the price.
The size may change the price.
The shirt type would change price.

Adding up the base price w/ the the pricing modifiers would result in the final sale price.

This is probably one of the more simple designs for your given CURRENT requirements.

Anyhow, no you do not need tables for every single sku option combination – that would be insane.

You could even do something like this where the design actually becomes apart of the configuration. The product would than just be a abstract container linking to the base config value:


products_config:

id      |       config_name
--------------------------------
 1      |         'color'
 2      |         'size'
 3      |         'style'
 4      |         'design'
 
 
 
products_config_values:

id      |   config_id   |  config_value         |     base_price
----------------------------------------------------------------------
1       |       1       |     'white'           |       NULL
2       |       1       |     'black'           |       NULL
3       |       1       |     'forest'          |       NULL
4       |       1       |     'midnight blue'   |       NULL
5       |       1       |     'yellow'          |       NULL
6       |       2       |     'S'               |       NULL
7       |       2       |     'M'               |       NULL
8       |       2       |     'L'               |       NULL
9       |       2       |     'Xl'              |       2.00
10      |       2       |     'XXL'             |       5.00
11      |       3       |     'long-sleeve'     |       15.00
12      |       3       |     'crew'            |       3.00
13      |       3       |     'sweatshirt'      |       10.00
14      |       3       |     't-shirt'         |       10.00
15      |       4       |     'ABC'             |       5.00
16      |       4       |     '123'             |       20.00
17      |       4       |     '456'             |       15.00


products:

id      |      product_name     |  base_config_values_id
-------------------------------------------------
1       |        NULL          |    15
2       |        NULL          |    16
3       |        NULL          |    17

You could also remove the products table entirely and rely on the configurations to create “products”:


products_config:

id      |       config_name     |    config_priority
--------------------------------------------------------
 1      |         'color'       |           4
 2      |         'size'        |           3
 3      |         'style'       |           2
 4      |         'design'      |           1
 
 
 
products_config_values:

id      |   config_id   |  config_value         |     base_price   
-----------------------------------------------------------------
1       |       1       |     'white'           |       NULL
2       |       1       |     'black'           |       NULL
3       |       1       |     'forest'          |       NULL
4       |       1       |     'midnight blue'   |       NULL
5       |       1       |     'yellow'          |       NULL
6       |       2       |     'S'               |       NULL
7       |       2       |     'M'               |       NULL
8       |       2       |     'L'               |       NULL
9       |       2       |     'Xl'              |       2.00
10      |       2       |     'XXL'             |       5.00
11      |       3       |     'long-sleeve'     |       15.00
12      |       3       |     'crew'            |       3.00
13      |       3       |     'sweatshirt'      |       10.00
14      |       3       |     't-shirt'         |       10.00
15      |       4       |     'ABC'             |       5.00
16      |       4       |     '123'             |       20.00
17      |       4       |     '456'             |       15.00

Show all available designs: (hypothetical)


SELECT
     pcv.config_value product_name
  FROM
     products_config pc
 INNER 
  JOIN
     products_config_values pcv
    ON
     pc.id = pcv.config_id
 WHERE
     config_priority = 1

Oddz,

On a side note…

Is there any easy way for me to show my database design on Sitepoint?

(When I used to be an MS Access developer, it was easy to just attach a .mdb file which already contained an ERD for the database.)

If I attached a GIF from some CASE tool, would that be viewable enough?

Also, how did you get all of your text nicely lined up in what you posted earlier?

TomTees

There are several ways to present your information.

If it’s a large amount, and plain text, then adding it to a post as an attachment will work.

Attached images work well too.

*note: attachments are moderated and may take a while to be approved and become available.

If the amount is small to moderate, then using bbcode tags helps a lot. http://www.sitepoint.com/forums/misc.php?do=bbcode most of these can be selected using the icons found at the top of the reply box. Also, the “select syntax” drop-down select works well.

I think for what you want here is to put your CREATE statement into SQL syntax. eg. [NOPARSE]


or


or


[/NOPARSE]

Thanks for the tips, but I was mainly talking about how Oddz was able to get nice, aligned columns of text separated by “pipes” || above.

The appear to be in “list boxes” with scrolling bars.

Did he paste that from some other application, or was he using formatting codes to get nicely lined up text?

TomTees

TomTees wrote:

Also, how did you get all of your text nicely lined up in what you posted earlier?

That was achieved manually using a text editor. I knew ahead of time what needed to be written and I planned for the column like layout.

Ah yes, that’s one of those things you find out eventually. Content placed inside code bbtags retains tabs. I’ve used it before to present tabular data that wasn’t technically code just so the tabs would be there.

Why are you putting the values for all attributes in one table (i.e. “products_config_values”)??

That seems like a strange way to group data.

TomTees

Oddz,

Okay, I was kicking things around during supper and think I’ve made some progress.

(Oddz and all) Please let me know how this seems as a solution…

PRODUCT (SKU) definition:
A finished silk-screened shirt consists of 4 “components”…
1.) (Silk-screen) Design
2.) Shirt Style
3.) Shirt Size
4.) Shirt Color

(This definition could change, but unlikely will.)

Below are abridged versions of the tables and some sample values…


COLOR table

id	|	name	|	cost
----------------------------------------
01		White		$0
02		Black		$0
03		Blue		$0
04		Forest		$0


SIZE table

id	|	name	|	cost
----------------------------------------
01		S		$0
02		M		$0
03		L		$0
04		XL		$0
05		XXL		$4
06		XXXL		$4


DESIGN table

id	|	name	|	cost
----------------------------------------
1000		Rawrrr		$12
1001		Nom Nom Nom	$11
1002		Clever Girl	$8
1003		B is for Bacon	$10


STYLE table

id	|	name		|	cost
------------------------------------------------
01		T-shirt			$10
02		T-shirt (female)	$10
03		Long Sleeve		$14
04		Hoodless Sweatshirt	$20


PRODUCT (SKU) table

id  (pk)
design_id  (SKU index)
style_id  (SKU index)
size_id  (SKU index)
color_id  (SKU index)
list_price??


A sample product would be...

1000		01		04		01
Rawrrr		T-shirt		XL		White
$12		$10		$0		$0


[b]SKU: 1000-01-04-01[/b]
Rawrrr
T-shirt
XL
White
List Price: $22

Then in the Product (SKU) table, I would pre-define every combination of finished shirt that is available.

In the Product Catalog, I would use SQL to refer to the Product (SKU) table and determine which Styles, Sizes, and Colors to display for a given Design.

When someone selects a Finished Shirt and adds it to their Shopping Cart, the SKU (e.g. “1000-01-04-01”) will be added to the Order_Details table.

And the benefit of having a complex SKU is that it will help to manage…

  • Inventory
  • Product Costs
  • Product Profits
  • Demand for various Shirt configurations

How does that sound for a solution?

TomTees

The reason he’s doing this is because of the exact reason you mentioned - they are attributes of a problem. Just because in the “real world” they are different, in reality they are just things used to describe an item.

This method is really complex and also limits some flexibility (as you add more attributes - longsleeve/short sleeve, material (cotton/lycra), etc).

I personally would have a setup more like Oddz provided you. It would allow you to automatically calculate the prices depending on the attributes and doesn’t require a whole new table if you add a new one.

But that’s just me. I can see why you’d want to do it the way you suggest - the only worrisome thing is it will get more complex as you add more attributes.

If I get time, I’ll whip up a quick design, but I’ve got some work to get done first…

True, but it is also harder (for me) to follow.

My design seems better laid out, although doesn’t scale as well as Oddz’s.

Then again - dying last words - I don’t expect the number of “attribute tables” to really change. Think about it… There isn’t much more to a shirt than its style, color, size, and what is printed on it. (If I was Dell computers, then Oddz’s approach is probably MUCH better.)

Another thing I like about my design is that is allows you to restrict what combinations can exist. (Maybe XXXL T-shirts only come in White or Black?)

Not sure how you’d do that using Oddz’s approach?

What does everyone else think about my table design?

If I get time, I’ll whip up a quick design, but I’ve got some work to get done first…

That would be great.

TomTees

I don’t know about anyone else, but it seems like something unexpected always finds it’s way in eventually for me. Thank goodness for ALTER

True, but remember that my initial goal is to create a reasonably well-designed site (and back-end) that I can understand, maintain, and get up and running.

The table layout I came up with last night makes more sense to me, and I do not believe that there will be anymore attributes any time soon. In fact, it is likely we will only offer plain T-shirts that are either White or Black.

(This business idea is based on selling cute, creative, funny designs on T-shirts versus being a clothing store.) :slight_smile:

Nonetheless, I do want to investigate things and plan for growth within reason.

TomTees

Preferably if a problem can be anticipated its always good to program for it. By using separate tables for every option you are pigeonholing yourself into those options. When you need to add more options your going to kick yourself. You are also breaking first normal form in that all those items can better be described as a collection of options rather than entirely separate entities.

id (pk)

option_1 (SKU index)
option_2 (SKU index)
option_3 (SKU index)
option_4 (SKU index)

list_price??

It may appear “easier” to manage data like that now but down the line your going to regret it.

Most of what needed to be said was touched upon by DaveMaxwell though.

Although, one very last point is the fall through of the method I proposed. You can add any option or value and all products will inherit it automatically. However, for your approach you would need to alter tables and add the option one by one to every product. Why do it that way when all products/designs (for the most part) are going to be available in the same color, size and style?

Well that was not part of the original requirements outlined.

However, that most likely be a separate series of table/tables for removing options at the config_value level.

perhaps:


products_config_values_removed_values 

id      |     products_config_values_id         |       removed_products_config_values_id
-----------------------------------------------------------------------------------------
1       |               10                      |                   3
1       |               10                      |                   4
1       |               10                      |                   5

I wonder what r937 thinks about this topic/question… :slight_smile:

TomTees

But they are just “look-up tables” and very unlikely to change.

You are also breaking first normal form in that all those items can better be described as a collection of options rather than entirely separate entities.

I don’t follow?

1st NF address “repeating groups”. Having the fields “Student1”, “Student2”,… , “Student_n” in the “Teacher” table would be a violation of 1NF.

Having logically distinct attributes in the “Product” table is not a violation of 1NF.

It may appear “easier” to manage data like that now but down the line your going to regret it.

So let me play devil’s advocate…

Let’s say you have a PEOPLE table with attributes describing a Person.


person_config:

id	|	attribute_name	|	attribute_priority
----------------------------------------------------------
1	|	gender		|	1
2	|	age		|	2
3	|	height		|	3
4	|	weight		|	4
5	|	fav_dessert	|	5


person_config_values:

id	|	attribute_id	|	attribute_value
-------------------------------------------------------
1	|	1		|	'male'
2	|	1		|	'female'
3	|	2		|	21
4	|	2		|	22
5	|	2		|	23
6	|	2		|	24
7	|	2		|	25
8	|	3		|	5'0" (not escaped!!)
9	|	3		|	5'1"
10	|	3		|	5'2"
11	|	3		|	5'3"
12	|	3		|	5'4"
13	|	4		|	150
14	|	4		|	160
15	|	4		|	170
16	|	4		|	180
17	|	5		|	'chocolate'
18	|	5		|	'vanilla'
19	|	5		|	'strawberry'

While you could do that, it seems to informally break some Normalization (and logic) rule in that it doesn’t make sense to put Gender, Age, Height, Weight, and Favorite Dessert all in one table.

Following that approach, you could build nearly any RDBMS with 2 tables!!

Since the SHIRT(s) we are working with only have a few physical attributes that likely won’t change - as opposed to things that are more configurable (e.g. cars, computers, electronics, etc.).

Just my two-cents…

TomTees