How to model Build-To-Order products?

[DaveMaxwell]

@TomTees

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

True, but if you need to add characteristics, that’s going to cause an additional table to be created and all the queries to have to be re-written.

@TomTees

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.

Depends on how granularly you’re looking at them. You’re looking at it from the sense that these are specific and distinct items that can describe an item. He and I are looking at it from the aspect that these are all attributes which can be used to describe. Sure, they describe different aspects of the object, but they still describe the object.

@TomTees

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.

In a generic sense, sure that’s true. But to plays angels advocate (to your devil :D) - age, height and weight would never be in a lookup table because they are finite and definitive (numeric) values. There are too many variations in those values for it to make sense. Now gender and favorite ice cream? Sure. Those are finite values so putting them into lookup tables makes sense.

Now, whether to put them into one table or two is where you could discuss. If you were adding additional information which would go with the value, then separate tables would make sense. So for example, if you were tracking the calories of ice cream people ate (assuming everyone ate one cone a day), then putting the ice cream flavors into a separate table would make sense because that record would also contain the calories, grams of fat, etc.

However, if it’s purely informational and it’s just a label, then there’s no reason to put them into separate tables because the structure is the same. You can separate them if you’d like, but it makes just as much sense to put them into one table if you look at them as attributes/labels of a person and not gender and ice cream flavor.

@TomTees

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

Only if your database is describing one object and that object has simple, finite attributes which can be applied.

@TomTees

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.).

If that is what you think is best for your situation, then by all means go for it. We’re just trying to get you to look past the current situation and see where the future needs could be. What if you start doing something other than t-shirts? What if you do sweatshirts? Or bags or mousepads or…? Or you start selling plain shirts for some reason?

Can your db design handle an expansion of products without a total rewrite? If it can, cool. But if not, then perhaps you need to step back and see where you can improve upon it.

[oddz]

@TomTees

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.

Ok, here is the thing. I’m looking at colors, sizes, etc as attributes of the product. So in that respect first normal form is being broken.

Now, if you are truly looking at those as colors, sizes, etc than fine. However, you would need to remove the cost because a color, size, etc doesn’t have a cost. They only have a cost when you apply them as attributes to the product. Otherwise they are unique and have no cost associated with them. Make sense?

[TomTees]

Wow, this thread is getting the ol brain workin’ overtime!

[QUOTE]Originally Posted by TomTees View Post
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.

Depends on how granularly you’re looking at them. You’re looking at it from the sense that these are specific and distinct items that can describe an item. He and I are looking at it from the aspect that these are all attributes which can be used to describe. Sure, they describe different aspects of the object, but they still describe the object.
[/QUOTE]

Interesting way to view things.

I am viewing them in the physical world, so I don’t see Age as having any relation to Weight or Favorite_Dessert.

Sure, they all describe aspects of a PERSON, but like I said, if you just view everything as an “attribute” then you could condense most databases down to a few tables.

[QUOTE]
Originally Posted by TomTees

So let me play devil’s advocate…

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

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

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.

In a generic sense, sure that’s true. But to plays angels advocate (to your devil ) - age, height and weight would never be in a lookup table because they are finite and definitive (numeric) values. There are too many variations in those values for it to make sense. Now gender and favorite ice cream? Sure. Those are finite values so putting them into lookup tables makes sense.[/quote]

Okay.

Now, whether to put them into one table or two is where you could discuss. If you were adding additional information which would go with the value, then separate tables would make sense. So for example, if you were tracking the calories of ice cream people ate (assuming everyone ate one cone a day), then putting the ice cream flavors into a separate table would make sense because that record would also contain the calories, grams of fat, etc.

Agreed.

However, if it’s purely informational and it’s just a label, then there’s no reason to put them into separate tables because the structure is the same. You can separate them if you’d like, but it makes just as much sense to put them into one table if you look at them as attributes/labels of a person and not gender and ice cream flavor.

I can see your perspective, but still prefer the harder division by seperating things into separate tables.

[QUOTE]Originally Posted by TomTees View Post
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.).

If that is what you think is best for your situation, then by all means go for it. We’re just trying to get you to look past the current situation and see where the future needs could be.[/QUOTE]

Well, let’s get back to my original question(s) and that will answer your questions/concerns…
[COLOR=“Blue”]

Original Post:
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)?
[/COLOR]

What I was asking help for was creating an Item # (aka “SKU”) for each finished Silk-Screened T-Shirt.

Though simple, we are building a “Finished Good” in accounting terms, right?

And “Finished Goods” are composed of “Raw Materials” (e.g. Silk-Screen Design + Shirt).

The term “BOM” (pronounced “bomb”) - standing for “Bill Of Materials” is often used in manufacturing.

So - not being an accountant or savvy businessman - I was thinking that…

1.) Using an AutoIncrement PK for an Item_Number is pretty meaningless

2.) Assigning an Item_Number to just the “Shirt Design” isn’t a good idea, since the type of shirt is as much a part of the finished product as the design, right?

However, creating a SKU with the following components:

Shirt_Design + Shirt_Type + Shirt_Size + Shirt_Color

would thoroughly describe WHAT we are selling in terms of COMPONENTS and the FINISHED PRODUCT.

And as far as your and Oddz’s concerns about changing attributes… [b]Once a SKU format was decided upon, it would not and could not change because a SKU format should stay the same for eons. Right?

What if you start doing something other than t-shirts? What if you do sweatshirts?

“Shirt Style” would handle that.

Or bags or mousepads or…?

So, mouse pads are not shirts?! Why couldn’t there be a DIFFERENT SKU format for mouse pads?

I bet if you went to a mega grocery or retail store that you would find SKU’s that varied in length and format across all products in the store…

Or you start selling plain shirts for some reason?

So the “Shirt Design” could equal “0000” and you would have varying values for “Shirt Type”, “Shirt Size” and “Shirt Color”, right?

No problem there.

Can your db design handle an expansion of products without a total rewrite?

That depends on your response to what I said above.

It also depends on how the database and front-end interact.

If someone can show me how to “abstract” things using OOP, I bet it could expand nicely even with disparate Products, SKU formats, etc.

If it can, cool. But if not, then perhaps you need to step back and see where you can improve upon it.

Won’t argue there.

TomTees

[TomTees]

@oddz

Ok, here is the thing. I’m looking at colors, sizes, etc as attributes of the product. So in that respect first normal form is being broken.

But they are physically different attributes so I don’t see them as logically related, and thus are not a “repeating group”.

I can see that you are abstracting things a different way.

I understand where you are coming from, but still prefer my way.

Now, if you are truly looking at those as colors, sizes, etc than fine. However, you would need to remove the cost because a color, size, etc doesn’t have a cost. They only have a cost when you apply them as attributes to the product. Otherwise they are unique and have no cost associated with them. Make sense?

But, again, it depends on how you abstract/view things.

RED is free to everyone.

But a Red T-shirt will cost you extra because it uses different dyes/pigments which cost more money. So, yes, “Red” - as in “Shirt Color” does have a cost associated with it. (Go to an art supply store and price tubes of paint. You will see that colors like “red” are much more expensive than, say “white” because one is physically or greater value, just like the precious metal “gold” is more expensive than “silver”.)

Shirt Color does not equal Color, though I see where you are coming from.

TomTees

[DaveMaxwell]

@TomTees

I can see your perspective, but still prefer the harder division by seperating things into separate tables.

Like I said earlier, if that’s how you want to handle it, then by all means go for it. It’s quite obvious that you’ve set your mind that your way is the best method for handling it already, and nothing we’ve written has changed that thought for you, so run with what makes you comfortable. It may work out just fine, it may cause you some re-work in the future. It depends on how far and fast the business grows. We are going to have to agree to disagree.

@TomTees

What I was asking help for was creating an Item # (aka “SKU”) for each finished Silk-Screened T-Shirt.

<snip />
1.) Using an AutoIncrement PK for an Item_Number is pretty meaningless

2.) Assigning an Item_Number to just the “Shirt Design” isn’t a good idea, since the type of shirt is as much a part of the finished product as the design, right?

However, creating a SKU with the following components:

Shirt_Design + Shirt_Type + Shirt_Size + Shirt_Color

would thoroughly describe WHAT we are selling in terms of COMPONENTS and the FINISHED PRODUCT.

Honestly, I missed that part of the original question - I was dealing with the first half on the best way to set it up.

I would agree that an autoincrement wouldn’t work as a user friendly SKU, you need to keep the sku short and simple or it becomes overwhelming.

The other way you could look at this is if you are dealing with physical inventory only, then wouldn’t the SKU simply be Shirt_Type + Shirt_Color + Shirt_Size (I would order the fields this way, but again that’s just me). The design is added on at the end, but in terms of inventory, it’s not there already, so shouldn’t go into the sku conversation.

@TomTees

“Shirt Style” would handle that.

OK. I guess your shirt style would have hooded, regular, zip-up, etc.

@TomTees

So, mouse pads are not shirts?! Why couldn’t there be a DIFFERENT SKU format for mouse pads?
<snip />

I wasn’t worried about the sku (again, I didn’t see that part of the OP). I was worried about the attributes which would apply to that item. A mousepad wouldn’t have a size or style. It might have a color and a design, but that’s it.

If you’re only going to ever have shirts, then OK. But if you’re going to branch to other products, you need to plan for that.

Like I said, it seems you’ve already set your mind. I was just trying to show where your business could go and ensuring you thought of everything before hand. It’s much easier to spend the time to think of this stuff now then it is to convert the data in the future.

[TomTees]

@DaveMaxwell

Honestly, I missed that part of the original question - I was dealing with the first half on the best way to set it up.

I would agree that an autoincrement wouldn’t work as a user friendly SKU, you need to keep the sku short and simple or it becomes overwhelming.

Well, I’ve been taught that every table should have an auto-increment ID field (for performance), but since I subscribe to the “physical key” model, I prefer to have a pseudo-key that uses something physical and thus meaningful.

So I would have an auto-increment ID and then four other fields that are a joint-index to serve as the “pseudo” PK.

If that makes sense?!

The other way you could look at this is if you are dealing with physical inventory only, then wouldn’t the SKU simply be Shirt_Type + Shirt_Color + Shirt_Size (I would order the fields this way, but again that’s just me). The design is added on at the end, but in terms of inventory, it’s not there already, so shouldn’t go into the sku conversation.

That is an interesting point, but from a Finished Good standpoint, wouldn’t you want to know that you sold a “I love BACON”, T-shirt (female), Large, Black??

Maybe I need a Shirt SKU, a [b]Design SKU[/b[, and then a Finished, Silk-Screened Shirt SKU???

This leads back to my OP. How do you manage Items in inventory and your e-commerce site beyond auto-increments of 1, 2, 3, 4,…, n??

If you’re only going to ever have shirts, then OK. But if you’re going to branch to other products, you need to plan for that.

Is it bad to have a different SKU “architecture” for different product lines?

1000-10-01-25 (for shirts)

1000-5024-01 (for other products)

Like I said, it seems you’ve already set your mind. I was just trying to show where your business could go and ensuring you thought of everything before hand. It’s much easier to spend the time to think of this stuff now then it is to convert the data in the future.

And I appreciate that.

Not as much made up my mind, as just stressing what was important, like once a SKU is designed it won’t like change so needing a super-flexible design using on or two tables isn’t relevant in this case.

I can see that my questions are really more about building an Inventory System than an E-commerce System?!

But I didn’t want to take the naive approach of just building an e-commerce site that offers {white, black, blue, yellow} T-shirts in {S, M, L, XL} to later realize that we have an inventory nightmare and an e-commerce site that can’t scale.

I think what I came up will work nicely for the foreseeable future, but obviously wanted to get opinions from others - especially anyone who is a guru with e-commerce sites and/or inventory management systems.

TomTees

[DaveMaxwell]

@TomTees

Well, I’ve been taught that every table should have an auto-increment ID field (for performance), but since I subscribe to the “physical key” model, I prefer to have a pseudo-key that uses something physical and thus meaningful.

So I would have an auto-increment ID and then four other fields that are a joint-index to serve as the “pseudo” PK.

If that makes sense?!

The only time an auto-incremented ID is “needed” when it’s going to be a foreign key in another table. You can do without it, but there can be a noticeable performance hit depending on what your “real” key is on the table.

@TomTees

That is an interesting point, but from a Finished Good standpoint, wouldn’t you want to know that you sold a “I love BACON”, T-shirt (female), Large, Black??

From an inventory standpoint, no. From the e-commerce systems I’ve worked with, the physical inventory is accounted for, then any customization that is added onto it is saved on the record as an “option”. It’s not part of the product - it’s an add-on.

@TomTees

Maybe I need a Shirt SKU, a Design SKU[/b[, and then a Finished, Silk-Screened Shirt SKU???

This leads back to my OP. How do you manage Items in inventory and your e-commerce site beyond auto-increments of 1, 2, 3, 4,…, n??

Is it bad to have a different SKU “architecture” for different product lines?

1000-10-01-25 (for shirts)

1000-5024-01 (for other products)

Honestly, I think you’re worrying about SKU’s a little too much. They are a human readable component, not a critical piece of architecture. You could call them alpha, beta and gamma or even See, Spot and Run if you wanted to and it wouldn’t have an effect on the system. You’ll still have your primary keys defined (auto-incremental IDs) which you’ll use to tie your tables together. How you display the information to the user isn’t a critical piece of the puzzle here. An important one, sure. But from an db architecture standpoint, not so much.

@TomTees

Not as much made up my mind, as just stressing what was important, like once a SKU is designed it won’t like change so needing a super-flexible design using on or two tables isn’t relevant in this case.

I can see that my questions are really more about building an Inventory System than an E-commerce System?!

But I didn’t want to take the naive approach of just building an e-commerce site that offers {white, black, blue, yellow} T-shirts in {S, M, L, XL} to later realize that we have an inventory nightmare and an e-commerce site that can’t scale.

I think what I came up will work nicely for the foreseeable future, but obviously wanted to get opinions from others - especially anyone who is a guru with e-commerce sites and/or inventory management systems.

I’m not an e-commerce guru, but I deal with one everyday. So I know how it’s setup and the trials/tribulations I deal with every day - which is why I’ve given some of the advice I’ve given you. I’m basing my opinions on what I’ve done and what I’ve had to tweak/look to improve upon.

But if what you have is comfortable and you think it’ll scale for the forseeable future, that’s your call.

[r937]

@TomTees

Well, I’ve been taught that every table should have an auto-increment ID field (for performance)

sorry, you were taught wrong

[TomTees]

Originally Posted by TomTees View Post
Well, I’ve been taught that every table should have an auto-increment ID field (for performance)

@r937

sorry, you were taught wrong

Well not to make this thread any longer, but how do you see it?

I have always favored “natural” keys (over “artificial”/“surrogate” keys) because they are self-documenting. And for those that say, “But natural keys can change!” my response is, “So it is worth the risk.”

It seems like in the Microsoft world (esp MS Access), people are convinced that the Alpha & Omega is the AutoIncrement PK.

Over time, it seemed to me that a combination works best…

Given (nearly) every table an AutoIncrement PK for immutability and performance, but then also have a Natural Key (one or more fields) that is tied to a Unique Index thus making it a “pseudo” PK as well.

That way if your Natural Key changes, it doesn’t disrupt related tables as much, and you still get the added performance and stability of an Artificial Key.

For simple “Look-up” tables (e.g. States, Colors, etc) I would use a Natural Key.

What do you think r937??

TomTees

[r937]

my feeling is you should use an autonumber only when a suitable natural key doesn’t exist

unsuitable keys include multi-column composite foreign keys

“added performance and stability of an Artificial Key” is a myth

as for the concern that natural keys can change, this is exactly what ON UPDATE CASCADE is for

[oddz]

I get the feeling you would like every purchasable item represented by a physical row in the database. If that is the case what can be done is to create a table products and part numbers. Part numbers in your case are the SKUs and products are a group of skus based on the same design.

What you will end up with a table for products like the below:

products:

id      |       display_name
---------------------------------
 1      |       I Love Bacon
 2      |       I Hate Bacon
 3      |       Another product Name

Now what you can do is create a separate table with a foreign key to each product for every part number or SKU of that product.

part_numbers

id      |   products_id     |       sku     
-----------------------------------------------
 1      |         1         |   x4567-9085-6
 2      |         2         |   dfg9087-6758
 3      |         2         |   fdsw34094
 4      |         2         |   34gvbghl-9058
 5      |         3         |   fg-9087-7839
 6      |         3         |   45-0958-89578 

In the above case now I Love Bacon has 1 item that can be purchased, I Hate Bacon has 3 items and Another product name has 2. The entries within this part numbers table represent the physical items that can be purchased. The products table groups them together based on a dominant similarity such as; design.

Now to revisit options. Options will now link to a product rather than be global. So the options table will hold foreign key and the name of the option for every product. That gives you ability to fully customize every products available options individually.

 products_options
 
 id     |    products_id    |       option_name
 -------------------------------------------------
  1     |        1          |       'color'
  2     |        1          |       'size'
  3     |        1          |       'style'
  4     |        2          |       'color'
  5     |        2          |       'size'
  6     |        2          |       'style'
  7     |        3          |       'color'
  8     |        3          |       'size'
  9     |        3          |       'style'

Once that is in place another table is needed to host all option values for each option. So this table will have a foreign key to an option and its value.

  products_options_values
  
  id      |     products_options_id     |         option_value
-----------------------------------------------------------------
  1       |             1               |         'white'
  2       |             1               |         'black'
  3       |             1               |         'forest'
  4       |             1               |         'midnight blue'
  5       |             1               |         'yellow'
  6       |             2               |         'S'
  7       |             2               |         'M'
  8       |             2               |         'L'
  9       |             2               |         'XL'
  10      |             3               |         'long-sleeve'
  11      |             3               |         't-shirt'
  12      |             3               |          'sweatshirt' 
  13      |             4               |         'white'
  14      |             4               |         'black'
  15      |             4               |         'forest'
  16      |             4               |         'midnight blue'
  17      |             4               |         'yellow'
  18      |             5               |         'S'
  19      |             5               |         'M'
  20      |             5               |         'L'
  21      |             5               |         'XL'
  22      |             6               |         'long-sleeve'
  23      |             6               |         't-shirt'
  24      |             6               |          'sweatshirt'
  25      |             7               |         'white'
  26      |             7               |         'black'
  27      |             7               |         'forest'
  28      |             7               |         'midnight blue'
  29      |             7               |         'yellow'
  30      |             8               |         'S'
  31      |             8               |         'M'
  32      |             8               |         'L'
  33      |             8               |         'XL'
  34      |             9               |         'long-sleeve'
  35      |             9               |         't-shirt'
  36      |             9               |          'sweatshirt'

This is makes it possible to remove or add a special option value for any given product. For example, you can now sell the I Love Bacon design in every thing but S by removing that option value from this table for product with primary key 1 which references the I Love Bacon shirt.

Now the last step is connect the option values with the appropriate part number. This is how you would know that the sku 34gvbghl-9058 is a I Hate Bacon product that is yellow, XL and long-sleeve.

part_numbers_to_products_options_values:

part_numbers_id     |       products_options_values_id
---------------------------------------------------------
         1          |                  1
         1          |                  9
         1          |                  3
         2          |                  16
         2          |                  19
         2          |                  18

Outlined in that table are the skus x4567-9085-6 and dfg9087-6758. The x4567-9085-6 sku is a white, XL, sweatshirt. The x4567-9085-6 sku is a mid-night blue, M, t-shirt.