Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Location
    Italy
    Posts
    4

    Question 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 on
    • An 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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,514
    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 Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Location
    Italy
    Posts
    4

    Post 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:
    1. The actors involved acting in possibly various roles (equity investor, lender, donor/grantor, guarantor, etc.) – hence not only an investor denomination;
    2. The projects that are being financed on-the-ground;
      and what characterizes the interactions between those 2 first groups, namely
    3. The financial flows originating from various actors to various other actors or project; (what you call transactions)
    4. The relationship between different actors or between actors and a given project (ownership, debt owed, oversight, management, etc.).


    Representation of the database
    Click image for larger version. 

Name:	LSCP Database.png 
Views:	5 
Size:	231.8 KB 
ID:	14029 (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 problems
    • The 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 20:04.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,514
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Location
    Italy
    Posts
    4

    Post

    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 image for larger version. 

Name:	Stage1.JPG 
Views:	3 
Size:	330.8 KB 
ID:	14032(click to zoom)

    Then I acknowledged that those non-1st level actors could also be investing in projects.
    Click image for larger version. 

Name:	Stage2.JPG 
Views:	3 
Size:	367.0 KB 
ID:	14033(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 image for larger version. 

Name:	Stage3.JPG 
Views:	1 
Size:	266.6 KB 
ID:	14034(click to zoom)

    Then I found out this logic was becoming silly as
    • I 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 image for larger version. 

Name:	Stage4.JPG 
Views:	5 
Size:	199.5 KB 
ID:	14035(click to zoom)
    1. Is this what you had in mind?
    2. Will this allow me to associate multiple investors and investees to a single entity? probably yes with a single composite key, right?
    3. 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?
    4. 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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,514
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2013
    Location
    Italy
    Posts
    4
    Dear healdem - thanks so much for your help. This was super helpful.

    Gardener

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •