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

1.) yes
2.) no
3.) with a VARCHAR column

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

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?

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

okay, let me see if i get what you're saying

instead of this --you want it to be this --but you'd be willing to accept this --tell me again please what's a natural key?

ze glasses, zey do nossink

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.

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

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

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

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...
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 cannot speak to the guid since I don't really remember what they are.



TomTees

1.) Maybe.
2.) Yes.
3.) Sure.

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

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

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:
In which case your natural key is simply:
Deriving an SKU from this can be as simple as

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

Way to make a guy scroll!!


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

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


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.


I'm a happily married man... No comment!


TomTees