If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Investment ownership structures database design (M-to-M): hierarchy? graph?

 Gardener Registered User Join Date: Jan 2013 Location: Italy Posts: 4
Investment ownership structures database design (M-to-M): hierarchy? graph?

 Hi all, I am designing a database for my job that would be collecting information on:(1) financial flows related to investments in specific types of projects, gathering information along the way on (2) the investors in such projects, (3) the investors in the investors that invest in such projects, (4) the investors that invest in the investors that invest in such projects, (5) and so on. I. Simplified structure of the tables The simple way I represented this with a pencil and a paper was to have the following tables ___________ _____ ______ ___________ _____ ___________ ProjectActor1 Actor1 Actor2 Actor1Actor2 Actor3 Actor2Actor3 ------------- ------ ------- ------------ ------ ------------ junction table junction table junction table ______ Project ------- II. Project and investors/actors The relationship between a given project and its providers of capital: "Project" 1-to-M link "ProjectActor1" M-to-1 link to "Actor1"A project can have a multiple investors An investor can invest in multiple projects so that's a M-to-M relationship that I broke down with a junction table using the 2 FKs III. Multiple layers of investors/actors The relationship between a given actor and another actor: "Actor1" 1-to-M link "Actor1Actor2" M-to-1 link to "Actor2" "Actor2" 1-to-M link "Actor2Actor3" M-to-1 link to "Actor3" and so onAn actor can have multiple shareholders An actor can have multiple stakes in other actors so that's a M-to-M relationship that I broke down with a junction table using the 2 FKs IV. The problem with III This artificial breakdown of structure forces me to create an unknown number of ownership layers (it can go up to 10 layers in rather complex cases for instance so this is not unrealistic). This is cumbersome and also causes some problems. So here are my doubts - concerns - questions in relation to this:What if a given investor (say a corporate group) in Actor3 invests directly in a project? I don't have an Actor3Project table and so on. This is very real so I should be able to capture such relationships. Plus I'm pretty sure that having multiple ActorX tables with possibly overlapping / redundant is in breach of one of the normalization rules. I would ideally like to have a single Actor table for the sake of simplicity for subsequent queries in the database I believe we are dealing with a hierarchical structures here. Given the M-to-M nature of what happens in the investor side, are we dealing with a graph (I saw this in a forum or a blog) rather than a tree structure? If so, how could I deal with this or more simply make it work? I am a bit lost to be honest here. (FYI: This is will be implemented later on in MySQL.) Many thanks for your help! Cheers
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,297
A lot depends on the fine details of what it is you want to model

Id want an entity for:-
Investors
Projects
A junction table for an investors projects
Transactions types
CurrencyCodes
Specific forex rates
..could be for specific transactions AND specific points in time (say close busienss 31 Jan 2013
Transactions

the only real issue is how you model an Investor investing in a Project which may itself by an investor. in part I don't think your terminology is helping you here.

depends how you define an investor and a 'project'
if there is a mix or private individuals or non quote companies then you couldn't use the obvious candidate key which would the fully qualified SEDOL code. so you may have to resort to an autonumber ID.

you could have an autogenerated ID for projects and use the InvestorID as a FK to investor.

what you probably should do is define all projects as investors, drop the table for projects altogether and have two FK;s from InvestorsProjects one identifies the investor, the other identifies the investment (what you called project)
there may well be a better way to model the InvestorProjects and transactions
however the way I've drawn it out below would allow for many transaction to be associated with a specific investment/project combination
Attached Files
 gardener.pdf (96.7 KB, 4 views)
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
 Gardener Registered User Join Date: Jan 2013 Location: Italy Posts: 4
More details here (incl. visualization)

 Dear healdem, Many thanks for your quick reply. This is very useful and helped me clarify exactly how I wanted to map the database (clarified hopefully the difference between an actor and a project - a real-life activity). I must admit that the level of details in the database is rather important (there would indeed be currency codes, etc. but this is not where I have a problem) – what I described in my previous post would be a simplified / early version of the database. More details follow. Entities The 4 core / high-level entities in the database would be:The actors involved acting in possibly various roles (equity investor, lender, donor/grantor, guarantor, etc.) – hence not only an investor denomination; The projects that are being financed on-the-ground; and what characterizes the interactions between those 2 first groups, namely The financial flows originating from various actors to various other actors or project; (what you call transactions) The relationship between different actors or between actors and a given project (ownership, debt owed, oversight, management, etc.). Representation of the database (click to zoom) My aim with this database Being able to characterize sources of capital actor-by-actor for given projects. Since we are usually dealing with shell companies owned by an holding itself owned by a subsidiary itself owned by another etc. It is critical to get to the bottom of this to understand which actors actually provide the ultimate sources of capital to given projects. My problemsThe ACTOR2, ACTOR3 tables and so on can and should be directly connected to the Project table - since a big corporate group or one of its division or even one small subsidiary of that division are all likely to invest in the project - not only the one that's the most remote from the ultimate parent. This is a big problem. I have the feeling that using a string of multiple ACTOR each owning each others is not the smartest thing to do to deal with corporate ownership / relationships. Again I wonder whether I'm dealing with a sort of graph here. Many thanks again for your help PS: SEDOL would indeed help for quoted companies but not for all the subsidiaries or private-held companies

Last edited by Gardener; 01-31-13 at 19:04.
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,297
 so drop the idea of a separate table for project Instead put that information back in the entity Investors, perhaps call that Entities to reflect that it contains entities that invest or are invested in. with this approach you can have as many levels as you need, although I'd be surprised if you have, say, more than 5 levels how you handle the rest of your investment relationships as either details in the Investors Projects or as items within the Transactions table or in a separate table of their own right. if you know know how many attributes you need to cater for then I might be tempted to store some or all of that be wary of using float to represent 'stuff' such as %'ges or monetray values, far better to use decimal(X,Y) where is the total number of digits INCLUDING the number of decimal places Y. as to how much of the loan is left.. well if you know how much was loaned, and how much has been paid back over time then you know how much is left. If interest is payable on the loan then add that each year, or find a way. although if its psuedo environmental power schemes like wind power no doubt there wont be interest..... By knowing how an entity is related to antoher entity (as handled by having an Entities and EntitiresRelationships Entities (repalces Investors) ID {PK) autonumber EntitiesRelationships (replaces InvestorsProjects) OwningEntityID PK, FK to Entities ID OwnedIDPK, FK to Entities ID you can get rid of the idea of Actor1....ActorX __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
 Gardener Registered User Join Date: Jan 2013 Location: Italy Posts: 4

 Hey healdem, thanks again this is very helpful in getting my ideas more organized. I. The n-actor table logic I've been trying to follow my logic of having an actor having a relationship to a project but also having a relationship to other actors, who in turn had relationships to other actors. (click to zoom) Then I acknowledged that those non-1st level actors could also be investing in projects. (click to zoom) Then I acknowledged that Actor4 could invest invest in Actor3, a project but also in Actor2 or Actor1 (merging flows and relationships for the sake of simplicity) (click to zoom) Then I found out this logic was becoming silly asI had redundant content in the many Actor tables as the number of level of ownership increased, the relationships increased geometrically this would have been become hell to query this... II. Your suggestions Dealing with the n-level actor tables I have been drawing what I think you suggested + indeed merging flows and relationships between entities makes sense (click to zoom)Is this what you had in mind? Will this allow me to associate multiple investors and investees to a single entity? probably yes with a single composite key, right? I really think that projects are a very different animal than entities. A project is not the legal entity but the physical asset that is being owned by a legal entity. The content of the 2 tables would be rather different. So I would like to keep them separated. How could this be altered to deal with this? What did you mean with the attributes "tempted to store some or all of that"? Datatype for percentages, etc. Thanks for the suggestion of using DECIMAL(X,Y) - this is excellent Remaining loans to be paid back True indeed. Misc. I have seen (and this DB will have to deal with) Chinese power generation structures with more than 8 levels of ownership ending up in the hands of the Chinese PRC Government through cascades of holdings & subsidiaries. Again many thanks this is great
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,297
 sort of to me the entitiesrelationships defines the intersection of the 'owner' and the 'owned'. the way you have drawn it suggests that there must be one transaction per such relationship, whereas Id exect it to be the other way round, that there is a relationship between oner and owned that may comprise one or more transacations so I wouldnt' expect the transaction id to be part of the PK for EntitiesRealtiosnhips. thats why I used ower, owned and the date of the transaction as the proposed PK of Transactions. however that would mean you could only record one transaction per instance of time. if you enter the instsancae of time as just the date, that would be one transaction per day, if you added the time one per whatever the base datetime unit of the proposed db is. as to the difference between project and entity, thats down to you. there isn't enough information provided for me to make a judgement on that.it may be that you need a table for projects but for your current requirement of ntier owners its of dubious value. As we don't know the fulldetial (and Im not sure Id want to know the full detail) its probably best if you model projects as a separate item). Im guessing a project is owned by someone who may in turn be owned by one of more others and so on up the tree as such a logical place for projects is to hang off Entities with say a nullable FK from Transactions to projects allowing investments to be made either for specific projects or just the owned entity __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
 Gardener Registered User Join Date: Jan 2013 Location: Italy Posts: 4
 Dear healdem - thanks so much for your help. This was super helpful. Gardener

 Tags graph, hierarchical database, investment, ownership