Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35

    Defining Primary Key/SKU

    I could use some advise on creating a primary key for my Products table.

    This is for a start-up business that sells silk-screened T-Shirts.

    My wife and I have decided to treat printed shirts as "Finished Goods", but we also want to keep track of the component parts.

    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.

    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
    Helping my wife to start an online business selling T-Shirts.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1.) yes
    2.) no
    3.) with a VARCHAR column

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    1) No
    2) Maybe
    3) Don't

    On a more serious note, keys come in two flavors: natural and surrogate. What you're trying to create in your SKU is called a "smart key" which is sort of a hybred between natural and surrogate, and these are a "do it yourself problem" in many ways. If you want to use natural keys, then use the natural keys. If you want to use surrogate keys, use surrogates that are purely to support your application, not derived from some other source or sources. Don't try to straddle the fence and be kind-of-natural-kind-of-surrogate, that will hurt!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    What you're trying to create in your SKU is called a "smart key" which is sort of a hybred between natural and surrogate, and these are a "do it yourself problem" in many ways.
    i've never understood this line of thinking

    let's suppose the product table has several columns which are foreign keys to designs table, styles table, and colours table

    wouldn't the composite of those columns be a natural key?

    okay, so the string with the dashes in it is a computed column

    where's the pain?

    okay, so some databases don't have a computed column concept, you'd use an actual but redundant string column

    where's the pain?

    okay, now remove the foreign key columns and leave only the string column

    my question is: isn't this still a natural key?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The pain is in the TSM, it isn't a T-Shirt nor a small nor a Men but it is a hybred of the whole lot of them. If the key was Small Men's T-Shirt (with or without gratuitous punction to "keyify" it), that would be a fine natural key.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let me see if i get what you're saying

    instead of this --
    Code:
    Style componenet
    --------------------
    TSM T-Shirt (male)
    you want it to be this --
    Code:
    Style
    --------------------
    T-Shirt (male)
    but you'd be willing to accept this --
    Code:
    Style
    --------------------
    t-shirt-male
    tell me again please what's a natural key?

    ze glasses, zey do nossink

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35
    Quote Originally Posted by Pat Phelan View Post
    The pain is in the TSM, it isn't a T-Shirt nor a small nor a Men but it is a hybred of the whole lot of them. If the key was Small Men's T-Shirt (with or without gratuitous punction to "keyify" it), that would be a fine natural key.

    -PatP
    I think you may be misinterpreting my preliminary naming scheme...

    In this SKU: "1003-TSM-01-SM"

    "1003" stands for the "Nom Nom" design.
    "TSM" stands for "T-Shirt (male)"
    "01" stands for "White"
    "SM" stands for "Small"

    I realize that one dig against creating your own naming scheme for PK's is that "life happens" and "Someday when things change or you run out of available numbers, YOU'LL BE SORRY!!!"

    This is certainly a possibility, but I tried taking a mathematical approach to things (plus this was just my first, rough, attempt at a naming convention.)


    Silk-Screen Design:
    A four digit number would basically allow up to 10,000 different designs. (I will probably change this to 5 digits to allow up to 100,000 different Silk-Screen Designs. By then, my wife and I will be retired! )


    Shirt Style:
    Right now I have this...

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

    Three digits is plenty for shirt styles, although choice of letters is always subjective.


    Shirt Color:
    I was going to use abbreviations, but I chose a two-digit number because it is less likely to run out or back-fire. Two-digits gives up to 100 different shirt colors. We are starting this business to make $$$ off my wife's creative "designs". It would be bad business to offer more than 100 shirt colors, and even if we could, I think my wife and I agree that would be silly.


    Shirt Size:
    This is what I came up for shirt sizes...

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

    This again seems pretty certain, and for customers that need a "X10" (i.e. XXXXXXXXXX-Large) shirt, we would send them down to Bob's Tent & Awning.

    ----

    Notice that I also staggered the SKU components (i.e. "1003-TSM-01-SM") from NUMBER to TEXT to NUMBER to TEXT in case using hyphens is ruled out (e.g. "1003TSM01SM").

    This should make the SKU's still fairly readable, even without hyphens.

    In my humble opinion, this is a pretty solid naming scheme and should create SKU's that are very scalable, and also very self-identifying, which will help everyone from Customers to Customer Service Reps to people in Shipping and Billing and the Administrator and my wife and I!!

    About the only SKU component that I might re-think later, would be wanting the "Design" portion to be more descriptive. However, since brevity counts, I think using a straight 4 or 5 digit number is safest here. And any meta-data will just be stored in non-key fields.

    So, that is a little more of my thought-process.

    I'm certainly no expert like you guys here, but I'd like to think my SKU idea is fairly well thought out, scalable, and will help us to run our business more efficiently.

    What do you think?



    TomTees

    P.S. I could always throw in an "ID" field that uses an AutoIncrement Integer just as a "fail-safe", although I think having records identified by 1, 2, 3, 4, ..., n is about as useless of things come.
    Helping my wife to start an online business selling T-Shirts.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Show me where TSM sits in terms of a natural key. T-Shirt is a natural key for the type of item. Small is a natural key for the size. Men's is a natural key for the audience. TSM is a bit of "voodoo that you do" to create a smart key pseudo-surrogate, but it isn't natural in any sense that I recognize.

    -patP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Actually Tom, I'm a "belt and suspenders" kind of guy when it comes to keys. I believe that (with possibly a few exceptions), every table should have both a natural key and a surrogate key defined.

    The Natural Key is what I show to the user, the Surrogate Key is the Primary Key and it is what my database/application/etc use. Users, and most especially legislators sometimes demand to change Natural Keys... I do everything I can to keep any of them from realizing that my Surrogate Keys even exist.

    I prefer to use guid values instead of integers because guids scale better.

    I tend to work with very large systems on more than one continent, so I often need to use both scale-up and scale-out together to solve many of my problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35
    Quote Originally Posted by Pat Phelan View Post
    Show me where TSM sits in terms of a natural key. T-Shirt is a natural key for the type of item. Small is a natural key for the size. Men's is a natural key for the audience. TSM is a bit of "voodoo that you do" to create a smart key pseudo-surrogate, but it isn't natural in any sense that I recognize.

    -patP
    Not sure if you saw my last (long) post, but I think you are reading "TSM" incorrectly.

    "TSM" is just short for "T-Shirt (men's)" - there is no "small" in there.

    And, yes, a Men's T-Shirt is completely "natural" in that it is cut differently than a Female's T-Shirt. So there is no meshing of Style and Gender.

    One should think of Men's T-Shirts and Women's T-Shirts as different as Baseballs and Softballs and Tennis Balls.


    TomTees
    Helping my wife to start an online business selling T-Shirts.

  11. #11
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35
    Quote Originally Posted by Pat Phelan View Post
    Actually Tom, I'm a "belt and suspenders" kind of guy when it comes to keys. I believe that (with possibly a few exceptions), every table should have both a natural key and a surrogate key defined.
    I don't mind adding in an "ID" using AutoIncrement, but I've always question the benefit of such a PK because you could have...

    1 John Doe Chicago, IL
    2 John Doe Chicago, IL
    3 John Doe Chicago, IL
    4 John Doe Chicago, IL
    5 John Doe Chicago, IL
    6 John Doe Chicago, IL



    If my Products table looked like this...
    Code:
    SKU (PK)	DESIGN		STYLE			COLOR		SIZE
    ---------------	----------	------------------	------		------		
    1003-TSM-01-SM	"Nom Nom"	T-Shirt (Men's)		White		Small
    1003-TSM-01-MD	"Nom Nom"	T-Shirt (Men's)		White		Medium
    1003-TSM-01-LG	"Nom Nom"	T-Shirt (Men's)		White		Large
    1003-TSM-02-SM	"Nom Nom"	T-Shirt (Men's)		Black		Small
    1003-TSM-02-MD	"Nom Nom"	T-Shirt (Men's)		Black		Medium
    1003-TSM-02-LG	"Nom Nom"	T-Shirt (Men's)		Black		Large
    1003-TSF-01-SM	"Nom Nom"	T-Shirt (Women's)	White		Small
    1003-TSF-01-MD	"Nom Nom"	T-Shirt (Women's)	White		Medium
    1003-TSF-01-LG	"Nom Nom"	T-Shirt (Women's)	White		Large
    1003-TSF-02-SM	"Nom Nom"	T-Shirt (Women's)	Black		Small
    1003-TSF-02-MD	"Nom Nom"	T-Shirt (Women's)	Black		Medium
    1003-TSF-02-LG	"Nom Nom"	T-Shirt (Women's)	Black		Large
    Then not only would I have Natural Keys, but I would also have a self-identifying Surrogate Key that is more agile than combining all of the Natural Keys but my useful than a sequential Surrogate Key.


    I prefer to use guid values instead of integers because guids scale better.

    I tend to work with very large systems on more than one continent, so I often need to use both scale-up and scale-out together to solve many of my problems.

    -PatP
    I cannot speak to the guid since I don't really remember what they are.



    TomTees
    Helping my wife to start an online business selling T-Shirts.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    1.) Maybe.
    2.) Yes.
    3.) Sure.

    Quote Originally Posted by TomTees View Post
    In this SKU: "1003-TSM-01-SM"

    "1003" stands for the "Nom Nom" design.
    Uhm....I thought 1003 stood for "B is for bacon"? Which is, of course, the problem with exposing surrogate keys to the user...

    Quote Originally Posted by TomTees View Post
    1000 Rawwr
    1001 Nom Nom
    1002 Clever Girl
    1003 B is for Bacon
    Seriously, you are as likely to make typos and mistakes entering in a natural key composed of surrogate keys as you are to when entering a simple surrogate key (which really, you shouldn't be doing either).
    So what is the big business case for having a pseudo-descriptive SKU?

    Also, I will build your entire database for you if you will introduce me to the Irish Girl. http://www.bustedtees.com/irish#female
    Last edited by blindman; 05-28-10 at 16:24.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    May 2008
    Posts
    277
    I'm not quite sure what the fuss is. I think the keys for your individual components are fine.

    So presumably, your shirt table looks something like:
    Code:
    CREATE TABLE shirt (
        design_num INT REFERENCES design,
        style_code CHAR(3) REFERENCES style,
        color_num INT REFERENCES color,
        size_code CHAR(2) REFERENCES size,
        ...
    In which case your natural key is simply:
    Code:
    PRIMARY KEY (design_num, style_code, color_num, size_code)
    Deriving an SKU from this can be as simple as
    Code:
    SELECT
        design_num || '-' || style_code || '-' || color_num || '-' || size_code AS sku,
        ...
    FROM shirt

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    So how do I know your TSM from Tivoli Storage Manager? The problem is that there's no occurance of "TSM" anywhere in the real world, which means that "TSM" is a Surrogate Key.

    My objection is to the Smart Key. If your key was "Nom Nom, Men's T, White, Small" I'd agree that it was a mangled but passible Natural Key value. I'd still say that "1003-TSM-01-SM" was a problem agressively in search of its next victim because in another Smart Key schema that same key means Amphibius (paddle) tire, 20 inch truck casing mount, hard rubber finish with Service Manager understrait". If those Composite keys were made up of the Natural Keys that underly them, then the resulting Composite Key would be a Natural Key. If the Composite Key is made up of relatively random letters and digits that resemble a Natural Key to a well trained observer, then that composite is a Surrogate Key and a poor Surrogate Key in my opinion.

    If you can hand something to me with a list of twenty possible values for the key and I can walk down the street and get more than eighty percent of the people I meet who can identify the item to correctly identify the key, then I'll conceed that it is a Natural Key. If twenty percent or more of the population that can correctly identify an item can't pick out the items key from a list, then the list isn't made up of Natural Keys. By that defintion, I think that "1003-TSM-01-SM" is a Surrogate Key.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35
    Quote Originally Posted by blindman View Post
    1.) Maybe.
    2.) Yes.
    3.) Sure.
    Way to make a guy scroll!!


    Uhm....I thought 1003 stood for "B is for bacon"? Which is, of course, the problem with exposing surrogate keys to the user...
    Oh, I was just mixed those up in my head. (Besides, that isn't the point of focus here.)


    [quote]
    Seriously, you are as likely to make typos and mistakes entering in a natural key composed of surrogate keys as you are to when entering a simple surrogate key (which really, you shouldn't be doing either).[quote]

    ************************
    Well, 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. (Maybe people who buy "Black" shirts only buy "Large" sizes?! And most guys wouldn't buy a "Pink" shirt.)

    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?

    So to address your concern, SKU's would be build by the form and thus not be prone to type-o's

    ************************


    So what is the big business case for having a pseudo-descriptive SKU?
    I don't know. It just seems like a very common architecture for SKU's across the business world over the years.

    Even all-numeric UPC's probably have some hidden coding in them.

    The main benefit is that is "short-hand" for a finished product for all who come into contact with a shirt. It would help people in Customer Service, Fulfillment, the Administrator, my wfie and I, and maybe even Customers.

    It also seems like it might be handy when querying things since you just have to go off of one field.


    Also, I will build your entire database for you if you will introduce me to the Irish Girl. BustedTees - Irish I were Drunk
    I'm a happily married man... No comment!


    TomTees
    Helping my wife to start an online business selling T-Shirts.

Posting Permissions

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