Product Table and Defining Key

Databases
1

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…
[COLOR=“green”][FONT=“Courier New”]
[B]Design component:

[/B]1000 Rawwr
1001 Nom Nom
1002 Clever Girl
1003 B is for Bacon

[B]Style componenet

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

[B]Color component

[/B]01 White
02 Black
03 Blue
04 Forest

[B]Size component

[/B]SM Small
MD Medium
LG Large
XL X-Large
X2 XX-Large
X3 XXX-Large

[B]Finished Good SKU

[/B]1003-TSM-01-XL

Which tells me there is a “B is for Bacon”, Men’s T-Shirt, White, Extra-Large shirt.
[/FONT][/COLOR]

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

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

3

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?

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

4

i know :slight_smile:

5

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.

6

Strange… Your reply didn’t appear until just now, even though it looks like you made it a while ago?!

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?! :confused:

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

7

if this is what yo umean by “dynamic” then sure, this is okay

yep

:slight_smile:

8

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

:slight_smile:

9

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