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.