SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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?
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lots of good questions! (Hey, I'm just the IT guy!)

    Quote Originally Posted by oddz View Post
    Are all shirts available in the same colors and sizes?
    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

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Considering your requirements I would propose something like:

    Code:
    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.
    The only code I hate more than my own is everyone else's.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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:

    Code:
    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
    The only code I hate more than my own is everyone else's.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    You could also remove the products table entirely and rely on the configurations to create "products":

    Code:
    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)
    Code SQL:
    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
    The only code I hate more than my own is everyone else's.

  7. #7
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,269
    Mentioned
    197 Post(s)
    Tagged
    2 Thread(s)
    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. [HIGHLIGHT="MySQL"][/HIGHLIGHT] or [HIGHLIGHT="Oracle8"][/HIGHLIGHT] or [HIGHLIGHT="SQL"][/HIGHLIGHT]

  9. #9
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    There are several ways to present your information...
    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

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  11. #11
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,269
    Mentioned
    197 Post(s)
    Tagged
    2 Thread(s)
    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.

  12. #12
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Considering your requirements I would propose something like:

    Code:
    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
    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

  13. #13
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    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
    
    
    SKU: 1000-01-04-01
    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

  14. #14
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by TomTees View Post
    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.
    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.

    Quote Originally Posted by TomTees View Post
    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...
    Code:
    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
     
     
    SKU: 1000-01-04-01
    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
    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...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  15. #15
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    This (your) 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.
    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

  16. #16
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,269
    Mentioned
    197 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by TomTees View Post
    .....
    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.)
    .....
    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

  17. #17
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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.)

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




    TomTees

  18. #18
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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?

    Quote Originally Posted by TomTrees
    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?
    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:

    Code:
    products_config_values_removed_values 
    
    id      |     products_config_values_id         |       removed_products_config_values_id
    -----------------------------------------------------------------------------------------
    1       |               10                      |                   3
    1       |               10                      |                   4
    1       |               10                      |                   5
    The only code I hate more than my own is everyone else's.

  19. #19
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wonder what r937 thinks about this topic/question...



    TomTees

  20. #20
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.
    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.
    Code:
    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

  21. #21
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by TomTees View Post
    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.

    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 Originally Posted by TomTees View Post

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

    Quote Originally Posted by TomTees View Post
    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.

    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. 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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by 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?
    The only code I hate more than my own is everyone else's.

  23. #23
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, this thread is getting the ol brain workin' overtime!


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

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


    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.
    Well, let's get back to my original question(s) and that will answer your questions/concerns...

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


    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

  24. #24
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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

  25. #25
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by TomTees View Post
    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.

    Quote Originally Posted by TomTees View Post
    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.

    Quote Originally Posted by TomTees View Post
    "Shirt Style" would handle that.
    OK. I guess your shirt style would have hooded, regular, zip-up, etc.


    Quote Originally Posted by TomTees View Post
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •