Mark Sargent <[EMAIL PROTECTED]> wrote on 04/06/2006 10:45:43 PM: > [EMAIL PROTECTED] 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
