1 reply to this topic

[Vaielab]

Hello,

I would like to have your opinion on what is the better option to do this.
I actually don't have a database for this, but I had to ask myself this question so many time, I want your opinion.
If we have a database that represent a sale system.
Each sale have the info of the client, and every items he bought.

A first table for each sales, with an id, a date
A second table with the client contact (his name, phone...)
And a third table with the items he bought and at what price (id of the item, price)

So if I wish to list all sell in the past month should I:
1) make a query with a inner join for each items, so the client name will be returned many time, and in php (or other language) only add the client info when the id of the sale isn't already in the array
something like this select * from sales inner join client on client.id = sales.idClient inner join items on items.idSale = sales.id

or should I
2) make a first query to only get the client info, than do a second query for the items
So I would have 2 smaller query, but a lot less duplicate data (that I don't have to strip in php)

So in your opinion wich is the best?

[WingedPanther]

It tends to depend on a lot of things. What are the indexes on the tables? Are there foreign key relationships? Will the extra data cause excessive bandwidth to be used (grabbing a blob from client, for example), etc.

Sometimes, just to make things more interesting, you want to do a join and extra queries (see blob above).