4 Replies - 806 Views - Last Post: 08 July 2012 - 05:22 AM Rate Topic: -----

#1 CasiOo  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1398
  • View blog
  • Posts: 3,094
  • Joined: 05-April 11

Dish & food category performance question

Posted 07 July 2012 - 05:34 PM

My website is going to use a database, and I'm not sure what the best design would be.

There should be the two tables Dishes and FoodCategories. I want the user to be able to search for a dish's name and then be able to see it's food category. The user should also be able to see all dishes belonging to a single food category (which will happen often!).

I first thought of this design:
FoodCategories:
name (varchar)

Dishes:
dishID
name
foodCategory - foreign key
...


But what if there are many dishes? I do not want the database server to go through every dish whenever a user want a dish from a chosen category. The lookup should be quick!

Then I thought about another design:
FoodCategories:
name (varchar) - primary key
dishID - primary key & foreign key ON DELETE CASCADE

Dishes:
dishID
name
foodCategory - foreign key ON DELETE CASCADE
...

Would this work? Or is there a better solution? I am a little worried about the relations between the two tables.

Is This A Good Question/Topic? 0
  • +

Replies To: Dish & food category performance question

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Dish & food category performance question

Posted 07 July 2012 - 06:17 PM

View PostCasiOo, on 08 July 2012 - 12:34 AM, said:

Would this work?

Not unless you want to duplicate the category names for each dish that belongs to the category. No, your first design is what you should be doing in this situation.

View PostCasiOo, on 08 July 2012 - 12:34 AM, said:

But what if there are many dishes? I do not want the database server to go through every dish whenever a user want a dish from a chosen category. The lookup should be quick!

With an index on your Dishes.foodCategory column, the database would have no reason to do a full table scan when querying for rows with a specific food category. It'll use the index to pull up only those rows that match your conditions. Some databases will create an index for a Foreign Key by default (like MySQL w/ InnoDB), but in others you may have to create it explicitly. - Tell us which database system you are using and we can be more specific.
Was This Post Helpful? 2
  • +
  • -

#3 CasiOo  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1398
  • View blog
  • Posts: 3,094
  • Joined: 05-April 11

Re: Dish & food category performance question

Posted 07 July 2012 - 06:42 PM

View PostAtli, on 08 July 2012 - 01:17 AM, said:

View PostCasiOo, on 08 July 2012 - 12:34 AM, said:

Would this work?

Not unless you want to duplicate the category names for each dish that belongs to the category. No, your first design is what you should be doing in this situation.

View PostCasiOo, on 08 July 2012 - 12:34 AM, said:

But what if there are many dishes? I do not want the database server to go through every dish whenever a user want a dish from a chosen category. The lookup should be quick!

With an index on your Dishes.foodCategory column, the database would have no reason to do a full table scan when querying for rows with a specific food category. It'll use the index to pull up only those rows that match your conditions. Some databases will create an index for a Foreign Key by default (like MySQL w/ InnoDB), but in others you may have to create it explicitly. - Tell us which database system you are using and we can be more specific.


I will be using MySQL (just started learning MySQL today). I read that the default engine is not InnoDB, so I will have to somehow make these indexers myself in my script? I am also aware that foreign keys will only work with InnoDB.

Thanks for your answer it was really helpful :)
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Dish & food category performance question

Posted 07 July 2012 - 07:07 PM

The default engine for MySQL was MyISAM up until MySQL 5.5.5. Now the default engine is InnoDB. However, which engine your server uses by default is configurable, so which engine is used by default can by no means be taken for granted. It's usually best to just specify which engine you want to use when creating tables.

As an example, this CREATE TABLE command will create tables that uses InnoDB, but also explicitly adds an index to a Foreign Key constraint.
CREATE TABLE `categories`(
	`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	PRIMARY KEY(`id`)
) ENGINE=InnoDB;

CREATE TABLE `items` (
	`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`category_id` INTEGER UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY (`category_id`)
		REFERENCES `categories`(`id`),
	INDEX (`category_id`)
) ENGINE=InnoDB;


The INDEX (`category_id`) line is the very simplest way to add an index to a column. You can adjust various aspects of it, but that's a more advanced topic. It's usually safe to use the defaults.

Read all about the syntax in the manual entry for the CREATE TABLE command.
Was This Post Helpful? 2
  • +
  • -

#5 CasiOo  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1398
  • View blog
  • Posts: 3,094
  • Joined: 05-April 11

Re: Dish & food category performance question

Posted 08 July 2012 - 05:22 AM

Thank you! Again very helpful.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1