List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 7 2006 7:00pm
Subject:Re: Many Attributes Required Design Query
View as plain text  
Mark Sargent <mark@stripped> wrote on 04/06/2006 10:45:43 PM:

> SGreen@stripped wrote:
> > I agree with the basic design: one table for all of your basic objects 

> > (shirts, pants, coats, shoes, etc), one table for all of your 
attributes 
> > (see Barry's response), a sku table equating objects (differentiated 
by 
> > their attributes) and their inventory quantities (on hand, 
backordered, 
> > etc), and one more to relate SKU to all applicable attributes.
> > 
> Hi All,
> 
> Shawn, what is a SKU?

I can't explain it any better than this:  
http://en.wikipedia.org/wiki/Stock_Keeping_Unit

To borrow an analogy from biology SKU relates to model as species relates 
to genus. Each SKU uniquely identifies a variation of a basic object. 
Those variations can be due to size changes, colorations, decorations, or 
style. One model number may have dozens of SKUs associated with it. Each 
SKU number is used to track how many of each size/style/etc is in stock or 
on order.

In the grocery store, SKU numbers are the barcodes on the labels. Del 
Monte makes several sizes of canned whole tomatoes (a product). They pack 
those in different sizes of cans. Each can gets its own barcode (SKU) so 
that the store can assign the correct price during checkout. These 
barcodes are also used for inventory control (Imagine the thought process 
of the manager "We have 6 cases of #10 cans but we are down to only 2 case 
of 12oz cans. We probably need to order more 12oz cans.")

You probably need to be able to provide the same level of detail in your 
inventory control system.

> > Each SKU represents one combination of a base object with a particular 
set 
> > of attributes. IT's the SKU number that important for inventory 
control 
> > and that will uniquely identify a size 8 pair of jeans from a size 9 
pair 
> > or a pair of black size 8s from a pair of red size 8s all in the same 
> > style (cut) from the same manufacturer
> > 
> Makers:
> maker_id
> maker_desc
> 
> Products:
> prod_id
> prod_code
> maker_id
> prod_desc
> attri_id
> object_id
> 
> Attributes:
> attri_id
> attri_desc
> 
> Objects:
> object_id
> object_desc
> 
> That is where I got to, as I've never done this kinda design before. 
> Thrown into the deep end, I guess. May I ask for more assistance with 
> this? Where does the quantity go? Any tutorials on this kind of design? 
> Cheers.
> 
> Mark Sargent.

I dont know about tutorials but I Googled SKU and got appx 88 million 
hits. I also Googled for inventory control schema and got over 900 
thousand hits. Some of them may give you some ideas of what your database 
needs to track or how to organize your tables.

Most user mangement front-ends hide a lot of the complexity that goes into 
a database design like this. Make sure you can store and retrieve the 
information you will need in order to answer the questions your users want 
to be able to ask your system. I know that sounds circuituous but if you 
know what your customers want to know, you can create what they need to be 
able to know it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
Many Attributes Required Design QueryMark Sargent6 Apr
  • Re: Many Attributes Required Design QueryBarry6 Apr
    • Re: Many Attributes Required Design QuerySGreen6 Apr
      • Re: Many Attributes Required Design QueryMark Sargent7 Apr
        • Re: Many Attributes Required Design QueryMark Sargent7 Apr
        • Re: Many Attributes Required Design QueryMark Sargent7 Apr
        • Re: Many Attributes Required Design QuerySGreen7 Apr
  • Re: Many Attributes Required Design QueryMartijn Tonies6 Apr