thr3ads.net - Rails - [newbie] table relationships [Oct 2005]

If this information is useful, please help other people find it:
Share via:

dave

2005-Oct-29 18:20 UTC

[newbie] table relationships

not really a rails question, but...  i''ve apparently hit a brick wall
in my
understanding of table relationships.  Say I have the following:

widgets:

id     widget
----------------------
1      sprocket
2      cog
3      hammer
4      thingamajig

qualifiers:

id     qualifier
----------------------
1      red
2      blue
3      green
4      smooth
5      crunchy
6      sharp

widgets_qualifiers:

widget_id qualifier_id
----------------------
1         1
2         3
2         5
3         2
3         6

         
Basically I have a many-to-many relationship between 
widgets and qualifiers, so that I can assign a number
of different properties to an entry in the widget
table, i.e. a smooth, blue hammer.  (Let''s ingore for
the moment that with this setup, it''s possible to 
have a widget be both red and blue which makes no sense)

I understand that so far.  What I want now is the ability
to assign a unique id to a widget posessing specific qualifiers.  
IOW, a way to reference a "green, smooth cog" as well as a 
"red, crunchy cog" uniquely.  So in another table I can reference
this unique identifier in a one-to-many relationship.  (For
example in an "open orders" table where we''re waiting for one
specific widget for the order to be complete.  Not the most
realistic example, but good enough for explanation purpose)
This is where I get stuck.

Because of the possibility of variability of the number of
qualifiers of any particular widget, I don''t see how I can 
normalize this design.  I think I''m on the right track with
the many-to-many relationship, but like I said I''ve reached
the upper limit of my conceptualization abilities on this.

any suggestions on how i can uniquely identify a widget with
varying properties?  or is this not even possible?

thanks!

Jan Prill

2005-Oct-29 19:26 UTC

head link

Re: [newbie] table relationships

Hi, Dave,

shouldn''t something like this work? You would be referencing a
qualified widget...

widget

has_many :qualified_widgets

(id, widget)

----------------

qualified_widget

belongs_to :widget
has_and_belongs_to_many :qualifiers

(id, widget_id)

----------------

qualifier:

has_and_belongs_to_many :qualified_widgets


(qualified_widgets_qualifiers: (qualified_widget_id, qualifier_id))

regards
Jan Prill


dave wrote:
>not really a rails question, but...  i''ve apparently hit a brick
wall in my
>understanding of table relationships.  Say I have the following:
>
>widgets:
>
>id     widget
>----------------------
>1      sprocket
>2      cog
>3      hammer
>4      thingamajig
>
>qualifiers:
>
>id     qualifier
>----------------------
>1      red
>2      blue
>3      green
>4      smooth
>5      crunchy
>6      sharp
>
>widgets_qualifiers:
>
>widget_id qualifier_id
>----------------------
>1         1
>2         3
>2         5
>3         2
>3         6
>
>         
>Basically I have a many-to-many relationship between 
>widgets and qualifiers, so that I can assign a number
>of different properties to an entry in the widget
>table, i.e. a smooth, blue hammer.  (Let''s ingore for
>the moment that with this setup, it''s possible to 
>have a widget be both red and blue which makes no sense)
>
>I understand that so far.  What I want now is the ability
>to assign a unique id to a widget posessing specific qualifiers.  
>IOW, a way to reference a "green, smooth cog" as well as a 
>"red, crunchy cog" uniquely.  So in another table I can reference
>this unique identifier in a one-to-many relationship.  (For
>example in an "open orders" table where we''re waiting for
one
>specific widget for the order to be complete.  Not the most
>realistic example, but good enough for explanation purpose)
>This is where I get stuck.
>
>Because of the possibility of variability of the number of
>qualifiers of any particular widget, I don''t see how I can 
>normalize this design.  I think I''m on the right track with
>the many-to-many relationship, but like I said I''ve reached
>the upper limit of my conceptualization abilities on this.
>
>any suggestions on how i can uniquely identify a widget with
>varying properties?  or is this not even possible?
>
>thanks!
>
>
>
>_______________________________________________
>Rails mailing list
>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>
>  
>

Jan Prill

2005-Oct-29 19:30 UTC

head link

Re: [newbie] table relationships

Just to clear it up: It would then be four tables:

widgets (id, widget), qualified_widget (id, widget_id), qualifier (id, 
qualifier), qualified_widgets_qualifiers: (qualified_widget_id, 
qualifier_id)

regards
Jan

Jan Prill wrote:
> Hi, Dave,
>
> shouldn''t something like this work? You would be referencing a 
> qualified widget...
>
> widget
>
> has_many :qualified_widgets
>
> (id, widget)
>
> ----------------
>
> qualified_widget
>
> belongs_to :widget
> has_and_belongs_to_many :qualifiers
>
> (id, widget_id)
>
> ----------------
>
> qualifier:
>
> has_and_belongs_to_many :qualified_widgets
>
>
> (qualified_widgets_qualifiers: (qualified_widget_id, qualifier_id))
>
> regards
> Jan Prill
>
>
> dave wrote:
>
>> not really a rails question, but...  i''ve apparently hit a
brick wall
>> in my
>> understanding of table relationships.  Say I have the following:
>>
>> widgets:
>>
>> id     widget
>> ----------------------
>> 1      sprocket
>> 2      cog
>> 3      hammer
>> 4      thingamajig
>>
>> qualifiers:
>>
>> id     qualifier
>> ----------------------
>> 1      red
>> 2      blue
>> 3      green
>> 4      smooth
>> 5      crunchy
>> 6      sharp
>>
>> widgets_qualifiers:
>>
>> widget_id qualifier_id
>> ----------------------
>> 1         1
>> 2         3
>> 2         5
>> 3         2
>> 3         6
>>
>>         Basically I have a many-to-many relationship between widgets 
>> and qualifiers, so that I can assign a number
>> of different properties to an entry in the widget
>> table, i.e. a smooth, blue hammer.  (Let''s ingore for
>> the moment that with this setup, it''s possible to have a
widget be
>> both red and blue which makes no sense)
>>
>> I understand that so far.  What I want now is the ability
>> to assign a unique id to a widget posessing specific qualifiers.  
>> IOW, a way to reference a "green, smooth cog" as well as a
"red,
>> crunchy cog" uniquely.  So in another table I can reference
>> this unique identifier in a one-to-many relationship.  (For
>> example in an "open orders" table where we''re
waiting for one
>> specific widget for the order to be complete.  Not the most
>> realistic example, but good enough for explanation purpose)
>> This is where I get stuck.
>>
>> Because of the possibility of variability of the number of
>> qualifiers of any particular widget, I don''t see how I can
normalize
>> this design.  I think I''m on the right track with
>> the many-to-many relationship, but like I said I''ve reached
>> the upper limit of my conceptualization abilities on this.
>>
>> any suggestions on how i can uniquely identify a widget with
>> varying properties?  or is this not even possible?
>>
>> thanks!
>>
>>
>>
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>  
>>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Jeremy Kemper

2005-Oct-29 20:40 UTC

head link

Re: [newbie] table relationships

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Oct 29, 2005, at 11:20 AM, dave wrote:> Basically I have a many-to-many relationship between
> widgets and qualifiers, so that I can assign a number
> of different properties to an entry in the widget
> table, i.e. a smooth, blue hammer.  (Let''s ingore for
> the moment that with this setup, it''s possible to
> have a widget be both red and blue which makes no sense)
>
> I understand that so far.  What I want now is the ability
> to assign a unique id to a widget posessing specific qualifiers.
> IOW, a way to reference a "green, smooth cog" as well as a
> "red, crunchy cog" uniquely.  So in another table I can reference
> this unique identifier in a one-to-many relationship.  (For
> example in an "open orders" table where we''re waiting
for one
> specific widget for the order to be complete.  Not the most
> realistic example, but good enough for explanation purpose)
> This is where I get stuck.
>
> Because of the possibility of variability of the number of
> qualifiers of any particular widget, I don''t see how I can
> normalize this design.  I think I''m on the right track with
> the many-to-many relationship, but like I said I''ve reached
> the upper limit of my conceptualization abilities on this.
>
> any suggestions on how i can uniquely identify a widget with
> varying properties?  or is this not even possible?
Hi Dave,

This is commonly modeled as Products (hammer) and Variations (smooth,  
blue hammer).  Products are all the kinds of things you sell, whereas  
Variations are the actual, physical items that you stock on your  
shelves.  They are often referred to by SKU (stock-keeping unit).

So a Product (hammer) has a set of Attributes you may choose from  
(smooth, blue, rough, red) and a Variation is a Product with a subset  
of these Attributes.

Logically:

Product *---* Attribute
      \         *
       \       /
        *     *
       Variation


The tables:

products
  id primary key
  name

product_attributes
  product_id references products(id)
  attribute_id references attribute(id)

attributes
  id primary key
  name

variations
  id primary key
  product_id references products(id)
  sku

variation_attributes
  variation_id references variation(id)
  attribute_id references attribute(id)
  primary key (variation_id, attribute_id)


The models:

class Attribute < AR::Base
end

class Product < AR::Base
   has_many :variations
   has_and_belongs_to_many :attributes, :join_table =>  
''product_attributes''
end

class Variation < AR::Base
   belongs_to :product
   has_and_belongs_to_many :attributes, :join_table =>  
''variation_attributes''
end


This is just one way of modeling products with variations.  Hope this  
helps.

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDY94/AQHALep9HFYRAhOcAKC6Tr1kt08aDfEl7UebLgEY4HYZWwCdFP4s
E/oHjmTwq4AQqL6Rnioo8S8=rUrS
-----END PGP SIGNATURE-----

Jeremy Kemper

2005-Oct-29 20:58 UTC

head link

Re: [newbie] table relationships

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Oct 29, 2005, at 1:40 PM, Jeremy Kemper wrote:> This is commonly modeled as Products (hammer) and Variations  
> (smooth, blue hammer).  Products are all the kinds of things you  
> sell, whereas Variations are the actual, physical items that you  
> stock on your shelves.  They are often referred to by SKU (stock- 
> keeping unit).
>
> So a Product (hammer) has a set of Attributes you may choose from  
> (smooth, blue, rough, red) and a Variation is a Product with a  
> subset of these Attributes.
And, you know what-- I modeled it wrong!

Variations have a subset of ProductAttributes.  You wouldn''t want a  
Variation setting its attributes to those available to another Product.

> Logically:
>
> Product *---* Attribute
>      \         *
>       \       /
>        *     *
>       Variation
Product --* ProductAttribute --- Attribute
      \        *
       \      /
        *    *
       Variation

>
> The tables:
>
> products
>  id primary key
>  name
>
> product_attributes
>  product_id references products(id)
>  attribute_id references attribute(id)
>
> attributes
>  id primary key
>  name
>
> variations
>  id primary key
>  product_id references products(id)
>  sku
>
> variation_attributes
>  variation_id references variation(id)
>  attribute_id references attribute(id)
>  primary key (variation_id, attribute_id)
>

products
  id primary key
  name

attributes
  id primary key
  name

product_attributes
  id primary key
  product_id references products(id)
  attribute_id references attributes(id)

variations
  id primary key
  product_id references products(id)
  sku

variation_attributes
  variation_id references variations(id)
  product_attribute_id references product_attributes(id)

> The models:
>
> class Attribute < AR::Base
> end
>
> class Product < AR::Base
>   has_many :variations
>   has_and_belongs_to_many :attributes, :join_table =>  
> ''product_attributes''
> end
>
> class Variation < AR::Base
>   belongs_to :product
>   has_and_belongs_to_many :attributes, :join_table =>  
> ''variation_attributes''
> end

class Attribute < AR::Base
end

class Product < AR::Base
   has_many :variations
   has_many :product_attributes, :class_name =>
''ProductAttribute''
end

class ProductAttribute < AR::Base
   belongs_to :attribute
   belongs_to :product
end

class Variation < AR::Base
   belongs_to :product
   has_and_belongs_to_many :product_attributes, :join_table =>  
''variation_attributes''
end


Example:

# Create a hammer
hammer = Product.create(:name => ''hammer'')

# And some attributes
smooth = Attribute.create(:name => ''smooth'')
blue   = Attribute.create(:name => ''blue'')

# Associate the attributes with the hammer
smooth_hammer = ProductAttribute.create(:product =>  
hammer, :attribute => smooth)
blue_hammer   = ProductAttribute.create(:product =>  
hammer, :attribute => blue)

# Create a variation and associate it with the hammer attributes
smooth_blue_hammer = Variation.create(:product => hammer)
smooth_blue_hammer.product_attributes << [smooth_hammer, blue_hammer]


> This is just one way of modeling products with variations.  Hope  
> this helps.
Hope this helps a little more!  It''s also served as an example of  
refactoring a has_and_belongs_to_many into simple has_many + belongs_to.

Best,
jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDY+JeAQHALep9HFYRAk2TAJ9eT4JHbnFb0Nf9K/lh6NuPEhn0fQCgqKFb
9dDreTpfmREUArZlakgcSdI=JowT
-----END PGP SIGNATURE-----

Rails - Oct 2005 - [newbie] table relationships

[newbie] table relationships

Re: [newbie] table relationships

Re: [newbie] table relationships

Re: [newbie] table relationships

Re: [newbie] table relationships