SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member alsotop's Avatar
    Join Date
    Jan 2005
    Location
    United Kingdom
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Efficient table structures

    Hi, I'm currently producing a web application with PHP and MySQL and would like to know your recomendations on the most efficient table structure to use. I have two tables which have a many-to-many link, so naturally I would have thought of a link table which picks of the Primary Keys from each table and puts them into a list. However, when programming this, it requires a hefty lot of MySQL Queries: I loop through the link table to find what links to what, and for each row, I must perform another query to find out what each is.

    As of now, I have tempoarily set up a different table strucuture which simply copies over the data from the first table, but of course this not only causes data duplication, also possibly redundancy.

    Can I ask for your views on how many queries is "too much", and which method you would prefer to go ahead with? Thankyou very much!

    Edit: might this be more useful?:

    Code:
    SELECT table1.*, table2.*
    FROM table1, table2
    WHERE table1.id=table2.foreiginkey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    any time you have (1) a query inside a loop, or (2) a comma-delimited list of values inside a column, you almost certainly have an inefficient solution that won't scale

    queries inside a loop can usually be fixed with a join

    comma-delimited lists should be broken out into their own table (do a google search for first normal form)

    here's an example i created for someone on another forum to demonstrate the classic many-to-many structure:
    Code:
    create table members
    ( id smallint not null primary key auto_increment
    , name varchar(9)
    );
    insert into members ( name ) values ( 'curly' );
    insert into members ( name ) values ( 'larry' );
    insert into members ( name ) values ( 'moe' );
    
    create table clubs
    ( id smallint not null primary key auto_increment
    , name varchar(9)
    );
    insert into clubs ( id, name ) values ( 101, 'football' );
    insert into clubs ( id, name ) values ( 102, 'macrame' );
    insert into clubs ( name ) values ( 'cooking' );
    insert into clubs ( name ) values ( 'babies' ); 
    
    create table memberclubs
    ( memberid smallint not null 
    , clubid smallint not null 
    , foreign key ( memberid ) references members ( id )
    , foreign key ( clubid ) references clubs ( id )
    , primary key ( memberid, clubid )
    , joindate datetime not null
    );
    insert into memberclubs values ( 1, 101, '2004-09-09' );
    insert into memberclubs values ( 1, 102, '2004-09-09' );
    insert into memberclubs values ( 1, 104, '2004-11-11' );
    insert into memberclubs values ( 3, 102, '2003-04-22' );
    insert into memberclubs values ( 3, 104, '2004-02-29' );
    try that, and if you have any further questions, let me know

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member alsotop's Avatar
    Join Date
    Jan 2005
    Location
    United Kingdom
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thankyou!

    Ahh, thats brilliant! Thankyou very much, r937!
    Last edited by alsotop; Jan 23, 2005 at 08:26. Reason: Would help if I could spell!
    Information that could be useful: PHP 4.3.10, MySQL 4.0.23.
    Then again, who knows?


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
  •