macOS Core Data - dual inverse relationships

[interMatt]

I have an entity that defines a transaction. Most of the time the transaction will simply involve an addition (e.g. receiving a shipment of 10 widgets into department A) or a subtraction (e.g. dispatching an order of 3 widgets from department B).

However, sometimes a transaction may be a little more complicated. For example department A may transfer 2 widgets to department B but as the departments are located on opposite sides of the globe department A records the subtraction transaction as having occurred on 16/07/2009 while department B has a long weekend in their country and doesn't record their addition transaction until 21/07/2009. They also give the transactions different names and descriptions.

I would like to be able to reconcile these two transactions in my application once they are completed. Obviously I could add additional information to the transaction entity to allow this (i.e. Department A creates a transaction with a "from" date, name and description but with an empty "to" date, name and description) which department B then fills in. This doesn't seem ideal though.

What I think would make sense is letting both departments create their transactions normally and then create a "transfer" entity to link the two. My question is, how do I go about this? Is it simply a matter of creating a "transfer" entity with a to-many relationship to transaction (with a min count of 2 and a max count of 2)? If so I guess I would then have to validate to make sure the transactions are compatible (i.e. count is the same, item is the same, receive date is not before send date, etc).

Alternatively, does transfer need two separate relationships to figure out who the transfer is from and to? If I create two separate relationships I then have to create two inverse-relationships in the "transaction" entity (transferFrom and transferTo) which seems to be a slippery slope towards the less than ideal solution I already mentioned.

I think I might have answered my own question while thinking through the alternatives but any input would be appreciated!

 

[lucasgladding]

It actually sounds a lot like what I've been working on with Ledger. The answer, IMHO, depends on how complex your transactions could be. I would setup the database with more entities than you suggested, and link them with relationships. Much of the entity creation could then be done behind-the-scenes.

Transaction
- relationship - transfers (to-many)

Transfer
- relationship - transaction (to-one)
- relationship - department (to-one)
- relationship - item (to-one)
- name
- description
- amount (+ve or -ve)

Department
- relationship - transfers (to-many)
- name
- description
- address

Item
- relationship - transfers (to-many)
- name
- description

You could then setup transient attributes for item inventory per-department, worldwide, etc. If you have different part numbers depending on the department, you could add another entity that joins the department and item and allows each to create their own localized names and descriptions.

This could be much more than what you need, but hopefully gives you some ideas. Keep in mind that your interface could be the same between database descriptions.

Kind regards

Luke

 

[interMatt]

It actually sounds a lot like what I've been working on with Ledger. The answer, IMHO, depends on how complex your transactions could be. I would setup the database with more entities than you suggested, and link them with relationships. Much of the entity creation could then be done behind-the-scenes.

Thanks for your reply Luke! And the model for a finance application would be quite similar (i.e. departments become accounts and items are no longer necessary as the only item you're dealing with is cash).

Running with the finance example I have a few detail level questions:
1) Would you give the account entity opening balance and date attributes (and prohibit transactions before that date) or would you just make the opening balance another transaction?

2) The example you gave seemed to place more emphasis on transfers than transactions. I see my problem domain being reversed from that - as it would be in a finance application too I believe. Normally a person just deposits their salary or pays for goods, it's only on rare occasions they wish to transfer money between accounts (e.g. getting ready to pay a big bill). So I like the idea of transfers simply being two transactions but I still don't quite see how to link them...

The FROM transaction item has to-one relationship with transfer item which has a to-one relationship with the TO transaction item. However, when I try to model that I get confused as in the core data model there doesn't seem to be a way of differentiating between a "FROM" transaction and a "TO" transaction. Or do you just deal with that later when you're fetching the data?

 

[lucasgladding]

Thanks for your reply Luke! And the model for a finance application would be quite similar (i.e. departments become accounts and items are no longer necessary as the only item you're dealing with is cash).

I've actually been working on a double-entry accounting application, so you define what is going and what is coming in every transaction. In double-entry accounting, an account is the same as an expense, asset, etc. I actually have transactions with a to-many relationship to entries, as each transaction will have two or more entries that are then linked back to the accounts. The account balance is then the result of the entries within that account.

Running with the finance example I have a few detail level questions:
1) Would you give the account entity opening balance and date attributes (and prohibit transactions before that date) or would you just make the opening balance another transaction?

I would just make it another transaction, at least in the database. You could predefine a transaction and make a separate interface for entering those amounts if you like.

2) The example you gave seemed to place more emphasis on transfers than transactions. I see my problem domain being reversed from that - as it would be in a finance application too I believe. Normally a person just deposits their salary or pays for goods, it's only on rare occasions they wish to transfer money between accounts (e.g. getting ready to pay a big bill). So I like the idea of transfers simply being two transactions but I still don't quite see how to link them...

That may just be a matter of the terminology I was using. As mentioned above, every transaction is a transfer in double-entry accounting. I'm using almost exactly this model in Ledger.

The FROM transaction item has to-one relationship with transfer item which has a to-one relationship with the TO transaction item. However, when I try to model that I get confused as in the core data model there doesn't seem to be a way of differentiating between a "FROM" transaction and a "TO" transaction. Or do you just deal with that later when you're fetching the data?

If you wanted to, you could use two relationship, one for to and one for from. The only problem with that is that your calculation would require checking which relationship has been used to calculate the ending balances for each inventory item. As suggested, you could mark from or to based on the amount/quantity being +ve or -ve. The application then just needs to validate that the two are equal. With that, just use one to-many relationship to link the amounts.

Kind regards

Luke

 

[interMatt]

Hi Luke (and anyone else reading this thread! =)),

Firstly thanks for your help and secondly thanks for making me think about double-entry accounting. I think I now know the BASICS of it... enough to understand why you put the emphasis on transfers.

I would just make it another transaction, at least in the database. You could predefine a transaction and make a separate interface for entering those amounts if you like.

Hmmm... having toyed with the options I've realised that you're correct yet again. But (and perhaps I'm looking at this the wrong way), is there a way to (or better yet a reason to) link from Account to a given Transaction as the "opening balance"? My thinking is that I will treat that opening balance transaction slightly differently in terms of business logic... for example checking to make sure new transactions don't predate the opening transaction.

I thought of having an "opened date" attribute on the account (and the transaction from that date is the opening balance) but that doesn't work in the likely event there are other transactions on that date. If I wasn't using Core Data I would simply use a linked list or other ordered structure as the data store and always put the opening balance in the root node but Core Data doesn't care about order. Does having a second relationship between Account and Transaction make sense? Ignoring the other attributes and relationships it would look something like (numbers represent inverse relationships):

Account
1) transactions (one to many with Transaction)
2) openingTransaction (one to one with Transaction)

Transaction
1) account (one to one with Account)
2) openingTransaction (one to one with Account)

Sorry for the barrage of questions!!!

Regards,
Matt

 

[lucasgladding]

I thought of having an "opened date" attribute on the account (and the transaction from that date is the opening balance) but that doesn't work in the likely event there are other transactions on that date. If I wasn't using Core Data I would simply use a linked list or other ordered structure as the data store and always put the opening balance in the root node but Core Data doesn't care about order. Does having a second relationship between Account and Transaction make sense? Ignoring the other attributes and relationships it would look something like (numbers represent inverse relationships):

Account
1) transactions (one to many with Transaction)
2) openingTransaction (one to one with Transaction)

Transaction
1) account (one to one with Account)
2) openingTransaction (one to one with Account)

An "opened date" could actually be used without any difficulty. Your validator would be custom code anyway, so you could check the day rather than just checking which time is greater. NSCalendar lets you define which components are relevant when comparing dates.

I'm not sure that there is any benefit to having the "opened date" attribute anyway, but that depends on the app. I suppose accessing the attribute would be quicker than following the "openingTransaction" relationship and checking that date. If you haven't mocked up your UI already, that may give you another answer.

You probably don't need the second relationship mentioned above under Transaction. I could be wrong, but you probably never need to lookup which account was opened given the transaction. The Core Data Programming Guide talks about "recent hires" under the Managed Object Models section as an example of a similar idea.

Make sure you understand where you're going with your UI before you finalize your data model. I often kick myself because something I missed became obvious when I opened my sketchpad and started drawing.

PS: If you want ordering anywhere in your application, make sure you define an order index attribute. Whenever an array is changed, step through each item and assign it an index. Core Data works with sets rather than arrays for storage, but objects are retrieved as an array based on whatever you define for the sort descriptors... just a little tidbit since you mentioned ordering in your last post.

Kind regards

Luke

 

[interMatt]

I guess I think that an account with zero transactions is no account... every account at least needs an opening balance/transaction (or perhaps that's just an arbitrary thought of mine I need to re-examine). But you're right, there's no need for an "opened date" attribute or a special relationship just to have an "opening" transaction. A better way to do it (if I decide to enforce this opening transaction idea) would just be to have a minimum count of one on Account's "transactions" relationship wouldn't it? That way the account won't be validated by Core Data unless there's one [opening] transaction. It's then up to the user (or the application logic) if that transaction has to be meaningful or can simply be a zero amount transaction with today's date and a generic description of "opening balance". I guess that may also be helpful from a programming point of view in that I know I can always just add up the transaction amounts. If for some reason there are no transactions I know the account is invalid (rather than just empty).

I'm not sure that there is any benefit to having the "opened date" attribute anyway, but that depends on the app. I suppose accessing the attribute would be quicker than following the "openingTransaction" relationship and checking that date.

Or I could just "cache" the opening date from the earliest (or only) transaction in the account. It wouldn't change often. I guess a transient property might be useful there???

I don't need that inverse relationship, you're right. I think that was a hang up from reading somewhere that inverse relationships are important... just because they are imporant in many situations doesn't mean they're required in every situation.

Make sure you understand where you're going with your UI before you finalize your data model. I often kick myself because something I missed became obvious when I opened my sketchpad and started drawing.

Really??? I thought (from a theoretical Comp Sci point of view) that a well designed model will be independent of a well designed view? Does that theory not translate into real life? I guess your point about ordering (and defining an order index attribute) might be one example but do you have any others?

Core Data works with sets rather than arrays for storage, but objects are retrieved as an array based on whatever you define for the sort descriptors... just a little tidbit since you mentioned ordering in your last post.

Is an order index attribute necessary if I'm storing transactions by date? My only thought on when it might come in useful is when multiple transactions occur on the same day and you want to list them in the order they apper somewhere else (e.g. a bank statement). I guess an order ID attribute would be more natural than manipulating the time portion of the date to get them ordered "correctly" plus I'm sure it will make more sense to me when (if) I revisit the code after quite some time...

I think I'm pretty close to being happy with my model (thanks in no small part to your help and advice Luke). When I'm done I'll try and remember to post the final design for critical review and on the off chance someone else stumbles across this thread in the future and wants to know what my outcome was.

Cheers,
Matt

 

[interMatt]

On second thoughts (and after reading this Cocoa-Dev mail thread) perhaps transient isn't what I was after???

My initial thought was that information such as account balance or number of transactions is actually available elsewhere (via the relationship) so I shouldn't store the calculated value in the store in case it gets out of sync. Reading that thread makes me think it's more likely to get out of sync if I make it transient!?

 

[lucasgladding]

On second thoughts (and after reading this Cocoa-Dev mail thread) perhaps transient isn't what I was after???

My initial thought was that information such as account balance or number of transactions is actually available elsewhere (via the relationship) so I shouldn't store the calculated value in the store in case it gets out of sync. Reading that thread makes me think it's more likely to get out of sync if I make it transient!?

I usually use transient attributes when the value is calculated - thanks for the link to the thread on the Apple lists. I typically work in one context only, so merging changes is something I have never dealt with.

I am still trying to find the time for this:
http://www.pragprog.com/titles/mzcd/core-data

It may be worth a look. I haven't made it far enough to comment in the PDF version. I'll probably work through the book when it gets released in final version.

 

[interMatt]

I usually use transient attributes when the value is calculated - thanks for the link to the thread on the Apple lists. I typically work in one context only, so merging changes is something I have never dealt with.

I am still trying to find the time for this:
http://www.pragprog.com/titles/mzcd/core-data

It may be worth a look. I haven't made it far enough to comment in the PDF version. I'll probably work through the book when it gets released in final version.

Aaah, I missed that small detail. And I don't think I knew you could work in more than one context and merge changes so I'll happily use transient attributes.

I stumbled across that Core Data beta book but choked when I saw the price. I'm not saying it isn't worth the money but $US21 for a book that isn't even finished yet seemed a little hard to justify to myself, let alone my wife! Maybe I'll get it for Christmas =)

You're probably sick of my questions but is there any reason to include additional data when it comes to attribute names. For example, if I have three entities all with a "name" attribute should I just call the three attributes name or will that be potentially confusing? The alternative would be to give them unique unambiguous names such as fooName, barName and bazName. I know in general the principle is make your code unambiguous but is that taking things too far? Are attributes always specified by the entity they belong to anyway?

 

[lucasgladding]

I have purchased a few beta books and haven't regretted the decision yet. You get updates to the book and the final version in PDF, so while you're getting early access, you're really paying for the final version.

I would use the shorter names. I recently started defining a master entity that includes name, notes, dates, location, photo, and contact information and use that as the parent for most other entities. You should always know what type of entity you are looking at, so the common names shouldn't be an issue.

Kind regards

Luke

 

[interMatt]

Thanks for all your generous assistance Luke!

For anyone who cares I decided against implementing transfers at this stage for the sake of simplicity and to ensure I meet my deadline. I might reconsider it if there is ever a future version of the app.

I've also attached a picture of my core data model.

Regards,
Matt