0

I've worked with mysql for some time but have never had to do anything anywhere near complicated. I'm working on a new project and need a little push in the right direction. Lets say I want to have a table that stores cats. Lets say I want to store the cats name, and food that the cat likes. Then lets say I want to query all cats that like fish, milk, and mice. I don't want to have the 'cats' table have rows for 'fish' 'milk' 'mice' that can be yes or no or 1 or 0. I think want I'm looking for is having a separate 'foods' table and then use the join statement. But I have no clue where to start looking or what to search for. Another way to think about this would be if you had a blog, and wanted posts to have categories. How would I store which posts are in which categories, and how would I query for posts by category?

I realize this is probably a very basic question, and would be happy with even a link to a tutorial explaining the structure / commands needed to pull this off! Thanks a lot!

Improve this question
1
  • 1
    What you have is a many-to-many relationship between cat and food, which is implemented by means of a junction table, such as the cat_food table in simshaun's answer. Commented Dec 19, 2010 at 6:35

3 Answers 3

Reset to default
2 

Table: cats
id
name

Table: foods
id
name

Table: cat_food
cid  (Cat ID)
fid  (Food ID)



Query: Select cats that like fish
SELECT
    name
FROM
    cats c
INNER JOIN 
    cat_food cf
        ON
    cf.fid = 2  -- Assuming food ID#2 is 'fish'

Query: Select cats that like fish, milk, or mice
SELECT
    c.name
FROM
    cats c
INNER JOIN
    cat_food cf
        ON
    cf.fid IN (1, 2, 3)
GROUP BY
    c.name

SCHEMA
CREATE TABLE IF NOT EXISTS cats (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;



INSERT INTO cats (id, name) VALUES
(1, 'Sassy'),
(2, 'Tiger');



CREATE TABLE IF NOT EXISTS cat_food (
  cid int(10) unsigned NOT NULL COMMENT 'Cat ID',
  fid int(10) unsigned NOT NULL COMMENT 'Food ID',
  UNIQUE KEY cid_fid (cid,fid),
  KEY fid (fid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



INSERT INTO cat_food (cid, fid) VALUES
(1, 1),
(2, 2),
(1, 3);



CREATE TABLE IF NOT EXISTS foods (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;



INSERT INTO foods (id, name) VALUES
(1, 'Fish'),
(2, 'Milk'),
(3, 'Mice');



ALTER TABLE cat_food
  ADD CONSTRAINT cat_food_ibfk_2 FOREIGN KEY (fid) REFERENCES foods (id) ON DELETE CASCADE,
  ADD CONSTRAINT cat_food_ibfk_1 FOREIGN KEY (cid) REFERENCES cats (id) ON DELETE CASCADE;

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for taking the time to make such an in depth answer!
0

I can only offer the rails example.

In rails

I a perfect world where every post had 1 category

And you would see SQL like 

Posts.find_all_by_category_id(Category.find_by_topic("sports").id)
# May generate 
SELECT ALL from `post`.* WHERE `category_id` = `7` 


class Category < ActiveRecord::Base
    has_many => :posts
end

class Post < ActiveRecord::Base
    belong_to => :categories
end

In the real world its a many to many relation

   class Category < ActiveRecord::Base
       has_and_belongs_to_many => :posts
   end

   class Post < ActiveRecord::Base
       has_and_belongs_to_many => :categories
   end

and rails would make the many to many join table for you behind your back

You would then go Category.find_by_topic("sports").posts to recover all the posts with a category of sports.

I don't know the detailed mySQL generated of the top of my head for how they do many to many relations.

Improve this answer

1 Comment

Thanks for the rails example! I've been using PHP for a long time and really like it a lot because of my C background. The projects I'm starting to work on these days seem like good candidates for rails. My next endeavor will be learning rails for sure-- I've already started working with ruby and i love it so far!
0

You are thinking right. You want two tables, a "Cats" table and a "Food" table. Your Food table would have some sort of primary key that your Cats table would reference as a foreign key. You could do a query perhaps like this:

SELECT * FROM cats WHERE food_id = 5

To understand how JOIN works in SQL, I would look through here.

Improve this answer

1 Comment

awesome! I after looking though the other answers and your link I can see where I can make a lot of improvements to my current databases!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.