Best way to SELECT from database

Not totally sure if this is the best place for this question, but regarding maintainability / good practice issues vs speed I have a question regarding what is the best way to SELECT some data from a database.I have the item_id of an item bought. I want to get a customer's billing information based on the item he bought. Should I do this with two queries or one using joins?

Here is my dbase schema:

customer_info
-----
customer_id
name
cc_num
expdate

item_bought
----
item_id
customer_id

And here is a basic pseudocode algorithm:
function getCustomerInfo(customerID)
{
SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info WHERE
customer_id=customerID;
return results;
}

function getCustomerInfoWithItemId(itemID)
{
SELECT item_bought.customer_id FROM item_bought WHERE item_bought.item_id=itemID;

results = getCustomerInfo(customer_Id);

return results;
}

results = getCustomerInfoWithItemId(400);
OR

function getCustomerInfoWithItemId(itemID)
{
SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info, item_bought WHERE item_bought.item_id=itemID
AND customer_info.customer_id = item_bought.customer_id;
return results;
}

results = getCustomerInfoWithItemId(400);

Definitely a join. Less traffic in/out of the DB.|||Hmm, yea there's less traffic, but with using 2 functions with 2 queries, won't I be able to reuse the functions ? However, with just one query using a join, there's less places that I will be able to reuse that function.|||Depends on what you are doing. If your site is for external customers (which Im assuming it is) you would want to do it all in one query. Speed is always the key to stop drop-off from your site. If your site is slow, there will be a lot of people who will just leave. If you are making the site for internal customers, then you can do multiple connections since the chances are they need to use the system and the number of connections to your database will be limited anyway.
Nick|||

tdnxxx444 wrote:

Hmm, yea there's less traffic, but with using 2 functions with 2 queries, won't I be able to reuse the functions ? However, with just one query using a join, there's less places that I will be able to reuse that function.

What exactly do you think you're going to be able to reuse?
Remember that a good design is orthogonal before being reusable.|||

Alex Papadimoulis wrote:

What exactly do you think you're going to be able to reuse?
Remember that a good design is orthogonal before being reusable.

What do you mean by orthogonal? I can reuse the function getCustomerInfo(customerID) everytime I want to get the customer information based on primary key.|||

Orthogonality is a design principle relating to explicitness. The more modules your system relies on to do things, the less orthagonal it is:
myCustomer = GetCustomerById( GetCustomerIdByItemId(ItemId) )
... versus ...
myCustomer = GetCustomerByItemId(ItemId)
The more functions interdependent on eachother, the more things have a chance of breaking when one of thef unctions break. Here is a pattern you may find helpful ...
Class CustomerFactory
Private Function FromDataReader( dr) As Customer)
...
End Function
Function FromCustomerId(CustomerId) As Customer
dataReader = SELECT ... FROM Customers WHERE CustomerId=?
Return FromDataReader(dataReader)
End Function
Function FromItemId(ItemID) As Customer
dataReader = SELECT ... FROM Customers ... JOIN Items ... WHERE ItemId=?
Return FromDataReader(dataReader)
End Function
End Class

|||

tdnxxx444 wrote:

What do you mean by orthogonal? .

Try this link for THE PRINCIPLE OF ORTHOGONAL DESIGN it is a two article. Hope this helps.
http://www.dbdebunk.citymax.com/page/page/622331.htm