SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 31 of 31
  1. #26
    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
    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

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

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

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

    Quote Originally Posted by TomTees View Post
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomTees View Post
    Well, I've been taught that every table should have an auto-increment ID field (for performance)
    sorry, you were taught wrong

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #29
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally Posted by TomTees View Post
    Well, I've been taught that every table should have an auto-increment ID field (for performance)
    Quote Originally Posted by r937 View Post
    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

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

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

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

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

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

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


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
  •