MySQL 1:N Data Mapping

Question

Something really bugs me and im not sure what is the "correct" approach. If i make a select to get contacts from my database there are a decent amount of joins involved.

It will look something like this (around 60-70 columns):

SELECT * 
FROM contacts
LEFT JOIN company
LEFT JOIN person
LEFT JOIN address
LEFT JOIN person_communication
LEFT JOIN company_communication
LEFT JOIN categories
LEFT JOIN notes

company and person are 1:1 cardinality so its straight forward. But "address", "communication" and "categories" are 1:n cardinality.

So depending on the amount of rows in the 1:n tables i will get a lot of "double" rows (I don't know whats the real term for that, the rows are not double i know that the address or phone number etc is different). For myself as a contact, a fairly filled contact, i get 85 rows back.

How do you guys work with that? In my PHP application i always wrote some "Data-Mapper" where the array key was the "contact.ID aka primary" and then checked if it exists and then pushed the additional data into it. Also PHP is not really type strict what makes it easy.

Now I'm learning GO(golang) and i thought screw that LOOOONG select and data mapping just write selects for all the 1:n.... yeah no, not enough connections to load a table full of contacts. I know that i can increase the connections but the error seems to imply that this would be the wrong way. I use the following driver: https://github.com/go-sql-driver/mysql

I also tried GROUP_CONCAT but then i running in trouble parsing it back.

Do i have to do my mapping approach again or is there some nice solution out there? I found it quite dirty at points tho?

Answer 1

The solution is simple: you need to execute more than one query!

The cause of all the "duplicate" rows is that you're generating a result called a Cartesian product. You are trying to join to several tables with 1:n relationships, but each of these has no relationship to the other, so there's no join condition restricting them with respect to each other.

Therefore you get a result with every combination of all the 1:n relationships. If you have 3 matches in address, 5 matches in communication, and 5 matches in categories, you'd get 3x5x5 = 75 rows.

So you need to run a separate SQL query for each of your 1:n relationships. Don't be afraid—MySQL can handle a few queries. You need them.