CODEWITHSUNDEEP
mysqldatabaseone-to-many
Apollo
Apollo

Reputation: 9064

MySQL optimizing a table structure

If I have a list of food items, each with a "course field", does it make sense to enter the course in as text ("Entree"), or would I be better served to create a course table and then use foreign key referencing? This is a one-to-many relationship between the course and food, but a one-one relationship between the food and course correct?

Thanks for any advice.

Upvotes: 0

Views: 91

Answers (2)

Christopher Pelayo
Christopher Pelayo

Reputation: 802

I stick to 1:M for the course and food. If this is the scenario I would be creating a separate table for the course and separate table for the food and use the course_id as a foreign key on food table.

anyway entering the course as a string would still work but the problem is when your going to do an update for a particular course you would still go on each food item having that course to update it and sometimes some restaurant may eventually change their course it's either they may add, edit or delete a course. :)

Upvotes: 0

Indy-Jones
Indy-Jones

Reputation: 728

I used to create separate tables for everything, but sometimes it's more hassle than it's worth. So it depends on your maintenance. If you are going to be changing these values and do not wish to have to do a massive such as:

UPDATE tblFood SET course = 'Main Entree' WHERE course = 'Entree'

if you need to change the values, then perhaps an external table isn't such a bad idea. The problem comes when you need to query the database...you'll need to inner or outer join the other tables.

A DBA once told me that MSSQL did a terrible job with indexing text/varchar values so that's why the external tables method was born...not sure if I completely believe that, but in the same breath he said that MySQL did a much better job of this, and makes queries much easier.

I'm sure you'll see 10 different answers for 10 different reasons why you should or shouldn't use an external table, but many times it comes down to what your specific needs are. Hope this helps a bit.

Upvotes: 1

Related Questions