#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Westminster, CA USA
    Posts
    52
    Rep Power
    14

    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
    Last edited by johnn; November 1st, 2002 at 05:05 AM.
  2. #2
  3. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1100
    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.
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Westminster, CA USA
    Posts
    52
    Rep Power
    14
    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
  6. #4
  7. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1100
    Yes, check out the manual for information on JOIN and INDEX.
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Westminster, CA USA
    Posts
    52
    Rep Power
    14
    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/forum...?s=&forumid=88

    and jofa has about the same view as yours.

IMN logo majestic logo threadwatch logo seochat tools logo