Attribute on a Tie

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

Attribute on a Tie

lfreeman
I have the following design issue (simplified to the core concept). Funds have Investors and Income Items. Income Items have an Amount attribute and a IncomeType. Investors will be allocated shares of Income Items.

I want to tie Investors and Income Items with the tie having an attribute Amount, but I can't give a tie an attribute. So I'm left creating a tied anchor between Investors and Income Items. This anchor called Investor Allocation will have the amount attribute, but I feel that it has an unnecessary id column since the allocation only needs the Income Item id and Investor Id to completely identify the Investor Allocation. The Investor Allocation id will have to accommodate (Investor * Income Items * Type) number of rows (and much more since Income Items has other attributes that will make the potential row count much higher).

Would the Investor id, Income Items id, Amount table not be in 6NF? Is there some other method rather than the tied anchor that would accomplish the goal of storing Investor Allocations?

Note: that sometimes Income Items are stored first and allocated to Investors while other times the Investor Allocations are known first and the sum stored in the Income Item. In all cases the Income Item should equal the sum of the Investor Allocations. The actual allocation process is iterative involving the results or prior allocations.

I would welcome your opinions on this issue. Thanks.
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

roenbaeck
Administrator
It sounds like Amount is a candidate for a knot on the tie. Even more so if there is a finite number of possible values for Amount, in which case identities can be determined in advance, but can also be done for infinite value domains with progressive allocation of identities.

Connecting a knot to a tie is conceptually the same thing as connecting it to an anchor having a single static attribute, which seems to be the case here. Model-wise you would have something like the following example tuples:

Investor anchor:
<#42>

Income item anchor:
<#555>

Share amount knot:
<#0, 0%>
<#10, 10%>

Investor to income item historized tie:
<#42, #555, #10, '2001-01-01'>
<#42, #555, #0, '2012-12-12'>

Does that solve your problem? The tie above is in (temporal) 6NF. Only if you were to add a second knot that also is outside of the primary key would the tie no longer be in (temporal) 6NF.
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
I understand how your solution works for finite allocation of identities of the Amount knot, but I do not have a finite value domain for the amount and I must restrict the investor to receiving only one allocation per income item. The Amount is a Money type since each investor must be allocated the actual amount down to the penny and not a percentage (due to the various methods of allocating the rounding errors among partners). 

Also each income item will have attributes for line item type (Contribution, Withdrawal, Interest Income, Admin fee, etc) and the period for which it was allocated (12/31/2012, 1/31/2013). Each investor may only receive one allocation from the income item and therefore only one allocation from each line item type for each period. It is not important to know the history of each allocation as the allocation is tentative until the period is closed and then the allocation becomes final.Once a period is closed changes are not allowed to any item or allocation. (Although the situation arises where a period may be reopened so perhaps a historicized tie would be useful but the one allocation per line item type per period can never be violated)

So, I was thinking that the solution would be:

Investor
<#42>
<#43>

Investor Name
<#42, 'Paul'>
<#43, 'Sally'>

Income Item
<#555>

Partnership Periods
<#23>

Partnership Period Date
<#23, '2012-12-32'

Partnership Period Open
<#23, True>

Income Item Partnership Period
<#555, #23>

Income Item Type
<#555, 'Contribution'>

Income Item Total Amount
<#555, 10000>

Investor to Income Item (6NF?)
<#42, #555, 4000.00>
<#43, #555, 6000.00>

Paul made a Contribution in the partnership's period #23 ended 2012-12-31 of 4000. 
Sally made a Contribution of 6000 for the same period. 

Other types of income allocations could be made by or allocated to Paul and Sally during the same period but and additional Income Items would exist for each. The sum of Investor to Income Item amounts would always add up to the Income Item Total Amount which essentially is the partnership's total amount. Some line item allocations start with the total amount and allocate to investors while others calculate each investor's amount separately and then determine the total amount for the income item. Income item is a simplified term as the income items can be expenses, fees, and capital movements.

Allocations may only be added for defined periods of the partnership therefore we have the partnership periods table. Periods are sequential and continuous.

I don't see that the Investor to Income Item table as I have described it is possible in anchor modeling because I can't define an "attribute on a tie". But I can't make the amount into a knot with an additional key which would allow multiple allocations and the additional key would have to be a money type I believe. I don't know what the solution would be if the tie were historicized.

I hope this has clarified my scenario.





On Sat, Mar 16, 2013 at 8:01 AM, roenbaeck [via Anchor Modeling] <[hidden email]> wrote:
It sounds like Amount is a candidate for a knot on the tie. Even more so if there is a finite number of possible values for Amount, in which case identities can be determined in advance, but can also be done for infinite value domains with progressive allocation of identities.

Connecting a knot to a tie is conceptually the same thing as connecting it to an anchor having a single static attribute, which seems to be the case here. Model-wise you would have something like the following example tuples:

Investor anchor:
<#42>

Income item anchor:
<#555>

Share amount knot:
<#0, 0%>
<#10, 10%>

Investor to income item historized tie:
<#42, #555, #10, '2001-01-01'>
<#42, #555, #0, '2012-12-12'>

Does that solve your problem? The tie above is in (temporal) 6NF. Only if you were to add a second knot that also is outside of the primary key would the tie no longer be in (temporal) 6NF.


If you reply to this email, your message will be added to the discussion below:
http://anchor-modeling.1047469.n5.nabble.com/Attribute-on-a-Tie-tp5706338p5706339.html
To unsubscribe from Attribute on a Tie, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
In reply to this post by roenbaeck
I saved a public model called Attribute on a Tie to give a simple demonstration of what I want to achieve.


On Sat, Mar 16, 2013 at 6:33 PM, Layton Freeman <[hidden email]> wrote:
I understand how your solution works for finite allocation of identities of the Amount knot, but I do not have a finite value domain for the amount and I must restrict the investor to receiving only one allocation per income item. The Amount is a Money type since each investor must be allocated the actual amount down to the penny and not a percentage (due to the various methods of allocating the rounding errors among partners). 

Also each income item will have attributes for line item type (Contribution, Withdrawal, Interest Income, Admin fee, etc) and the period for which it was allocated (12/31/2012, 1/31/2013). Each investor may only receive one allocation from the income item and therefore only one allocation from each line item type for each period. It is not important to know the history of each allocation as the allocation is tentative until the period is closed and then the allocation becomes final.Once a period is closed changes are not allowed to any item or allocation. (Although the situation arises where a period may be reopened so perhaps a historicized tie would be useful but the one allocation per line item type per period can never be violated)

So, I was thinking that the solution would be:

Investor
<#42>
<#43>

Investor Name
<#42, 'Paul'>
<#43, 'Sally'>

Income Item
<#555>

Partnership Periods
<#23>

Partnership Period Date
<#23, '2012-12-32'

Partnership Period Open
<#23, True>

Income Item Partnership Period
<#555, #23>

Income Item Type
<#555, 'Contribution'>

Income Item Total Amount
<#555, 10000>

Investor to Income Item (6NF?)
<#42, #555, 4000.00>
<#43, #555, 6000.00>

Paul made a Contribution in the partnership's period #23 ended 2012-12-31 of 4000. 
Sally made a Contribution of 6000 for the same period. 

Other types of income allocations could be made by or allocated to Paul and Sally during the same period but and additional Income Items would exist for each. The sum of Investor to Income Item amounts would always add up to the Income Item Total Amount which essentially is the partnership's total amount. Some line item allocations start with the total amount and allocate to investors while others calculate each investor's amount separately and then determine the total amount for the income item. Income item is a simplified term as the income items can be expenses, fees, and capital movements.

Allocations may only be added for defined periods of the partnership therefore we have the partnership periods table. Periods are sequential and continuous.

I don't see that the Investor to Income Item table as I have described it is possible in anchor modeling because I can't define an "attribute on a tie". But I can't make the amount into a knot with an additional key which would allow multiple allocations and the additional key would have to be a money type I believe. I don't know what the solution would be if the tie were historicized.

I hope this has clarified my scenario.





On Sat, Mar 16, 2013 at 8:01 AM, roenbaeck [via Anchor Modeling] <[hidden email]> wrote:
It sounds like Amount is a candidate for a knot on the tie. Even more so if there is a finite number of possible values for Amount, in which case identities can be determined in advance, but can also be done for infinite value domains with progressive allocation of identities.

Connecting a knot to a tie is conceptually the same thing as connecting it to an anchor having a single static attribute, which seems to be the case here. Model-wise you would have something like the following example tuples:

Investor anchor:
<#42>

Income item anchor:
<#555>

Share amount knot:
<#0, 0%>
<#10, 10%>

Investor to income item historized tie:
<#42, #555, #10, '2001-01-01'>
<#42, #555, #0, '2012-12-12'>

Does that solve your problem? The tie above is in (temporal) 6NF. Only if you were to add a second knot that also is outside of the primary key would the tie no longer be in (temporal) 6NF.


If you reply to this email, your message will be added to the discussion below:
http://anchor-modeling.1047469.n5.nabble.com/Attribute-on-a-Tie-tp5706338p5706339.html
To unsubscribe from Attribute on a Tie, click here.
NAML


Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

roenbaeck
Administrator
You may still use a knot:

AMT_Amount
<#1, 4000.00>
<#2, 6000.00>

with the tie becoming:
<#42, #555, #1>
<#43, #555, #2>

If you get a value you haven't seen before, say 2000.00, the you allocate it in the knot and reference the new key.

However, the knot AMT_Amount may very well be a candidate for refactoring at some point, since I have a feeling that "Contributions" actually are events having properties of their own other than Amount. Perhaps the exact dates of the contributions, payment methods, etc. In that case Contribution warrants being its own anchor, as in your original design.
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
In reply to this post by lfreeman
Hi
For the sake of clarity, can you provide the forum with your 'ideal' current view model of the entities involved in your example (e.g. investors, income_items, income_types) and their attributes (amount, name, type etc etc) and the relationships between them (m:m 1:m)
Thanks
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
The model that I'm describing below is for the allocation part of a much larger system. The concerns about collecting data on Contributions and other capital activity are addressed in another part of the application. Eventually after much review this data is merged together with portfolio income and expense activity that comes from another system into an integrated chart of accounts which is used for investor reporting. The key here is that the data must be aggregated over multiple periods, line items, and investors depending upon the reporting requirements. So the structure of the investor allocations drives the performance of the reporting system.

Funds have Investors, Chart of Accounts, Income Periods, and Line Item Values.

Income Periods are sequential and continuous by number starting at 0 and counting up. A period ending date is used to relate the period to the calendar time. Income Periods are the periods during which the fund's partnership agreement allows capital in or out, or marks its investments for purposes of calculating performance. Generally is monthly but it can be for whatever period the partners desire. Never daily (too expensive). You only need to calculate values for the partnership when capital can be exchanged. The values in between are irrelevant hence we mark time by Income Periods rather than a regular date. Income Periods may be open or closed but they may only be closed in sequence. No changes may be made to the related data of a closed period.

A Chart of Accounts is a hierarchy of Line Items such that any item is the sum of itself and all of its child items. The top level Line Item would contains an Investor's capital balance for the income period. The child line items show the details of the changes. This concept is key for how the accountants think about the relationships between the line items.

Line Items have a name and a specific allocation method that determines how the amount of the item is allocated to investors. Note that the allocation process is iterative and quite complex as many allocations depend upon the results of other allocations. Every allocation must store its results in the Investor Allocation table with a corresponding link to a Line Item Value and nowhere else. Some methods start with a value in the Line Item Value table, others calculate Investors' amount and put an aggregate in the Line Item Value, and others simply move data from the capital activity system into the appropriate Investor Allocation and Line Item Value.

Line Item Values hold the total amount of a Line Item for an Income Period that is to be allocated to investors. Note that a Line Item's allocation method may allocate to the investors by taking the total amount and distributing it among the investors, or the method may calculate each investor's amount and then put the sum in the total amount (for example certain fees calculated on each investor's balance, or the capital activity that is inserted from the capital activity tracking application). Line Item Values may only exist for Line Items that are in the Fund's Chart of Accounts. There is a chicken and the egg problem here as sometimes the Line Items Values is known before the allocations and sometimes after depending upon the allocation method. Note that the Line Item Values give the Fund's aggregate capital, income, and expenses without aggregating at the investor level. This can be important for reporting.

Investor Allocations hold the amount that a particular Investor received of the total amount in the Line Item Value. The key requirement here that the previous proposals have not addressed is that an Investor can receive one and only one allocation for a Line Item Value. So the primary key #InvestorId, #LineItemValueId, #Amount (id from a knot) would allow multiple amounts per investor from a Line Item Value which is not allowed. My current solution is #InvestorId, #LineItemValueId, Amount attribute. But there appears this construct is not a valid form in Anchor Modeling. An investor is not required to receive an allocation for each Line Item Value and many times will not depending upon how an allocation method works.

Investors contain the Name of the investor and describe the legal components of the investor's agreement with the fund. Allocation methods use these values and other fund level settings to determine allocations between investors.

So the relations would be:

Fund 1-m Investor
Fund 1-m Income Period
Fund 1-m Line Item Value
Fund 1-1 Chart of Accounts

Chart of Accounts 1-m Line Item

Line Item 1-m Child Line Item (assume a hierarchy is enforced)
Line Item 1-m Line Item Value

Income Period 1-m Line Item Value

Line Item Value 1-m Investor Allocation

Investor 1-m Investor Allocation

Hope this explanation helps.
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
Thanks - leave it with me - I will be back in touch soon
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
Hi
Thanks for the explanation. It seems like your requirement could be solved quite easily with 'standard' data modelling i.e. a hierarchy of line items (with a unique key of fund, period and line id); funds, periods, investors (probably m:m with funds) and an intersection (m:m) between investors and line items called investor_allocations. As each period closes the value for that period is allocated in varying amounts to the investors based on fund and investor 'settings'.

I understand your point about anchor modelling wrt ties (1:m or m:m) that have an attribute like amount which is a continuous value rather than an enumerated domain of id's.

So what drew you to anchor modelling in the first place? Is there something special or temporal about your requirement that you have not mentioned? If so then I would be interested to know more, if not then use standard modelling.

Regards
 


   

Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
I came across anchor modeling while searching for more information on a relational design that would eliminate nulls. I've read the Third Manifesto and have had enough practical experience (starting with rBase back in 1988) to realize the incredible problems that null values cause. I just had to debug a problem this morning that was the result of not taking nulls into consideration. Unfortunately the internet discussions regarding nulls are filled with journeyman programmers who completely miss the point regarding nulls. They immediately jump to the conclusion that the only alternative is to use empty string or some sort of signal value and miss the point regarding what the semantics of what a missing value means. It seems that the nosql and "the database is just a bucket to store values" crowd loves nulls and hates joins and any discussion quickly goes off the rails.

Anchor modeling was mentioned in a discussion on Stackexchange and went to the website to check it out. The modeling tool and the use of 6NF was very appealing to me. I find the the modeling tool very impressive.

So I took the real life database that I've designed over the last 10 years and attempted to model a simple version with the tool to learn more about anchor modeling and see if it would fit my needs. Of course, this is a slight detour from solving the null value issue, but I think that anchor modeling could be extended to eliminate nulls in the way that Darwen has suggested.

While I may not be able to use anchor modeling to refactor my current database right now, I certainly will continue to follow developments. Maybe the team will consider the problem that I raised with the investor allocation table and consider a solution that preserves the 1:1 relationship between the allocation and investor and the line item value without using another anchor table that has its own unique key (That is the solution I came up with that uses 3 anchors). I may be missing the point but in this database the performance of queries on this table is of the utmost importance. The investor id and the line item value id do form a complete key for the investor allocation and the only data value that needs to be stored is the value of the allocation. 

Thanks for taking a look.


On Wed, Mar 20, 2013 at 4:08 PM, rob squire [via Anchor Modeling] <[hidden email]> wrote:
Hi
Thanks for the explanation. It seems like your requirement could be solved quite easily with 'standard' data modelling i.e. a hierarchy of line items (with a unique key of fund, period and line id); funds, periods, investors (probably m:m with funds) and an intersection (m:m) between investors and line items called investor_allocations. As each period closes the value for that period is allocated in varying amounts to the investors based on fund and investor 'settings'.

I understand your point about anchor modelling wrt ties (1:m or m:m) that have an attribute like amount which is a continuous value rather than an enumerated domain of id's.

So what drew you to anchor modelling in the first place? Is there something special or temporal about your requirement that you have not mentioned? If so then I would be interested to know more, if not then use standard modelling.

Regards
 


   




If you reply to this email, your message will be added to the discussion below:
http://anchor-modeling.1047469.n5.nabble.com/Attribute-on-a-Tie-tp5706338p5706346.html
To unsubscribe from Attribute on a Tie, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
Hi
I like joins and don't mind nulls. I personally think that creating a table for each attribute is an expensive step to take just to avoid nulls so there must be other advantages to anchor modelling. Like I said, I like joins but not sure that I like the idea of a join for each attribute!

The modeling tool does let you create an attribute on a tie - but that is weird isn't it because a tie is really just a table (just like an anchors is really a table). An attribute on an anchor just creates a table with the same pk as the anchor so why can't the same be true of an attribute on a tie? Technically it would be easy to create a table withe the same pk as the tie for its attribute.

My conclusion is that there must be some other restriction that we dont know about wrt the other functions provided

Regards

Rob
   
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
sorry - typo - I should have said 'doesn't' let you create an attribute on a tie
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

roenbaeck
Administrator
In reply to this post by lfreeman
Just to make it clear, when you say "So the primary key #InvestorId, #LineItemValueId, #Amount (id from a knot) would allow multiple amounts per investor from a Line Item Value which is not allowed." Why do you not remove #Amount from the primary key, giving you the desired behavior? Or am I misunderstanding something?
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
I would remove #Amount from the primary, but if this table is a tie then I can't do that correct?


On Thu, Mar 21, 2013 at 1:24 PM, roenbaeck [via Anchor Modeling] <[hidden email]> wrote:
Just to make it clear, when you say "So the primary key #InvestorId, #LineItemValueId, #Amount (id from a knot) would allow multiple amounts per investor from a Line Item Value which is not allowed." Why do you not remove #Amount from the primary key, giving you the desired behavior? Or am I misunderstanding something?


If you reply to this email, your message will be added to the discussion below:
http://anchor-modeling.1047469.n5.nabble.com/Attribute-on-a-Tie-tp5706338p5706350.html
To unsubscribe from Attribute on a Tie, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

roenbaeck
Administrator
In reply to this post by rob squire
Rob, the restriction is there because of historization. In a historized tie the changing date is part of the primary key.

For example, the tuple:

<#1, #42, '2001-01-01'> with PK <#1, '2001-01-01'>

If you were to reference this PK from another table you would get a dangling reference if a change occurs.

For example, the tuple is "changed" to:

<#1, #43, '2002-02-02'> with PK <#1, '2002-02-02'>

The only way to connect your "tie attribute" table would be to duplicate your information having two rows referencing the different PKs. If you do so, your "tie attribute" is no longer in 6NF. Therefore, we only allow ties to reference other tables, and not other tables to reference ties.

I hope that explains why.
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

roenbaeck
Administrator
In reply to this post by lfreeman
In a tie you can select which 'roles' are part of the PK. If you click on a cardinality marker you can change it from m (many) to 1 (one) selecting "Toggle identifier" from the popup menu.

You can also select "Toggle cardinalities" from the "Layout" menu if you feel more comfortable working with PK-components than cardinalities. Then the cardinality markers will change to small circles instead. A filled cirlce means the role is part of the PK, and if it is not filled, it is left out of the PK.

Instead of selecting "Toggle identifier" you can also just hover over a marker and press 'i' on the keyboard.
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
I see. Now that may work for me. I will look at my model again and adjust it.


On Thu, Mar 21, 2013 at 1:36 PM, roenbaeck [via Anchor Modeling] <[hidden email]> wrote:
In a tie you can select which 'roles' are part of the PK. If you click on a cardinality marker you can change it from m (many) to 1 (one) selecting "Toggle identifier" from the popup menu.

You can also select "Toggle cardinalities" from the "Layout" menu if you feel more comfortable working with PK-components than cardinalities. Then the cardinality markers will change to small circles instead. A filled cirlce means the role is part of the PK, and if it is not filled, it is left out of the PK.

Instead of selecting "Toggle identifier" you can also just hover over a marker and press 'i' on the keyboard.


If you reply to this email, your message will be added to the discussion below:
http://anchor-modeling.1047469.n5.nabble.com/Attribute-on-a-Tie-tp5706338p5706353.html
To unsubscribe from Attribute on a Tie, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
In reply to this post by roenbaeck
Hi

Thanks for the explanation.

It is now clear to me why anchor modelling doesn't allow an attribute on a tie.

The fact that you can historise a tie means that you cant allow an attribute on a tie (because a date is part of the historised tie pk)

Anchors don't have that problem because you cant (and it doesnt make sense to want to) historize an anchor.

Conclusion: Dates are tricky!
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

rob squire
Finally, if you are interested in seeing a modelling technique that allows attributes like amount on m:m relations (with full bi-temporal historization of 1:m, m:m relations and attributes) then please let me know and I will be happy to provide a demo.

Regards

Rob
Reply | Threaded
Open this post in threaded view
|

Re: Attribute on a Tie

lfreeman
That would be interesting.


On Thu, Mar 21, 2013 at 4:54 PM, rob squire [via Anchor Modeling] <[hidden email]> wrote:
Finally, if you are interested in seeing a modelling technique that allows attributes like amount on m:m relations (with full bi-temporal historization of 1:m, m:m relations and attributes) then please let me know and I will be happy to provide a demo.

Regards

Rob


If you reply to this email, your message will be added to the discussion below:
http://anchor-modeling.1047469.n5.nabble.com/Attribute-on-a-Tie-tp5706338p5706356.html
To unsubscribe from Attribute on a Tie, click here.
NAML