11 ms·

Aside from whether or not this method of linking data increases performance, there is a huge cost in relational databases to the complexity created by many-to-m

Aside from whether or not this method of linking data increases performance, there is a huge cost in relational databases to the complexity created by many-to-many relationships. Even if the performance were the same, the dispersion of data makes it a nightmare to pull together a hierarchical object out of many different database tables, and then to deduplicate the data that is cross-joined (ie in the case of a user with multiple shipping addresses...join user on address => you get the same user record for every instance of address, which then has to be consolidated in code). Then do that times 5-10 other tables and you have yourself a very messy query that is very slow. These problems can be solved with KV stores, but at the cost of human readable databases. Likewise, with ORMs, you sacrifice performance and direct understanding of how the database is being queried. There are many advantages to a document-graph model like orientdb for these reasons.

mtdewcmu 13y ago
>in the case of a user with multiple shipping addresses...join user on address => you get the same user record for every instance of address, which then has to be consolidated in code One solution is to use two queries: one to get the user with the user's key, then a second to get the addresses that match that user key. If you want to get all the user fields and all the addresses in one query, you can do a join with a group by on user and aggregate the addresses. In Postgres, you can aggregate into an array with array_agg(). More recent versions have json_agg() to aggregate into json. Under no circumstances should you have to deduplicate data in code. That's what the database is for. These queries can get tricky to write, but I view them as enjoyable puzzles, like writing regular expressions. The payoff for writing the right query is that it's faster than fetching too many records and deduplicating in client code, and the SQL is short and declarative.

mtdewcmu 13y ago

>in the case of a user with multiple shipping addresses...join user on address => you get the same user record for every instance of address, which then has to be consolidated in code One solution is to use two queries: one to get the user with the user's key, then a second to get the addresses that match that user key. If you want to get all the user fields and all the addresses in one query, you can do a join with a group by on user and aggregate the addresses. In Postgres, you can aggregate into an array with array_agg(). More recent versions have json_agg() to aggregate into json. Under no circumstances should you have to deduplicate data in code. That's what the database is for. These queries can get tricky to write, but I view them as enjoyable puzzles, like writing regular expressions. The payoff for writing the right query is that it's faster than fetching too many records and deduplicating in client code, and the SQL is short and declarative.