SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table and select question

    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; Nov 1, 2002 at 03:34.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ???
    TABLE APPL is some kind of apple index?

    Why not create an index on FRUIT.f_fruit_type instead?
    Makes selecting ...WHERE f_fruit_type = 'apple' faster

    If you really really want to create a second table, then it should be a table with fruit types...
    id, type
    1, apple
    2, banana
    3, cherimoya

    ...and then change the fruit table to
    f_id primary key autoincrement
    f_field1
    f_field2
    ..
    f_fruit_type FK - id from fruit_type

  3. #3
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    TABLE APPL is some kind of apple index?
    Thanks, Jofa. Yes, its key is ap_id primary key autoincrement
    Why not create an index on FRUIT.f_fruit_type instead?
    Makes selecting ...WHERE f_fruit_type = 'apple' faster
    I thought about it like doing this:
    INDEX f_fruit_type (f_fruit_type)
    but if table FRUIT has several other indexes, it may slow things down, correct my basic understanding if I'm wrong. So, create an index on FRUIT.f_fruit_type (as you suggest) is ONE WAY to do it.
    If you really really want to create a second table, then it should be a table with fruit types...
    id, type
    1, apple
    2, banana
    3, cherimoya
    If I use THIS WAY, then in a join of 2 tables, I need to add 'AND' for such as:
    ... AND f_fruit_type = 'apple'
    so, there are 2 ways to do it, which way is efficient?

    Another thing is if I create 3 tables: one for apple index, one for banana index, one for cherimoya index, is it better than 2 WAYS above?

    Thank you very much in advance,
    John
    Last edited by johnn; Nov 1, 2002 at 08:44.

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basic rule; create indices for columns frequently used in where clauses => create the index on the fruit type name in the fruit_type lookup table?... actually, I don't think you need any index - how many types are there?
    Creating your own fruit type index tables seems like a really slow method

    The point with two tables, one for fruits and one for fruit types, is that you don't have to repeat the same fruit type name for each row in the fruit table.
    When you join the two tables, it's on
    fruit.fruit_type_id = fruit_type.id

    As I understand it, the fruit/fruit_type relationship is like city/country (OK, bad examples with apple, banana, cherimoya then... should have used "fruit families")
    Correct?
    Last edited by jofa; Nov 1, 2002 at 08:42.

  5. #5
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, jofa. The relationship is more like a fruit type in many stores. I guess 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 tables as above?

    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.

    Thanks,
    John
    Last edited by johnn; Nov 1, 2002 at 12:48.

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,253
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    A quick lookup table will not slow your queries down much at all. In fact, a well normalized database (at least to 3rd normal form) will almost always beat a non-normalized database in performance.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  7. #7
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, DaveMaxwell. I feel better. A quick lookup table (indexed) is a table that contains 2 or 3 fields and can contains many rows, is it right?

    Thanks again,
    John

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,253
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Yes, that would be how I would define it. Codes table is another name commonly used for it.

    I usually create one per type of lookup I want to do, but I have also seen it done in one table with a two field lookup key. That's usually more complex and unnecessary in most cases.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •