I have an existing database schema that is used in both an online (MySQL) and desktop (SQLLite) version of my product (data is synchronized between the two). I am now developing an iPhone version (CoreData) that I hope to synchronize with one or both of the existing products, but I am having trouble deciding how to manage entity relationships in the iPhone app in a way where I can still synchronize with a traditional database.
I will start with the simple example:
Table A has a UID field (GUID), and an Updated field (timestamp), as well as other fields. Table B has a similar structure.
There is a one-to-many relationship between Table A and Table B, which is managed with a third table:
Table C has a UID (GUID), TableA_ID (GUID), TableB_ID (GUID), and an Updated field (timestamp).
The GUID and Updated fields are used to manage synchronization between two databases, and the logic is applied to each table individually. I get that, in the CoreData app, I will have to manage these fields..er..properties myself.
I understand that in CoreData, I would define a one-to-many relationship between Entity A and Entity B and an inverse one-to-one relationship from Entity B to Entity A (and that CoreData will create the equivalent of Table C "under the hood"). The problem with this approach is that I will no longer have the information from Table C (i.e. UID and Updated fields) to properly sync with the other databases.
Another approach I have considered is to define and maintain Table C as a separate entity. In this case, would the relationship between Entity A and Entity C (and Entity B and Entity C) be one-to-one both ways?
A wrinkle in all of this is that I also have one-to-many relationships where the equivalent of Table C defines additional fields.
Any thoughts on this would be appreciated.
Bruce