List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 6 2006 1:59pm
Subject:Re: Many Attributes Required Design Query
View as plain text  
Barry <Barry@stripped> wrote on 04/06/2006 03:52:53 AM:

> Mark Sargent wrote:
> > Hi All,
> > 
> > I have the job of creating a DB for an online clothing company. I have 

> > some experience with relational design and sql and php. I'm stumped as 

> > they have products that contain numerous attributes, and are wishing 
to 
> > get away, if possible, with entering separate records for each and 
every 
> > product that has different ones. I will list below some example 
products 
> > and their attributes.
> > 
> > Shirts:
> > neck
> > body_length
> > shoulder_length
> > chest
> > waist
> > sleeve
> > cuff
> > color
> > 
> > Pants:
> > waist
> > under_crotch_length
> > upper_crotch_length
> > upper_crotch_back_length
> > upper_leg_length
> > lower_leg_length
> > 
> > Jacket:
> > body_length
> > shoulder_length
> > chest
> > waist
> > sleeve
> > 
> > Belts:
> > body_length
> > body_width
> > waist_length
> > buckle_length
> > buckle_length
> > holes
> > sleeve_length
> > 
> > below is a link to what they have on Ebay at the moment,
> > 
> > http://cgi.ebay.com/DOLCE-GABBANA-PEACE-
> Jeans-48_W0QQitemZ7757125046QQcategoryZ11483QQtcZphotoQQcmdZViewItem 
> > 
> > 
> > keep in mind, I have only listed attributes above, not the actual 
table 
> > designs. They deal with a lot of variety within brands etc. Anyone got 

> > any solutions for this? I haven't started on a basic design yet, but 
am 
> > looking to do that soon. I f i can minimize their record input, that'd 

> > be great Cheers.
> > 
> > Mark.
> 
> Uhm.
> My solution would be 3 Databases where one has ID,Attrib_object_id, 
> Attrib_name_id, Attrib_value
> 
> And the other two would be an attrib database and an object databse.
> 
> Yep, something like that.
> 

I think you meant to type "tables" not "databases" -  :-0  But we knew 
what you meant...  ;-)

> -- 
> Smileys rule (cX.x)C --o(^_^o)
> Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
> 

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.

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

It's a time-tested inventory control model used by all but the smallest of 
retailers.

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