Efficient table structures

[alsotop]

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?:

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

[r937]

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:

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

[alsotop]

Ahh, thats brilliant! Thankyou very much, r937!