table and select

Hello,

Say I have a table FRUIT defined as:
f_id primary key autoincrement
f_field1
f_field2
..
..
f_fruit_type
..

I could do:
"select * from table FRUIT where f_fruit_type = 'apple'";
and that is FIRST WAY to do it.

Now another way is whenever someone input and insert a row into table FRUIT that has fruit type = 'apple', I will insert also into TABLE APPL, defined as
ap_id primary key autoincrement
ap_fruit_id //foreign key is f_id as above

Then to list all rows that fruit type = 'apple' in table FRUIT, I do:

"SELECT f.*
FROM table FRUIT f, APPL a
WHERE f.f_id = a.ap_fruit_id "

OK, so this is the SECOND WAY to find fruit type = 'apple', but it requires 2 tables.
Which way is more efficient and better?

Thanks,
John

I've always looked at it as "how can I store the same information as little as possible?" and it's usually based on a one to many relation. You have one Fruit Type which relates to many Fruits.

In you current schema, you would store 'apple' many times in table Fruit, then again in table Apple. It would be better to store fruit types in a table with a unique id, then reference that id in your fruit table.

table.fruit
f_id
f_t_id // from table.fruit_type
f_1
f_2

table.fruit_type
f_t_id
t_label

In table.fruit_type you might have the following rows:
1 | Apple
2 | Pear
3 | Orange

In table.fruit, you'd have
1 | 1 | field1 | field2
2 | 3 | field1 | field2
3 | 1 | field1 | field2

In table.fruit, you have two apples, one orange, and zero pears.

Thanks, jharnois. So it's more efficient to break into 2 tables.
One thing, suppose this table:
1 | Apple
2 | Pear
3 | Orange

and there are exactly only 3 types of fruits like that, that's it. If when searching, the user wants to find a store in a city in the United States that sells apple (there are hundreds of thousands of stores), a detail about that store will be displayed. So in a query, it may contains several joins, do you think it's still efficient to have 2 table as above?

Thanks,
John

Yes, check out the manual for information on JOIN and INDEX.

Thanks again, I'm kinda concerned when breaking down to more tables may create huge temporary table for database to work in a query, and if not then it may slow things down.

BTW, I posted same topic at forum http://www.sitepointforums.com/foru...p?s=&forumid=88

and jofa has about the same view as yours.