small medium large xlarge

Photo_2_pragsmall
15 Oct 2008, 16:09
Paul Nelligan (17 posts)

Hi all

in the active record chapter on table joins, the following code is given:

def self.up
  create_table :products do |t|
    t.string :title
    # ...
  end
  create_table :categories do |t|
    t.string :name
    # ...
  end
  create_table :categories_products, :id => false do |t|
    t.integer :product_id
    t.integer :category_id
  end
  # Indexes are important for performance if join tables grow big
  add_index :categories_products, [:product_id, :category_id], :unique => true
  add_index :categories_products, :category_id, :unique => false
end

Overall I understand the final goal here, I’m already familiar with table joins. But, I don’t think the book adequately explains the last two lines with ‘add_index’… I understand that the first statement informs us that the combination of :product_id and :category_id is unique, although I don’t quite understand why this is necessary since the id field in both the products and categories tables are going to be unique anyway… but why is :category_id set to :unique => false here??

can someone please illuminate me?

thanks

Paul

Generic-user-small
15 Oct 2008, 22:45
James West (104 posts)

Hi, Just a stab in the dark as I haven’t read that chapter yet but here gows in the hope that it helps.

I guess your questions lean towards database design rather than ruby code which is probably why your questions have not been answered.

Why is the the category_id set to unique?

I assume that the purpose of the table is create a many to many relationship between categories and products. To allow products to live in more than one category but for a join table that could cause problems if the index is not set properly.

If you set the index on the category field to be unique then you could not have a priduct live in different categories

Take the following example

With a site selling jewellery for example you might have a situation where you would want categories of bracelets, earrings for example but you migh also want categories of jewellery with gem stones and jewellery without gem stone.

A bracelet with a gem stone would want to appear in both categories but this would not be possible if the category index was unique. It all depends on how you want to design your database.

The combination of both keys HAS to be unique otherwise you could go on forever and have your product appearing multiple times in the same category.

It really is basic database design as I say.

A many to many join table has only the keys for the primary indexes on both tables and this combination is ALWAYS unique

One of the individual keys needs to be not unique otherwise there is no point to having the join table.

Hope that makes sense.

It may help to write down a couple of products and a couple of categories on a piece of paper and see what would happen to the join table and whether or not it would prevent combinations that didn’t make sense if the key was unique. You’ll need to do this anyway when you are designing a join table for your own purposes as you will need to work out what the purpose is.

I hope I have not rambled on too much and that all is now a little clearer

James

You must be logged in to comment