Uniqueness across multiple properties


#1

Hello, I’m trying to create an app where user accounts are synced, and the app pulls bank transactions that are stored in the database. Each user can have multiple accounts, and each transaction is stored with its account ID and the transaction ID. Transaction IDs are guaranteed to be unique within a single bank account, but not across different bank accounts, so I need both the account ID and the transaction ID to uniquely identify a single transaction.

How can I ensure uniqueness across both of these fields? For example, if two phones using the same account separately import the same set of transactions, then those documents are replicated, how can I prevent duplicates from being synced?

From some preliminary Googling, I think the answer is to use compound keys, but I’m having trouble finding any documentation about creating/querying compound keys in CBL 2.0.


#2

Just so I understand the problem - is each transaction a separate document ?


#3

Yes, that’s correct.


#4

So could you specify the document Id to be a combination of AccountId:TransactionID and query based on that ?

You could consider using per-account channels so you put all documents belonging to an account into a specific channel and only pull documents belonging to specific channels of interest.


#5

So could you specify the document Id to be a combination of AccountId:TransactionID and query based on that ?

This is exactly what I’m doing now, but it seemed like more of a hack. If I want to query for all transactions for a given account, I need to either do a LIKE query on the ID string, or create a separate field that contains only the account ID. Neither of these options is terrible, but – from my limited research – I thought compound keys provided a more elegant way of storing/retrieving multiple values in a single key. Or is that unrelated?

You could consider using per-account channels so you put all documents belonging to an account into a specific channel and only pull documents belonging to specific channels of interest.

Yep, I’m already doing this now, as well.


#6

Actually it’s not a hack. In a document style NoSQL world, it’s what your app defines and enforces. Perfectly acceptable to handle it with a Id that makes sense for your application that is guaranteed to be unique . You can index on this Id and then do a like or regex query expression against this.

In a document style database, We don’t have this concept of “keys” in the sense of a traditional relational style database. There is no uniqueness constraint enforcement of any of the properties by the database - that’s unto the app. The document Id is what is assumed to be unique and hence the suggestion of generating that from account Id and transaction Id (which you appear to be doing already).

But that said, I may be missing the point of your question . Are you trying to figure out a way to query based on two properties because that’s certainly possible …or are you trying to see if the system can enforce this uniqueness when document is created (because that’s not possible)

Cool. Looks like you are on the right track already.