SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Product Table and Defining Key

    Looking for some advise on creating a primary key for my Product table, which contains silk-screened shirts.

    I would like to create a Primary Key (SKU) that not only uniquely identifies a particular "Finished Good", but that is self-identifying and allows anyone looking at the SKU to quickly know what components make it up.

    r937 had a link to a great article that talked about why AutoNumber PK's can be dangerous in that that have no tie to a physical record.

    That is why creating a Primary Key/SKU that is self-identifying and that relates to a shirt's 4 main components (i.e. Silk-Screen Design, Shirt Style, Shirt Size, and Shirt Color) seems like a good idea.

    My only concern is whether creating a Surrogate PK will bog down my table and/or database because of its size.

    I was thinking of a SKU layout like this...

    Design component:
    --------------------
    1000 Rawwr
    1001 Nom Nom
    1002 Clever Girl
    1003 B is for Bacon


    Style componenet
    --------------------
    TSM T-Shirt (male)
    TSF T-Shirt (female)
    LSM Long Sleeve (male)
    LSF Long Sleve (female)
    SSM Sweatshirt (male)
    SSF Sweatshirt (female)


    Color component
    --------------------
    01 White
    02 Black
    03 Blue
    04 Forest


    Size component
    --------------------
    SM Small
    MD Medium
    LG Large
    XL X-Large
    X2 XX-Large
    X3 XXX-Large


    Finished Good SKU
    --------------------
    1003-TSM-01-XL

    Which tells me there is a "B is for Bacon", Men's T-Shirt, White, Extra-Large shirt.



    QUESTIONS:
    ----------------

    1.) Is that okay by itself?

    2.) Should I use an AutoNumber and just index the SKU instead?

    3.) For SKU readablility, I want hyphens (-) between the parts. How do I handle that?



    TomTees

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    1.) yes
    2.) no
    3.) with a VARCHAR column

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

  3. #3
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Strange... Your reply didn't appear until just now, even though it looks like you made it a while ago?!


    Quote Originally Posted by TomTees View Post
    QUESTIONS:
    ----------------

    1.) Is that okay by itself?

    2.) Should I use an AutoNumber and just index the SKU instead?

    3.) For SKU readablility, I want hyphens (-) between the parts. How do I handle that?


    TomTees

    Quote Originally Posted by r937 View Post
    1.) yes
    2.) no
    3.) with a VARCHAR column

    So you are saying that it is okay to generate a primary key (pk) "on the fly" when a new record is added to the Product table?

    (I had envisioned a form where you "Build a SKU", and based on the values you select in drop-down boxes, it would then take those values and dynamically create the primary key before doing an INSERT into the Product table. This, of course, would make those AutoNumber-loving people crazy!!)


    On the third question, are you saying that it is okay to add hyphens into the SKU and primary key field?!

    Any of the Microsoft DB Developers I used to know would flip out at even the thought, since they would say, "Why would you want to store extraneous information in the PK field. Just dynamically add the hyphens later during the displaying of data!!"



    TomTees

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomTees View Post
    So you are saying that it is okay to generate a primary key (pk) "on the fly" when a new record is added to the Product table?
    if this is what yo umean by "dynamic" then sure, this is okay

    Quote Originally Posted by TomTees View Post
    On the third question, are you saying that it is okay to add hyphens into the SKU and primary key field?!
    yep

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

  5. #5
    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 r937 View Post
    Originally Posted by TomTees View Post
    So you are saying that it is okay to generate a primary key (pk) "on the fly" when a new record is added to the Product table?
    if this is what you mean by "dynamic" then sure, this is okay
    Stepping back for a second...

    The workflow I have been considering is this...

    I would have a simple Administrator page where the left side of the webpage displays all current SKU's (i.e. Products). There would be some filtering mechanism (e.g. drop-down menus) to reduce the SKU's being shown as there could be ten of thousands.

    Maybe I would select the design "Nom Nom" and the left side of the page would show me this...

    1001-TSM-01-SM
    1001-TSM-01-MD
    1001-TSM-01-LG
    1001-TSM-02-SM
    1001-TSM-02-MD
    1001-TSM-02-LG
    1001-TSF-01-SM
    1001-TSF-01-MD
    1001-TSF-01-LG
    1001-TSF-02-SM
    1001-TSF-02-MD
    1001-TSF-02-LG

    Which basically means that I have the "Nom Nom" design in T-Shirts (i.e. "TS") for Men and Women (i.e. "M" and "F"), in White and Black (i.e. "01", "02"), in Small, Medium, and Large (i.e. "SM", "MD", "LG").

    Knowing this, maybe we would want to add Red as a color for just Men's T-Shirts.

    So seeing our current inventory of "Nom Nom" shirts, I would then go to the right half of the Administrative webpage, and I would choose Design = "Nom Nom" (1001), Style = "Men's T-Shirt" (TSM), Color = "Red" (03), Size = "Small" (SM) from the drop down boxes.

    This would build a the SKU...

    "1001-TSM-03-SM"

    The system would verify that such a SKU does not already exist.

    And after clicking "Create SKU", the system would INSERT a new record in the Products table with a primary key = "1001-TSM-03-SM" and the corresponding other fields filled out.

    I would repeat this process and next create a similar Medium and Large shirt.

    This may seem like a rather laborious process, but - as I see it - would allow us to "tweak" our inventory to match what people are buying. (Since most Americans are soooo F-A-T, it might turn out we never sell "Small" shirts?! Or maybe it turns out that females tend not to buy "Red" shirts for whatever reason.)

    When we first populate the Products table, I would likely just use a spreadsheet to quickly create a large number of combinations we think will sell, but moving forward, what I described above would give control to the Administrator.

    Does that make sense?

    Does "dynamically creating SKUs" make sense based on what I described above?

    Is there anyway I could get into trouble with the database (and data integrity) doing what I described?

    Quote Originally Posted by r937 View Post
    Originally Posted by TomTees View Post
    On the third question, are you saying that it is okay to add hyphens into the SKU and primary key field?!
    yep
    So there is no problem adding all those hyphens into a Primary Key field?

    Why do you think that having 3 extra hyphens is not extraneous or breaking any rules?

    (It seems okay as long as it doesn't freak out the database like spaces do, but I know when someone once asked that question in years past on a Microsoft usergroup, the resident "gurus" flipped out?!)

    Just trying to better understand the mechanics of why it is or is not okay.

    Thanks,



    TomTees

    P.S. You are a man of few words, r937!!!!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomTees View Post

    You are a man of few words, r937!!!!

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

  7. #7
    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 r937 View Post
    i know
    Grrrr...

    So what did you think about my L-O-N-G response (i.e. #5) to your pithy response last night?

    Does my workflow and thought process for wanting to "build SKU's" seem reasonable?

    Here comes a "Yes/No" response!

    How would you approach all of this?


    TomTees

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,830
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    If each part of that hyphenated key is always the same length then you could store the keys without the hyphens and just add them when displaying them.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  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 felgall View Post
    If each part of that hyphenated key is always the same length then you could store the keys without the hyphens and just add them when displaying them.
    Yes, each part is fixed-width.

    So, what would be the difference from an implementation and performance standpoint from having a one-field Surrogate PK (including hyphens) versus have 4-fields that combined together form a Surrogate PK??

    Also, felgall, what do you think about my lengthy discussion above about how I envisioned building SKU's??

    Isn't that a pretty common workflow for any "Finished Good" that is built from components but where knowing the components is still important?



    TomTees


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
  •