Imagine an application that allows you to add line items to a quote. The end result is like a sales person giving you a printed sheet with information such as name of the business, name of the sales person, and various service line items printed on the quote along with pricing, description, and quantity.
To implement this on the database level I have tables Item (holds 0-to-many items per Quote) and QuoteMeta (holds quote meta-information like who created the quote, quote number, revision, status, etc).
Then I have a domain object called Quote, which is not aware of the database layer directly, but receives data from both Item and QuoteMeta. The Quote is the "quote sheet" described previously.
My question is how to handle this domain object when I have more than one table representing it internally. For example, with my Quote domain object I can do something like …
Single Repository, Single Domain object
//Repository populates $quote from both database tables
$quote = (new QuoteRepository())->getQuoteById($id);
$quote->getQuoteNumber(); //gets data from quote metadata table
$quote->getItemByLineNumber(5); //gets an item from items table
The above "marries" the tables into the one domain Quote object. QuoteRepository handles queries directed to and from both database tables. My question is .. can I do this?
For example, will it be better to keep concepts separate, maybe like so?
Multiple Repositories, Multiple domain objects
//repository strictly deals with quote meta data
$quoteMeta = (new QuoteMetaRepository())->getQuoteMetaById($id);
$quote->getQuoteNumber();
//repository strictly deals with item handling
$items = (new ItemRepository())->getItemsByQuoteId($id);
$items->getItemByLineNumber(5);
Here the concerns are more separated, but I have complexity of dealing with multiple objects, when physical Quote representation is typically understood to be the items + quote metadata merged together. You don't have just items without metadata, and there is little point in having metadata without service items.
That said, is preferred to keep repositories separate, or together? And if separate, how do I handle things like table JOINs that operate on both tables? Which repository do they go to?
Dealing with multiple tables
To shift gears a bit, I have some code purpose of which is to delete an Item. An item record is deleted from the table. Then references to other data that are tied to the Item are deleted from 5 other tables (things like graphs, design, etc, and other data that belongs to the item). Should this "Item Deletion" be a single handler method that deletes data from multiple tables directly in the place of one method, or should each table have their own respective Repository handler class and method, and I can call each handler to delete their portion of the item?
Also, in the example where I delete the item, I am not really touching much of QuoteMeta table or entity. Thus I may only use the ItemRepository handler by itself, which makes me lean towards separate repositories in this case.
Notes on ORM
In my codebase I am using an ORM, where I have entities, such as Item Entity corresponds to item table, and QuotaMeta corresponds to quote_meta. The focus of my question though is not on an ORM. ORM provides a convenient way to populate my entities from tables, but without ORM I'd either be populating Entities anyway (i.e. via mysqli_fetch_object), or I'd be using arrays. The question still stands — how do I model my domain objects and corresponding repositories? Do I … have one per table, or do I go to a higher level of abstraction, to where I operate on multiple tables per single domain/repository?
Final Thoughts..
To be super-specific, do I have a Quote domain object that operates on both tables, or do I have separate ItemGroup and QuoteMeta objects, where one manages items that belong to the Quote but doesn't care about the metadata, and one manages Quote metadata, without the dealing with items that belong to it?
Best Answer
Your first approach is basically creating an ORM. If ORM is what you are after then go with it, but remember that objects from the ORM do not and should not represent your domain - ie. the layer enforcing your business rules presented by the stakeholders. They are objects which understand persistence and as such they should not contain business rules, because that would be breaking of the Single Responsibility Principle, which is not a rule to blindly follow but which greatly increases code readability.
With that in mind, your domain layer not working with ORM objects, you would need to transform the ORM objects to your domain objects anyway, which seems kind of pointless.
I greatly favor the direct repository approach, as it very clearly states what each object does:
You have a domain object and want more data related to it, then use a service which knows how to communicate with outer world, in this case the persistence layer.
Also, completely avoiding ORM when querying the database lifts the constraint that you need to use the ORM objects, which you don't. Many times you need different representations of the same entity, for different purposes, and for that you can sometimes use even very simple DTOs. This makes it easier to construct such objects, leaves less memory footprint and generally makes the applications faster, because you can specifically craft queries to reduce the number of times you need to go from the application server to the database server and back.