Here’s a challenge both in database design and LookML. It’s from our use case in modelling complex capital structures, but the same data structures can certainly find applications in many different environments. We would love your help.
Here goes:
In one table we have a number of debt instruments (bonds, loans, etc.) These are the objects that we trade, make money from, etc…
In another table, we have a table full of companies who issue that debt.
In a perfect, easy world – every instrument would be related to one company, and we could write a very simple one (company) to many (instruments) join.
The world isn’t easy though. Instead, those companies are related to one another in various pyramid shaped capital structures, with parent companies and subsidiaries. It gets more complicated, as companies buy and sell subsidiary companies, their debt may migrate along with them. Also, we can’t rely on just using the very top of the pyramid, as sometimes there are large conglomerates, and we are only interested in on section of it.
Out of all of that mess, we want a way to be able to answer top-down and bottom-up questions.
- Top-Down Example: How much money did we make from company X
- Bottom-Up Example: Given Bond A, tell me all of the other bonds related to it (by joining up to companies, and then back down to the individual bonds)
I should add what we do have. At any given moment, I can usually find out which sub-company a bond is connected to. And I can find out the parent company of any given company.
Backing up, to a birds-eye view, every company has a one to many relationship to other companies, and each company may have a one to many relationship to debt instruments.
While I’ve already made my head spin, this feels like something that has a solution. Similar relationship structures exist all over the place, and we’re hoping that someone with a bit more knowledge of database design may be able to provide some guidance.
Thanks again, all ideas are welcome
