Recently I came across the DAO design pattern and I'm a bit confused how to properly use it.
I have read somewhere that there should be one DAO per table and it should perform CRUD operation on that table only. DAO should be dumb object that just maps object properties with database fields and business layer uses different DAOs to complete the objects. A DAO should not call another DAO. Is this correct? I have an example where this doesn't work.
Suppose I have a three entities in my general store application: Product, Customer, and Bill.
In database I have three tables:
product(id, name, price),
customer(id, name, address),
bill(id, product_id, customer_id, date)
If I create DAOs that can access only one table and perform only CRUD operations on it, it will be simple to create ProductDAO and CustomerDAO but I can not think of how BillDAO will work. Will it only return me the productId and customerId and from business layer I again call ProductDAO to get the details of each product and the customer's name. Lets take another example, say i want to get the details of all the products bought in a particular month and name of customers who bought them. This requires join of all the tables. Which DAO should answer this query, should there be another DAO for it?
Please help me in understanding the best practices for using a DAO pattern.