Querying the Database

All our services are designed to query each database table at most once. This minimizes the use of the database, as all the required data is gathered in one go. To achieve this goal, we have to design SELECT stored procedures that sometimes accept criterias from related tables. For example, let's say our database has the following schema :

Client (1) - (*) Order (1) - (*) OrderItem (*) - (1) Item

If we need to query the Item table based on an ItemID, OrderID or ClientID, we end up with these stored procedures :

Item_SelectByItemID:
SELECT * FROM Item WHERE ItemID=@ItemID;

Item_SelectByOrderID:
SELECT DISTINCT Item.*
FROM Item INNER JOIN OrderItem ON Item.ItemID=OrderItem.ItemID
WHERE OrderItem.OrderID=@OrderID;

Item_SelectByClientID:
SELECT DISTINCT Item.*
FROM Item INNER JOIN OrderItem ON Item.ItemID=OrderItem.ItemID
INNER JOIN Order ON ItemOrder.OrderID=Order.OrderID
WHERE Order.ClientID=@ClientID