DAL design: handling access to 2 or more tables in a database?

Question

I'm developing on php at the moment, but this question should be language independent.

For DB access, I have always heard people promoting the "one class per table" practice, so I'm wondering where to put access methods that accesses 2 or more tables at the same time (using JOIN, for example). I can think of 3 ways to deal with this:

1. Create a separate class for the combination of the 2 tables
2. Put the access methods in the class for both tables
3. Have the BLL call each db class separately and write custom logic to get what you need (i.e. do the join outside of SQL)

What is the best practice and why?

Thanks!

Answer 1

Of your three options 1 like one the best. As long as there isn't too much business logic in it. "Too" much is subjective, mind you. Another option is to create a database view that combines the 2 tables. You then create a class for that as if it was a table.