4 ms·
If we dump everything we might JOIN on to a single document then in many cases we're going to end up with a ridiculously nested document, and/or duplicate data
by nulagrithom 9y ago
If we dump everything we might JOIN on to a single document then in many cases we're going to end up with a ridiculously nested document, and/or duplicate data everywhere. Let's try mapping out the project I'm working on right now.
We've got moves, and each move has multiple stops:
move: {
shipper: string,
consignee: string,
stops: [{
address: string,
appt: datetime,
}]
}
So far this looks great. I like this. Yay MongoDB!
But I need to add a driver to each stop... Where do I put the driver? If I put him under the stop I'm going to duplicate his data all over the place. Do I put the move under the driver instead?
driver: {
name: string,
id: string,
moves: [{
shipper: string,
consignee: string,
stops: [{
address: string,
appt: datetime,
}]
}
Ok, not bad. We've got an array of objects under an array of objects but I'm still perfectly comfortable.
Now I need to get a terminal-based look at all the drivers, so I know what every driver is doing in a particular city, also drivers can actually have multiple trucks too:
terminal: {
city: string,
drivers: [{
name: string,
id: string,
truck: [{
plate: string,
moves: [{
shipper: string,
consignee: string,
stops: [{
address: string,
appt: datetime,
}]
}]
}]
}]
}
That's getting a touch nested but... Drivers actually work for vendors, and vendors might work across multiple terminals. Do we duplicate the vendor across terminals, or the terminal across vendors? Also even the driver/truck relationship is many-to-many... Do we duplicate the truck info? What happens when we need to update a truck? Do we attempt to update across all drivers? Do we duplicate that data too?
terminal: {
city: string,
vendor: [{ // many-to-many! duplicate vendor data?
id: string,
drivers: [{
name: string,
id: string,
truck: [{ // many-to-many! duplicate truck data?
plate: string,
moves: [{
shipper: string,
consignee: string,
stops: [{
address: string,
appt: datetime,
}]
}]
}]
}]
}]
}
Ok, this schema is starting to look like shit and it's only going to get worse. The shipper and consignee should actually be unique objects as well, and we might want to update the contact info on them. Do we duplicate that data and update across all moves?
Also we want to cross-reference the shipper, consignee, billing, and ordering entities for market analysis (the consignee isn't always the customer, and the customer doesn't even always get the bill!) Do we duplicate that information across moves? Do we duplicate moves across entities? More many-to-many relationships...
Maybe I'm just not creative enough to use MongoDB, but I'm really starting to feel like this is a square peg in a round hole.
- scarface74 9y agoThis isn't rocket scientist and people far smarter than us have already figured this stuff out. https://martinfowler.com/bliki/DDD_Aggregate.html https://martinfowler.com/bliki/DDD_Aggregate.html If you look at this from a Domain Driven Design standpoint, you should model your business and think about your aggregate roots and each collection should be its own aggregate root, data access should be handled by one class/microservice for each aggregate root. If you modeled your Domain correctly that wouldn't be an issue. On the other hand, not modeling your domain correctly first would lead to an ungodly, untestable, tangle of stored procedures and 10 way joins with A relational database. The Mongo docs goes into best practices which mirrors basically the concept of an aggregate root. https://www.mongodb.com/blog/post/thinking-documents-part-2 https://www.mongodb.com/blog/post/thinking-documents-part-2
- nulagrithom 9y agoThe MongoDB document linked says "Referencing should be used to represent complex many-to-many relationships" ... "References are usually implemented by saving the _id field of one document in the related document as a reference. A second query is then executed by the application to return the referenced data." So... It's like a join, but with two queries instead of one, and you push the join data to the webserver, which brings us back to the start of this comment thread...
- scarface74 9y agoFrom your scenarios and what your asking to get from your data (yes I understand your problem domain, I wrote mobile software for field service workers in a previous life), you're not doing a lot of complex two way joins where you need to get all of the data from table A and match it to all of the data from table B. You're starting with a known entity like a terminal with references to other known entities. In your scenarios all your DB is doing is searching tables based on indexes and creating a data set in memory. If you did the same searches one table at a time starting from your known root - the terminal you are looking for and query individual tables based on foreign references, the only performance difference would be the slight overhead of multiple DB calls. You wouldn't even be getting unnecessary data back. Even if I were using a relational database, I would still separate out my different domains (drivers, terminals, stops, etc.) into different classes/microservices and join the different entities in memory. This would allow different developers, teams etc. to focus on separate business objects and would allow you to start off with a monolithic code base/app and separate the app out to microservice later without intertwined logic and to mix and match storage based on what makes sense. I never said always embed everything into one document. I said think through your aggregate roots to decide how to design your apps and your document model. I've designed this way for the last 10 years - 9 using RDMSs. I don't do complex 10 way joins for apps. I separate out business domain logically and let each module be responsible for its own domain and then have an overarching orchestrating class that combines the various objects when needed.