There are multiple solutions for this challenge and that's why I would like to explain my decisions.
So, after reading the requirements I decided to focus on the reads performance. To achieve that I decided to flatten the tree structure, doing this I can easily search for all relations of any organization because I have all the relations in the database.
Example:
{
"org_name": "Paradise Island",
"daughters": [{
"org_name": "Banana tree",
"daughters": [{
"org_name": "Yellow Banana"
},
{
"org_name": "Brown Banana"
}]
}]
}
+-----------------+-----------------+----------+
| org | org_relative | relation |
+-----------------+-----------------+----------+
| Banana tree | Brown Banana | daughter |
| Banana tree | Paradise Island | parent |
| Banana tree | Yellow Banana | daughter |
| Brown Banana | Banana tree | parent |
| Brown Banana | Yellow Banana | sister |
| Paradise Island | Banana tree | daughter |
| Yellow Banana | Banana tree | parent |
| Yellow Banana | Brown Banana | sister |
+-----------------+-----------------+----------+
If I want to search the relations of Banana tree I just need to do a simple SELECT * FROM organization WHERE org = 'Banana tree'
and I get all the direct relations (parent, daughter, sister).
Of course, there are no perfect solutions, and by improving the read operations I sacrificed the writes, basically it's a trade off.
Advantages
- Fast and simple read operations
Disadvantages
- Lose tree hierarchy (we can't rebuilt the tree)
- Flatten tree (bunch of rows)
Setup Project
- Create database (scripts/create-database.sql)
- Install node dependencies (npm install)
- Run application (npm run start or npm run start-pm2)
- (Optional) Generate fake data (npm run generate)
- GET: http://localhost:5000/api/organizations/TestOrg?page=1